We’re currently building an SQL 2008 server cluster which will use resilient SAN-attached storage. This will provide the University with a leading edge hosted SQL service on which to safely manage its databases as well as making available a whole host of new SQL 2008 Business Intelligence services. More news about that to follow in future blogs…

In order to decide upon the best RAID configuration for the new service (i.e. whether to configure the disks to get biggest capacity or best performance) I’ve had to gather some stats about the transactions per second (TPS) on the current (SQL 2000/2005) servers. To this end, I started monitoring with SQL Profiler in conjunction with Performance Monitor. Now, I don’t know if you’ve ever done this, but let me tell you, it is something of a black art and unless I’m very much mistaken, it does come under the category of Rocket Science if only because it makes you want to hurl your computer at the moon! Fortunately, all was not lost – to my rescue came MOM: Microsoft Operations Manager. You can Google for MOM and get all sorts of info but, for a quick overview of what it is, this covers it really:

http://en.wikipedia.org/wiki/System_Center_Operations_Manager (and yes they changed the name from MOM to SCOM but we all still call it MOM out of habit…)

So, how did MOM help in this case? The MOM servers run by the Windows Infrastructure Team have (amongst a lot of other things!) a MOM SQL Management Pack installed which means that when they connect to SQL servers, they are able gather SQL specific data in addition to the other more generic monitoring of disks, memory, network connectivity, etc. So, MOM has been quietly monitoring TPS and storing the data into MOM reports for some time. All I had to do was request the report and hey presto! Here’s a couple of examples:-

This image shows a top-level report which gives a broad view of various SQL-specific data over the last quarter of 2008 for a range of performance data on one of the SQL servers:-

And then… you can drill into, for example, the Transactions per Second data and obtain a more detailed view….

A lot prettier (in so many ways!) than Perfmon and Profiler. These are just 2 basic reports but there is a wealth of other information that can be got from MOM, and not just for SQL – and not just in retrospect. MOM carries out service-specific monitoring for Exchange, IIS, Terminal Services, ISA… in fact all of the Windows Server services run by our team, keeping an eye on services – 24/7 – and alerting us to any problems or errors as soon as they arise.