Sql-server – What “tool” to use for monitoring instances and where to store this data

monitoringpowershellsql serversql-server-2008ssis

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?

Best Answer

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.

Related Question