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.

Posted in Active Directory, PowerShell, SQL Server
2 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.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">