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”

import-module activedirectory
get-adcomputer -filter "Name -like '*SQL*'" |select Name

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*”

#####################################################################################
## Author: Colin Smith
## Script: Get_Intstance_names.ps1
## Purpose: This script will read in a file of hotnames that has been
##          Provided of servers with SQL Server running on them. This
##			script will then look at the services on that host to find
##			the instance name if the instance is named. If the instance
##			is a default instance the script will also report that.
#####################################################################################
$Servers = get-content "C:\servers.txt"

echo "Server, Instance" >> "C:\sqltab.txt"
        foreach ($server in $servers)
       {
              $instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}

              if (!$?)
                     {
                          echo "Failure to connect on $server" >> "C:\failures.txt"
                          echo "Failure to connect on $server"
                     }
              else
                     {
                          $instancenames = @()
                          foreach ($name in $instances)
                     {
                          if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*"))
                                   {
                                          $instancenames += $name.name
                                   }
                     }
                          foreach ($iname in $instancenames)
                     {
                                          echo "$server, $iname" >> "C:\sqltab.txt"
                                          echo "$server, $iname"
                     }
              }
         }

Now to get all these SQL Servers registered..

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in Active Directory, PowerShell, SQL Server