Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server instances for monitoring purposes. My problem is what "tool" to use and where to store this administration data. My choices are

  • Create an admin database for each instance and store monitoring data, queries and stored procedures separately
  • Create an admin database for e.g development database and query data from all instances into this database
  • Use SSIS to gather data from all instances and store this data into admin database (dev instance)?
  • Use PowerShell to query data from all instances and store this data into admin database (dev instance?)

What other choices do I have?

share|improve this question
up vote 1 down vote accepted

What data are you wanting to collect? If your are just looking for free space, backups checks etc.. and have an instance of SQL Server 2008 handy you could use Central Management Server and Policy Based Management and PowerShell. I did a talk about this at SQL PASS Member Summit and SQL Rally this year. You can grab my resources here

Personally, I also would want to store my monitoring information on a development server. I wouldn't want to use up the buffer pool on a production box for monitoring.

I hope this helps you out.

share|improve this answer
Yes, exactly, free space, backup checks etc. – jrara Jan 20 '12 at 4:46
Ok, then the Enterprise Policy Based Management Framework could be perfect for you. You can so go with Central Management Server and PowerShell. Here is a blog post I created that has a framework for collecting meta-data about your servers. – JohnS Jan 20 '12 at 5:10
  • sql sentry
  • Quest spotlight for sql server enterprise
  • Microsoft Operations Manager
  • Various RedGate tools
  • Your own scripts, data collectors and jobs
  • Windows Performance monitor
share|improve this answer
Thanks, but I cannot afford third party tools like RedGate. My intention was to question what is the most "logical" place to store monitoring data from three instances and what tools to use (SSIS, PowerShell, or stored procedures). – jrara Jan 19 '12 at 19:06

I have been thinking about a similar solution in our own environment. My ideas have been gearing towards the last two options (ssis & powershell). By having a single instance of your admin database you will be able to generate one report for all servers and do comparisons among your different instances.

The reason I have been leaning towards ssis or powershell is that I would then have the ability to create a configuration table(s) in my admin database listing off all the instances I want to gather data for. Then the powershell\ssis process could be tailered to use the list of instances to dynamically pull data. This will then allow flexibility when new servers are added, all you have to do is add the instance name to your configuration table and the data will start being gathered for the new instance.

share|improve this answer

minidba might suit your needs - the free version won't quite give you enough functionality to view historic data but the pay version will and its price is probably way less than 1-2 days of dba salary it will take to roll your own.


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.