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"

1import-module activedirectory
2get-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"

 1#####################################################################################
 2## Author: Colin Smith
 3## Script: Get_Intstance_names.ps1
 4## Purpose: This script will read in a file of hotnames that has been
 5##          Provided of servers with SQL Server running on them. This
 6##			script will then look at the services on that host to find
 7##			the instance name if the instance is named. If the instance
 8##			is a default instance the script will also report that.
 9#####################################################################################
10$Servers = get-content "C:\servers.txt"
11
12echo "Server, Instance" >> "C:\sqltab.txt"
13        foreach ($server in $servers)
14       {
15              $instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}
16
17              if (!$?)
18                     {
19                          echo "Failure to connect on $server" >> "C:\failures.txt"
20                          echo "Failure to connect on $server"
21                     }
22              else
23                     {
24                          $instancenames = @()
25                          foreach ($name in $instances)
26                     {
27                          if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*"))
28                                   {
29                                          $instancenames += $name.name
30                                   }
31                     }
32                          foreach ($iname in $instancenames)
33                     {
34                                          echo "$server, $iname" >> "C:\sqltab.txt"
35                                          echo "$server, $iname"
36                     }
37              }
38         }

Now to get all these SQL Servers registered..