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?