7

There seems to be a bit of SQL Server sprawl at a client and I'd like to document all of them.

What is the best way to find all installed versions of SQL Server on a network?

  • Thank you. I used this tool and now have a wonderful list. – Jake Jun 11 '13 at 11:29
7

SQLPing should do the job

I've used it on medium and small networks and it seemed to find all stray SQL Server installs

| improve this answer | |
  • To note, you can easily chose to not bruteforce the hosts. The uses the below API, as well as about 10 other mechanisms to find SQL instances. – mbrownnyc Feb 9 '15 at 18:00
7

If you want to programmatically find this information, you can make a call to the SqlDataSourceEnumerator.GetDataSources Method:

# PowerShell example
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Something to note about this call, and documented in the above reference:

Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call. If you plan to use this function to let users select a server from a list, make sure that you always also supply an option to type in a name that is not in the list, in case the server enumeration does not return all the available servers. In addition, this method may take a significant amount of time to execute, so be careful about calling it when performance is critical.

Another thing you could consider is to reach out across all of your servers (again, very time consuming) and get all services that contain a known SQL Server string in their name.

Get-Service -Name "mssql*"

If you loop through all your servers and grab all services that start with "MSSQL" you'll have a rudimentary list to start with. You may have to manually filter that down though.

| improve this answer | |
4

The Microsoft Assessment and Planning (MAP) Toolkit is a Microsoft product that's specifically designed to perform discovery and help identify licensable servers.

There's a decent webcast available showing some of the canned reports, which look like they might serve your purposes.

Your mileage may vary - performing discovery can be a nightmare on complex networks!

| improve this answer | |
2

Using PowerShell :

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers()

Also, look at Generate a SQL Server Instance Inventory. It has all tools available for scanning sql servers on network.

| improve this answer | |
2

You can’t be 100% sure you’ve listed all sql servers on the network unless all are configured to work on default 1434 port. Otherwise this would turn out to be a complex task that would require scanning all non-standard ports on all servers.

If these circumstances are met you can try sqlcmd –L command line statement.

You can also download Discovery wizard tool from Quest and see if you can use it in trial mode to get the job done.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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