» SQL Server Discovery Tools and Scripts

24May/120

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"

                     }

              }

         }

Next up, I'd like to investigate the SQL Server Active Directory Helper service and see what that's all about.. then get all these SQL Servers registered.