Tools for SQL Discovery

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.

Tool	Pros
System Centre Operations Manager 2007R2Microsoft'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 repositoryCons- 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.0https://technet.microsoft.com/en-us/solutionaccelerators/dd537566.aspxFree agentless tool to sweep a network and discover SQL instances	Pros - Easy to install and run- Agentless- not intrusive- can capture performance counters- some basic automated reportsCons- performance counters limited- Annoyingly insists on installing sql express locally
SQL Server Health and History Tool (SQLH2)https://www.codeplex.com/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	Pros- 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 startedCons- can be fiddly to configure- does not do discovery only inventory
SQL Pinghttps://www.sqlsecurity.com/Tools/ FreeTools/tabid/65/Default.aspxSQLPing 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	Pros- Easy to run and very lightweight- uses multiple discovery techniques.- Better discover than MAP too- Also does brute some password probing capabilityCons- Only does discovery, so needs to be combined with another tool to get performance data across the enterprise
PerfmonGood old performance can be configured to capture performance counters from multiple servers and the result scan be imported into a database for analysis	Pros- zero install- widely used- very flexible- AgentlessCons- not a discovery tool- analysis of data is quite manual- only captures perfmon counters, no hardware information.

Comments

• Anonymous
  September 21, 2009
  What about the best freebie around - SQLRecon ?