SQL Server Discovery Tools and Scripts

Each time I start a new contract, the first thing I do is search for all SQL Servers across any network/subnet to which I have access. While the documentation I get is usually paltry or non-existent. When it does exist, it’s usually only for the production SQL Servers. Sometimes, most production SQL Servers aren’t even documented; one of my clients only had 20 documented servers, and I found over 60 (including SQL Express instances, of course.)

So here I am again, starting a new contract and I’m out looking for SQL Discovery Tools. Ultimately, here are the 5 I ended up finding and using, in no particular order:

Idera SQL Discovery is a really great tool within the Idera SQL Toolbox. That one’s good and , which has been in beta for years.

A new tool I discovered this time around is the Microsoft Assessment and Planning Toolkit. Free (as in beer), of course.

Next is a quick lil script that grabs all SQL Servers in Active Directory that contain the word “SQL”

Next is a more thorough script written by Colin Smith which grabs a list of servers within a text file and probes their services. I created the list with the script above, though I replaced “*SQL*” with “*Server*”

Now to get all these SQL Servers registered..

Chrissy is a Cloud and Datacenter Management MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in Active Directory, PowerShell, SQL Server
3 comments on “SQL Server Discovery Tools and Scripts
  1. Jelle says:

    Thanks for sharing this, although matching on computer-name is not bulletproof for my environment.

    Other solution could be using Powershell command:
    [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
    (finds SQL Servers where SQLBrowser is enabled)

    Or use command: SQLCMD -L
    (also returns instance name, you could catch the result in SSIS and parse it, like i did)

    Guess a really solid solution would be scanning for registersettings though, but then you will need to have the proper rights to do that aswell.

    Best wishes,
    Jelle

  2. Franke says:

    Thanks! Idera is a def. a good server and application management tool.

1 Pings/Trackbacks for "SQL Server Discovery Tools and Scripts"
  1. […] on the network. It’s my usual routine to get permission to sniff the network then run about five different programs including Idera’s SQL Discovery and Microsoft’s SQL Server Assessment and Planning […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*