Adapting vCheck for SQL Server
Posted by Josh | Posted in Powershell, SQL Server | Posted on 06-11-2012
Tags: automation, Powershell, SQL Server
2
For those of you not already aware, VMware and PowerCLI guru Alan Renouf (blog | twitter) recently upgraded his excellent vCheck Powershell script framework to have a plugin friendly approach. (The framework was originally written to provide a daily report of issues identified throughout a VMWare environment.) As a result, numerous forks have been popping up for getting daily reports on all sorts of systems, from Exchange to System Center. I noticed that there didn’t appear to be anything for SQL Server, and pinged Alan to confirm if anyone had starting working on one. When he responded that no one had, I volunteered to take a stab at it. The framework is an impressive piece of work, with some robust HTML reporting features and extensibility. If you’ve got a need to do any kind of centralized, scheduled reporting on some infrastructure, I highly recommend you check out his work.
And here, dear reader, is where I need your help. I want to make this as useful and complete a daily health check as possible for all of us DBAs out there. I have my own list of items that I want to check on, but I want to get more input to make sure I’m covering all the bases.
The scripts will connect to a CMS server, and iterate over all the servers contained within while performing various health checks. At the end, a nicely formatted HTML report is delivered listing all the problems identified. Thanks to Alan’s work, all the thresholds will be completely configurable.
Here is a list of the checks I’ve thought of so far:
- Ping test (is the server responding to a ping)
- Last backup date (full, differential, log)
- Last DBCC date
- Disk space free percent
- Services running (SQL Server, SQL Agent)
- Database file space free percent
- High severity errors (17+)
- Failed logins (over a certain threshold of counts)
- Failed agent jobs
This is a short list, which is why I’d love to hear from you. What would you like to see in your mailbox every morning that will give you the best view of your SQL Server infrastructure?
Josh,
Damn fine idea – I was looking at this myself after using both Alan’s VMware and Exchange vCheck scripts and thought of expanding the usage to SQL as well so glad it’s not just me!
My list for such included (some of these listed in yours already):
1) Server info: Computer name, OS, OS SP, SQL Ver, SQL SP, Version (e.g. Standard, Enterprise, Developer)
2) Disk space report: volumes with disk space less than x% percent (copy / paste the Exchange plug-in)
3) SQL DB Stats: Server, DB Name, DB Size, Log Size, Whitespace, Last grown date for each, how grown (% or volume), Mounted or not, Logging configuration (Full, Simple…)
4) Resilience: Server, DB Name, Last full, Last incremental, Last transaction log backups, replication (log shipping / mirrored… with dates / status of each
4a) if mirrored. the latency within the mirror itself
5) Index health: % defragmented, last date / time of defragmentation / re-index
6) Latency on a standard query of DB Server or DB’s themselves e.g. “SELECT @@VERSION” or “SELECT TOP 100 * FROM sysobjects”
Interested in your thoughts and let me know if you wish me to tackle one of more of the above or how you’re getting on with this as is.
Cheers
Steve
Hey Steve,
Thanks for the comment! Since I’ve moved out of a DBA role and into a full time development gig I’ve not worked too much on this. I do have a working model that just needs to be documented. Let me finish that up in the next week and put it out on my Bitbucket repository. If you like, please shoot me an e-mail at josh at awanderingmind dot com with you e-mail address and the like, so I can give you commit access. At that point you are welcome to contribute plugins. I’m also happy to just add anything you want write directly if you’d rather stay away from dealing with BitBucket (with full credit of course!). You’ve certainly got some good ideas there! Thanks for volunteering to assist.
-Josh