Wednesday 6 February 2013

Adventures with Powershell, turning it into BAU

Having tamed the security beast for the time being I have now been able to get my version of server monitoring tool in place and running reliably for a few days.

The biggest issue I have encountered though is that our service provider has supplied us with such an array of down servers that I haven't had a chance to roll out the process to any more machines.

I now have a process that breaks into multiple parts (with some documentation for whoever comes after me) broken into several "creation" files to keep functionality clear and simple having chosen a target SQL server (tested on 2008R2 as the host and 2005, 2008R2 targets (Standard, Enterprise & Express editions) :
  1. Tables Script - creates all the tables, indexes and a view to reduce my typing when looking at results. I have included in the process a "ProcessLog" table, I have used an identical design as I have in place on a live application on another box and one day I might well merge the two together (which has prompted me to create a to do list).
  2. Check State process creation - creates the requisite stored procedure on the host machine.
  3. Report State process - creates separate functionality for reporting the state retrieved from the check state process, I separated the two because originally this was configured to email results (HTML formatted) but in testing this got annoying when I had slow / non-functioning email so I added a parameter to present output locally; it was tidier to separate checking a state from reporting on states so allowing me to run the check process once and check the results repeatedly without waiting - handy for multiple users.
  4. Manage history - each time the state checking occurs it moves all the current data to a history table - that way the live data is uncluttered whilst we keep a record. The maintenance process takes two parameters, firstly how long to keep any data and secondly how long to keep "issues" - that way I can, for instance, say I'm only interested in a complete log of service state information for 7 days but I'd like to keep servers which are not reported as active for 3 months. Handy for pointing at in discussions with service providers.
  5. Scheduling process. Via my 1-click install process I generate two "DBA Maintenance Tasks" - one called "Daily" and one "Weekly" (I suspect you know how often they run). But this checking should ultimately run more frequently (that will depend on your enviroment & execution time, criticality / SLAs and how on the ball the server boys are), I am thinking ultimately once a hour for normal use (and I have added another to do item, logging checking periods).
  6. A deployment record. Currently this is a simple Excel sheet that shows the date of the last code update (manually entered) and a list of servers with deployment dates (all manual) with  conditional formatting to highlight out of date deployments (another to do item is to make this a tidier SQL process).
I also have a 1-click install process (I'll write about this one day) that deploys all of my maintenance routines to a specified machine. What I've done to that is add "monitoring server" to the list of variables configured (yes you'll need something reliable) and updated the weekly maintenance routine to add a step for the history management action if the 1-click install is running on the monitoring server (the 1-click install is also coded for redeployment of updated code). I also automatically add a new server to the monitoring list assuming a default checking frequency.

Next I'll go through the code that does the work.


No comments:

Post a Comment