Infrastructure Saturday - Wrap Up

Firstly a huge thank-you to everyone that attended the session on Saturday, and also a thank-you to BenF for assisting with the presentation (and letting me out of the cage for the day).

I am working with the organizers of Infrastructure Saturday to make the slides available for download, so stay tuned for more information on that front.

Some interesting questions were raised during the session and I thought I would provide some additional information here for those that were interested.

During the session some questions/discussions were raised about Virtalisation. I won't go into this in this post because I am actually expecting some interesting news on that front to come out of the SQLPASS which is being held this week in Seattle which although I am not attending I will be following closely.

At the end of the session I was approached with the question "We currently have multiple SQL 2005/2008 servers but our monitoring is only of the server layer, can you suggest/recommend how we could monitor the SQL layer".

In my experience a combination of monitoring solutions can offer a much more holistic view of your environment. This also helps when monitoring multiple different Operating Systems (e.g. Windows and Linux). Of cause this is based on my experience with monitoring 220+ servers.

The reason a combination of monitoring solutions can offer more depth is because some of monitoring platforms offer better network monitoring than others, and other monitoring platforms offer better Application / Software monitoring.

The challenge with monitoring a SQL Server is that you not only need to monitor the Infrastructure (e.g. ICMP, TCP Port tests, etc) but you need to be able to check Event Logs and that Services are running. Most monitoring systems can provide those tests, however you also need to monitor the actual SQL Instance layer and some of the best ways of doing that is through some form of script which can run a TSQL statement or use SMO to check aspects of the environment and return a status code to the Monitoring environment.

Some of the key SQL layer aspects that a good monitoring solution should be able to check are:
- SQL Server Memory
- SQL Server Error Log
- SQL Server Settings
- Database File Free Space
- Database Status
- Database Settings (for enforcing environment settings)
- Blocking Locks

My experience has been largely about using Microsoft Operations Manager (aka MOM) and custom VBScripts to ensure that our business rules are enforced. This has also allowed a number of automated actions to be implemented while allowing for the dynamics of the farm.

Here are some links to some monitoring platforms to look at:

Microsoft System Center Operations Manager (aka SCOM, the latest release of MOM)
http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx
If I had to recommend an enterprise software it would be SCOM, only because that is where my experience has been with. Of cause there are other offerings from EMC and other vendors which might be more suitable to your environment.

If your looking for some free monitoring solutions purhaps the following might be of interest:

Zenoss
http://www.zenoss.com/ or http://community.zenoss.org
I have had some experience with using this and it can offer a good monitoring tool for network monitoring (ICMP) and auto discovery. It does take some amount of tweaking to get to work well with Windows (as with any Unix based monitoring tool) however it can provide that first level monitoring quite well and there are a number of ZenPacks available to provide SQL Server monitoring. However I do find it quite challenging to come up with a in-depth SQL monitoring from it, but this is purely because I am use to a Windows Scripting environment.

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
I previously blogged about this (and some other open-source tools). This is still my fav free open-source offering, and a great option if you do not have but budget for SCOM and if you are willing to write your own client front-end, or if you need to plug into an existing monitoring front-end.

I hope this information helps you to find a way to monitor your SQL database environment. Feel free to post comments should you need more info.


Thanks again to everyone that attended the session.