I was asked recently by a customer to discover all SQL servers in an Active Directory Domain. This script code provides two discovery methods: 1) by searching AD for registered SQL SPNs (Service Principal Names) and 2) by enumerating all Windows servers and checking each for SQL Services.  Obviously the first method is quite a bit faster since it only queries AD for the SQL SPN list while the second queries AD for a list of servers and then has to connect to each to check for appropriate SQL services.

Here’s some code:

Param
    (
    [string] $AutoDiscover = “AD”,
    )
$SQLServerListFile = “c:\temp\SqlServerList.txt”
$DomainDNS = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name #Get AD Domain (lightweight & fast method)

write-output “Configuring Powershell environment… `r “
Write-Verbose “Importing Active Directory Powershell module `r”
import-module ActiveDirectory

########################
# Discover SQL Servers #
########################
IF ($AutoDiscover -eq “Scan”)
    {  ## OPEN IF AutoDiscover Scan
        Write-Verbose “AutoDiscover mode set to Scan. The script will check all Windows servers for SQL services. `r”
        [array] $AllServers = Get-ADComputer -filter { (OperatingSystem -like “*Windows*”) -and (OperatingSystem -like “*Server*”) -and (Enabled -eq $True) } -properties Name, DistinguishedName, OperatingSystem, passwordLastSet
        $AllServersCount = $AllServers.Count
        Write-Output “There were $AllServersCount Windows Servers discovered in $DomainDNS … `r”
        Write-Verbose “Compiling Server List `r”
        ForEach ($Computer in $AllServers)
            { ## OPEN ForEach
                  [array] $ServerList += $Computer.Name
            } ## CLOSE ForEach
        $ServerList = $ServerList | sort-object

        Write-Verbose “Connecting to each Windows server to check for a SQL instance… `r”
        ForEach ($Computer in $ServerList)
            {  ## OPEN ForEach Computer in AllServers
                $ComputerNum++
                Write-Verbose “Checking connectivity to $Computer … `r”
                # Ping computer
                $Results = Test-Path \\$computer\c$
                IF ($Results = “True”) ## Ignore RPC Connect
                  {  ## OPEN IF Continue if RPC Connect
                     Write-Verbose “$computer is accessible via RPC. Continuing… `r”
                     Write-Verbose “Checking for SQL services on $Computer [$ComputerNum out of $AllServersCount] … `r”
                     $SQLServerCheck = Get-Service -Name MSSQL* -ComputerName $Computer -ErrorAction SilentlyContinue
                     IF ($SQLServerCheck) { [array] $DiscoveredSQLServers += $Computer ; Write-Verbose “$Computer is running Microsoft SQL … `r” }    
                       ELSE { Write-Verbose “NO SQL services were discovered on $Computer … `r” }
                  }  ## OPEN IF Continue if RPC Connect
                ELSE { Write-Warning “Unable to connect to $Computer via RPC. The Server may be down or inaccessible. Skipping SQL service check. `r” }
            }  ## CLOSE ForEach Computer in AllServers
        $DiscoveredSQLServersCount = $DiscoveredSQLServers.Count
        Write-Output “There were $DiscoveredSQLServersCount discovered SQL servers in $DomainDNS `r”
        [array] $SQLServers = $DiscoveredSQLServers

        Write-Verbose “Saving the discovered list of SQL servers to $SQLServerListFile `r”
        $SQLServers | out-file $SQLServerListFile -Force
    }  ## CLOSE IF AutoDiscover Scan

IF ($AutoDiscover -eq “AD”)
    {  ## OPEN IF AutoDiscover AD
        Write-Verbose “AutoDiscover mode set to AD. The script will query AD for registered SPNs and their associated server names. `r”
        [array] $AllSQLServers = Get-ADComputer -filter { (OperatingSystem -like “*Windows*”) -and (OperatingSystem -like “*Server*”) -and (Enabled -eq $True) -and (ServicePrincipalNames -like “*MSSQLSvc*”) } -properties Name, ServicePrincipalNames, DistinguishedName, OperatingSystem, passwordLastSet
        $AllSQLServersCount = $AllSQLServers.Count
        Write-Output “There were $AllSQLServersCount SQL Servers discovered in $DomainDNS … `r”
        Write-Verbose “Compiling Server List `r”
        ForEach ($Computer in $AllSQLServers)
            { ## OPEN ForEach
                  [array] $ServerList += $Computer.Name
            } ## CLOSE ForEach
        $ServerList = $ServerList | sort-object

        [array] $SQLServers = $ServerList

        Write-Verbose “Saving the discovered list of SQL servers to $SQLServerListFile `r”
        $SQLServers | out-file $SQLServerListFile -Force        
    }  ## CLOSE IF AutoDiscover AD 

NOTE: I may have missed defining a couple of variables when pulling this out of a larger script.