2

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?

Improve this question

4 Answers 4

Reset to default
1

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.

Improve this answer
2
  • Yes, exactly, free space, backup checks etc.
    – jrara
    Commented Jan 20, 2012 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
    Commented Jan 20, 2012 at 5:10
3
  • sql sentry
  • Quest spotlight for sql server enterprise
  • Microsoft Operations Manager
  • Various RedGate tools
  • Your own scripts, data collectors and jobs
  • Windows Performance monitor
Improve this answer
1
  • 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
    Commented Jan 19, 2012 at 19:06
2

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.

Improve this answer
0

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.

HTH

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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