Sunday, December 17, 2006

Monitoring SQL Server using SQL Server Health and History Tool (SQLH2)

A couple of weeks ago my boss came across this tool called SQLH2. Its purpose is to monitor Microsoft SQL server, by collecting data about how the machine is performing and optionally what the SQL server services are doing.

The main part is one executable file that you schedule to run every day or so. This collects data about the server, how it is performing and what the state of the sql servers are running on that machine. It logs all this info into a sql server database.

The second part is a service that runs every few mins, and collects SQL server performance counter data. These get put into a local file and when the afore mentioned exe runs this data gets put into the database as well.

Lastly there is a suite of SQL reporting services reports ready made to look at the database, and provide you with all the information it has collected.

I did run into a couple of problems when installing it, the documentation is quite good but the interface to configure it is a bit clumsy you might be better of editing the xml config files by hand.
First thing is if you want to collect perf data from more than one machine at a central point, make sure you share the SQLH2\data directory's on the remote servers out as SQLH2PerfCollector$, and make sure the user running the exe has access to this folder.
Also I found it helped to run the perf collector service as a domain user.

Then, on the server that is collecting the data, make sure you add a new target section the the H2PerfConfigFile.xml file, with the relevant counters. There doesn't seam to be anyway to do this using the GUI config application.

Once you are up and running it seams to create some very useful stats, that might help in your next push for a server upgrade!

The only thing I couldn't get working (and I'm not sure if you can do it) was monitoring a SQL server instance on a cluster. When running the app, even on the server that was currently running the database it didn't find the instance. If any one has got this working can you let drop me and email on how to get it working.

Download the SQLH2 here

Update 28/12 - I emailed Microsoft about using it on a cluster, and the responded saying that it was'nt cluster aware. They did say though that you should be able to use the performance counters section by adding it to the xml config file on another machine. I haven't managed to get this working yet though. If anyone does please let me know

No comments: