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..