SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tracking data about your instances

I saw a post recently asking how to build a daily report for each instance that tracks the metrics deemed important. My response was this:

  • Create small db on every instance to hold data.
  • write scripts to gather metrics, separate table for each metric. Use a separate job to schedule each set of metrics as needed (some daily, some hourly, etc.)
  • Put a report table in the db that just holds char fields, an instance name, and an ordering field
  • Write a proc that "builds" a report, putting lines of data in the report table, ordering them as needed, and including the instance name. This gives you a report daily, on each server.
  • Write a script (SMO/Powershell/LinkedServers/etc) to roll up all reports from all instances to one central server.

Seems a little silly, but this was the fourth of fifth evolution of a monitoring system, and I even had Patrol and Unicenter on top of those at times. However this worked better.


1. Each server captures it’s own metrics. Critical in the case of communication errors. I’ve had servers disconnected (not down, but off the network) for ten minutes while someone replaced a cable. And that 10 minute window is ALWAYS when I’m building a report or looking for metrics.

2. Customizable. I can write separate scripts for anything I need, and even update them over time. For example, one thing I used to do was capture sp_configure information. I didn’t want it on a report unless something changed. So I stored the data every day, loaded a new copy the next day, compared them for changes, and then wrote out to my report table anything that was different.

3. Separate schedules for metrics. I might want CPU every 5 minutes, but sp_configure every day. I can easily have separate schedules.

4. I get one report a day, and I stored off the list of servers in this central instance. If I didn’t get a report from a server, I’d go looking for it. New ones were reported to me and people soon realized that if they let me know of their new instances they didn’t ever have to worry about them.

5. I have the raw data and the report stored off. This satisfied my ISO 9001 requirements, which was great for me.

I’m not knocking the purchased monitoring solutions, and they work great for having a central team monitor things, but they’ve always required some customization to work for me, and in the same amount of time I could build my own reporting system easily. A few scripts covered all sorts of things that often weren’t built into large systems, or cost extra.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Glenn Berry on 16 March 2010

Yeah, I like to roll my own, local monitoring solutions just like you describe. Its more fun, you will probably learn something while doing it, and it is free (outside of your labor).

Posted by Steve Jones on 16 March 2010

I actually used to have my own setup scripts for this. A package that would build a DB from SQLCMD and then install all the tables, setup jobs. The same one could be run on each instance and then you just had manual config from the central instance. Nice thing is you can easily have separate scripts for different versions, different monitoring capabilities

Posted by Anonymous on 16 March 2010

Pingback from  Guidelines For Looking Through A Forex Robot Software Review | Insider Forex Secrets Guide

Posted by Jason Brimhall on 17 March 2010

This is the same kind of method that I employ.

nice read.

Posted by Anonymous on 19 March 2010

Pingback from  Forex Trading Profits ? a Forex Trading Method for Huge Gains | Insider Forex Secrets Guide

Leave a Comment

Please register or log in to leave a comment.