For anyone looking to find out how many SQL Servers they have deployed, and gain insight into resources used or performance - here is a selection of tools I have been using a lot on projects. Ok System Centre isn't free, but sometimes I am lucky enough to find it is already installed before we are doing a SQL discovery or assessment exercise.
|System Centre Operations Manager 2007R2
Microsoft's flagship operational management product. Uses an agent based topology to discover SQL and also capture performance and capacity wmi metrics
|- Runs 24x7 and maintains a data warehouse.
- If it is already installed is tool of choice, with most of the data you need in a query able repository
- Huge task to deploy it so not suitable for a quick discovery and inventory exercise.
- Does not capture all required metrics "out of the box" with the SQL Management pack - for example performance metrics like Page Life Expectancy and database space usage.
- Limited out of box reports for SQL inventory/analysis
- Complex data schema for querying
|Microsoft Assessment Planning Toolkit 4.0
Free agentless tool to sweep a network and discover SQL instances
- Easy to install and run
- not intrusive
- can capture performance counters
- some basic automated reports
- performance counters limited
- Annoyingly insists on installing sql express locally
|SQL Server Health and History Tool (SQLH2)
The tool allows you to collect information from instances of SQL Server, store this information, and run reports against the data in order to determine how SQL Server is being used
- open source
- very extensible wrt performance counters
- Dumps data into a nice data warehouse for reporting and analytics
- comes with some sample reports to get you started
- can be fiddly to configure
- does not do discovery only inventory
SQLPing 3.0 performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise
- Easy to run and very lightweight
- uses multiple discovery techniques.
- Better discover than MAP too
- Also does brute some password probing capability
- Only does discovery, so needs to be combined with another tool to get performance data across the enterprise
Good old performance can be configured to capture performance counters from multiple servers and the result scan be imported into a database for analysis
- zero install
- widely used
- very flexible
- not a discovery tool
- analysis of data is quite manual
- only captures perfmon counters, no hardware information.