Data Collection from SQL 2005 Instances

Dec 23, 2011 at 10:00 PM

Under http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=321 you say to use "what Microsoft is doing ... and use that information to collect it against “lower” versions of SQL Server, in particular SQL Server 2005."

First of all, it appears that you extract the Data Collection source code from 2008 and then run it on your 2005 servers after confirming that the underlying query(s) run cleanly.  This creates a data collection set - a remote job that is scheduled on the 2005 instance - that feeds data into the central management server running on 2008.  Is this correct?  It seems counter-intuitive to me that we have a central management server that relies on the scheduler of the remote instance.

Assuming that I'm correct (that the data collection is initiated on the remote server), SQL 2005 doesn't have a "Data Collection" branch in the object explorer so I assume that you have to do everything through SQL commands.  Can you show some examples or recommend other site that explain how to set this up.

Ken

Dec 31, 2011 at 10:55 PM

Ken - sorry for the late reply. I've been on vacation and just back now.

There are two concepts in monitoring. One is to "push" the information from a monitored system, the other is to "pull" it periodically from a central server. After years of working with multiple system types, from mainframes to micro's on various OS's, I've found the best approach is to have each node collect it's own information and then sweep by with the main system to collect it. This allows for the greatest resilience, scale, independence and so on. However, you're more than welcome to modify the system to your liking. My approach certainly isn't the only way to do it.

If you do change to another paradigm, make sure you blog it or comment it here - that allows others to learn from what you're doing.

 

Thanks!

 

- Buck