13

I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem to find all the instances.

1) Use WMI

        $srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName
    $instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}}   
    return $instances

2) Use remote registry (as with Get-SQLInstance1)

The biggest problem I'm running into is that not all of the servers that I know about are running with the SQL Server WMI provider nor are all of them allowing remote registry. Is there a third method? I can use Remote Desktop to access all the servers but I'm looking at approximately 30 machines and would like to avoid manual steps if possible. This only needs to work for SQL Server 2008 and higher and while it would be nice to know about the other SQL Server services (SSIS/SSAS/SSRS) my main focus is on SQL Server itself.

12

If you want something that will be useful for the future I would probably steer clear of trying to search the registry. The hives for SQL Server have changed a bit over the years and it can be troublesome to keep up with.

The method with the SqlDataSourceEnumerator is flaky at times and although I will use it, not concrete evidence that instances are on the network. I believe it depends on SQL Browser Service as well, which most of the time I find disabled.

I will utilize the WMI class win32_Service. I use this because it offers up more information about the service than Get-Service cmdlet does.

I write everything as functions generally because you can use this to actually just do daily check or verification of the service for troubleshooting.

function Get-ServiceStatus ([string[]]$server)
{
 foreach ($s in $server)
 {
   if(Test-Connection $s -Count 2 -Quiet)
   {
    Get-WmiObject win32_Service -Computer $s |
     where {$_.DisplayName -match "SQL Server"} | 
     select SystemName, DisplayName, Name, State, Status, StartMode, StartName
   }
 }
}

This is a bit more than what I usually use but in case someone else comes across and wants to use it. The Test-Connection equates to ping myserver in a DOS prompt and the -Quiet flag simply just has it return true or false. This will default to 4 pings so setting -Count 2 just makes it do it twice instead.

The variable [string[]]$server is a method used to state that $server will accept an array of server names. So an example call of this function could look something like:

Get-ServiceStatus -server (Get-Content C:\temp\MyServerList.txt)

or

$servers = 'MyServer1','MyServer2','MyServer3'
Get-ServiceStatus -server $servers

EDIT

A noted comment is the above does depend on a list of servers being provided. In cases where I am not provided that list you do have a few other options.

  • If I am in an Active Directory environment I can use the ActiveDirectory module in PowerShell to pull a list of all the servers on the domain with Get-ADComputer cmdlet. A word of warning though make sure you use a good -Filter on large domains.

  • I have also simply done an IP scan (with approval) of a network that gives me the IP addresses where port 1433 was found open. I will take that IP list and utilize Get-ADComputer to find the domain computer names, then pass that into the function above

Example:

Import-Module ActiveDirectory
$sList = $ipList | Select -ExpandProperty IP
$results = foreach ($i in $sList) { 
 Get-ADComputer -Filter 'IPv4Address -eq $i' -Properties * | Select Name}
Get-ServiceStatus -server $results

EDIT

The suggested edit to utilize Write-Verbose and also add in try/catch block, while that may be useful, and in most cases a code practice, I will leave that up to the person that wants to use this function to add that additional code or functionality. Just trying to provide a basic example to go on. I did add the SystemName property to the output to include the actual server name returning information, do this on other functions just generally don't use this for more than one server at a time so it slipped my mind.

  • That works provided you are supplied with a list of servers to begin with. That can't always be assumed. – Thomas Stringer Oct 15 '14 at 16:14
  • Just for clarity limiting the scanning to port 1433 will leave out any servers with only named instances (or with default instances hard-coded to use a different port). Maybe not a big deal but there are plenty of paranoid folks out there who shut down that port enterprise-wide. – Aaron Bertrand Oct 15 '14 at 17:11
  • True, it is just a starting point. The ones where ports are generally set I have found clients usually have those servers noted (aware of them). Did find this method by Brian Kelley but have not tried it. – Shawn Melton Oct 15 '14 at 19:07
  • I think combining both the registry method and the win32_service WMI as a fallback should get the majority of servers, and then a manual search of the remainder will work. As a pleasant side effect, I can also pull some information on services that are running but aren't needed, servers that aren't allowing me access, etc. – Elsimer Oct 15 '14 at 20:27
5

The only way that I know of discovering instances across an environment without knowing all of the possible owning servers and their particular names, would be to make a call to System.Data.Sql.SqlDataSourceEnumerator.GetDataSources(). This method comes with a lot of footnotes, though. Here is a snippet that is pulled directly from that MSDN resource:

Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call. If you plan to use this function to let users select a server from a list, make sure that you always also supply an option to type in a name that is not in the list, in case the server enumeration does not return all the available servers. In addition, this method may take a significant amount of time to execute, so be careful about calling it when performance is critical.

The call is simple from PowerShell:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

That method returns a DataTable object which you can handle accordingly.

3

If the SQL Browser Service is active, you can query the service for SQL Instances with the PowerShell code below. It implements the following commandlets to perform the queries:

  • Get-SqlBrowserInstanceList
  • Get-SqlBrowserInstanceInfo
  • Get-SqlBrowserInstanceDac

    function Parse-ServerResponse([byte[]] $responseData)
    {
        [PSObject[]] $instances = @()
    
        if (($responseData -ne $null) -and ($responseData[0] -eq 0x05))
        {
            $responseSize = [System.BitConverter]::ToInt16($responseData, 1)
    
            if ($responseSize -le $responseData.Length - 3)
            {
                # Discard any bytes beyond the received response size. An oversized response is usually the result of receiving multiple replies to a broadcast request.
                $responseString = [System.Text.Encoding]::Default.GetString(($responseData | Select -Skip 3 -First $responseSize))
                $instanceResponses = $responseString.Split(@(";;"), [System.StringSplitOptions]::RemoveEmptyEntries)
    
                $instances = foreach ($instanceResponse in $instanceResponses)
                {
                    $instanceResponseValues = $instanceResponse.Split(";")
                    $instanceResponseHash = @{}
                    for ($index = 0; $index -lt $instanceResponseValues.Length; $index += 2)
                    {
                        $instanceResponseHash[$instanceResponseValues[$index]] = $instanceResponseValues[$index + 1]
                    }
    
                    New-Object PSObject -Property $instanceResponseHash
                }
            }
            else
            {
                Write-Warning "The response was too short. Expected $($responseSize) bytes but got $($responseData.Length - 3)."
            }
        }
    
        return ,$instances
    }
    
    function Parse-ServerResponseDac([byte[]] $responseData)
    {
        $dacPort = 0
    
        if (($responseData -ne $null) -and ($responseData[0] -eq 0x05))
        {
            $responseSize = [System.BitConverter]::ToUInt16($responseData, 1)
    
            if (($responseData.Length -eq 6) -and ($responseSize -eq 6))
            {
                if ($responseData[3] -eq 0x01)
                {
                    $dacPort = [System.BitConverter]::ToUInt16($responseData, 4)
                }
                else
                {
                    Write-Error "An unexpected protocol version was returned. Expected 0x01 but got $($requestData[3])."
                }
            }
            else
            {
                Write-Error "The response size was incorrect."
            }
        }
    
        return $dacPort
    }
    
    function Get-SqlBrowserInstanceList
    {
        <#
        .SYNOPSIS
        Gets the list of available SQL Instances on the server.
        .DESCRIPTION
        Gets the list of available SQL Instances on the server by querying the SQL Browser Service on port 1434.
        .EXAMPLE
        Get-SqlBrowserInstanceList servername
        .EXAMPLE
        Get-SqlBrowserInstanceList servername.dnsdomain.tld
        .EXAMPLE
        Get-SqlBrowserInstanceList $env:COMPUTERNAME
        .EXAMPLE
        Get-SqlBrowserInstanceList 192.168.1.255 -Broadcast
        .EXAMPLE
        Get-SqlBrowserInstanceList 255.255.255.255 -Broadcast
        .PARAMETER $ServerName
        The name or IP Address of the server.
        .PARAMETER $Broadcast
        If the broadcast switch is specified, the query will be sent as a broadcast and may receive replies from multiple hosts; otherwise, the query is sent to a single server.
        #>
        [CmdletBinding(SupportsShouldProcess = $False)]
        param
        (
            [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
            [string] $ServerName,
            [switch] $Broadcast
        )
    
        process
        {   
            [System.Net.IPAddress] $ipAddress = [System.Net.Dns]::GetHostAddresses($serverName) | Select -First 1
            $parsedResponses = @()
    
            if ($ipAddress -ne $null)
            {
                [System.Net.IPEndPoint] $localIPEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Any, 0)
                [System.Net.IPEndPoint] $remoteIPEndPoint = New-Object System.Net.IPEndPoint($ipAddress, 1434)
    
                if ($ipAddress -eq [System.Net.IPAddress]::Broadcast)
                {
                    $Broadcast = $true
                }
    
                [System.Net.Sockets.UdpClient] $receiver = New-Object System.Net.Sockets.UdpClient
                $receiver.Client.ReceiveTimeout = 30000
    
                [byte] $queryMode = 0x03
                $sleepDuration = 1
                [System.Net.Sockets.UdpClient] $sender = $null
    
                if ($Broadcast -eq $true)
                {
                    Write-Verbose "Using broadcast mode."
                    $queryMode = 0x02
                    $sleepDuration = 30
    
                    # Set the receiver to allow another client on the same socket.
                    $receiver.Client.SetSocketOption([System.Net.Sockets.SocketOptionLevel]::Socket, [System.Net.Sockets.SocketOptionName]::ReuseAddress, $true)
                    $receiver.Client.Bind($localIPEndPoint)
    
                    # Because broadcasting from this UdpClient instance causes the underlying socket to be unable to receive normally, a separate sender must be bound to the same socket as the receiver.
                    # NOTE: Windows Firewall does not view a reused socket as being part of the same conversation. If Windows Firewall is active, this requires special firewall rules to work.
                    $sender = New-Object System.Net.Sockets.UdpClient
                    $sender.EnableBroadcast = $Broadcast
                    $sender.Client.SetSocketOption([System.Net.Sockets.SocketOptionLevel]::Socket, [System.Net.Sockets.SocketOptionName]::ReuseAddress, $true)
                    $sender.Client.Bind($receiver.Client.LocalEndPoint);
                }
                else
                {
                    $sender = $receiver
                    $receiver.Client.Bind($localIPEndPoint)
                }
    
    
                $responses = @{}
    
                try
                {
                    # Send the broadcast.
                    Write-Verbose "Sending request to $($ipAddress)..."
                    $sender.Connect($remoteIPEndPoint)
                    $bytesSent = $sender.Send(@($queryMode), 1)
    
                    # Wait to give responses time to arrive.
                    Sleep $sleepDuration
    
                    do
                    {
                        [System.Net.IPEndPoint] $responderIPEndPoint = $null
                        $response = $receiver.Receive([ref] $responderIPEndPoint)
                        $responder = $responderIPEndPoint.ToString()
    
                        if ($responses.Contains($responder))
                        {
                            $responses[$responder] += $response
                        }
                        else
                        {
                            $responses.Add($responder, $response)
                        }
                    } while ($receiver.Available -gt 0)
                }
                finally
                {
                    if ($sender -ne $receiver)
                    {
                        $sender.Close()
                        $sender.Dispose()
                    }
    
                    $receiver.Close()
                    $receiver.Dispose()
                }
    
                foreach ($responseItem in $responses.GetEnumerator())
                {
                    Write-Verbose "Parsing the response from $($responseItem.Name)..."
                    $parsedResponse = Parse-ServerResponse $responseItem.Value
                    $parsedResponses += $parsedResponse
                    Write-Verbose ($parsedResponse | ft ServerName, InstanceName, tcp, np, Version, IsClustered -AutoSize |Out-String)
                }
            }
    
            return $parsedResponses
        }
    }
    
    function Get-SqlBrowserInstanceInfo
    {
        <#
        .SYNOPSIS
        Gets information about the specified SQL Instance from the server.
        .DESCRIPTION
        Gets information about the specified SQL Instance from the server by querying the SQL Browser Service on port 1434.
        .EXAMPLE
        Get-SqlBrowserInstanceInfo servername instancename
        .EXAMPLE
        Get-SqlBrowserInstanceInfo servername.dnsdomain.tld instancename
        .EXAMPLE
        Get-SqlBrowserInstanceInfo $env:COMPUTERNAME
        .PARAMETER $ServerName
        The name or IP Address of the server.
        .PARAMETER $InstanceName
        The name of the SQL Instance.    #>
        [CmdletBinding(SupportsShouldProcess = $False)]
        param
        (
            [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
            [string] $ServerName,
            [Parameter(Mandatory = $True, ValueFromPipeLine = $False)]
            [string] $InstanceName
        )
    
        process
        {   
            $instances = @()
            [System.Net.IPAddress] $ipAddress = $null
    
            $ipAddress = [System.Net.Dns]::GetHostAddresses($serverName) | Select -First 1
    
            if ($ipAddress -ne $null)
            {
                [System.Net.IPEndPoint] $ipEndPoint = New-Object System.Net.IPEndPoint($ipAddress, 1434)
                [System.Net.Sockets.UdpClient] $udpClient = New-Object System.Net.Sockets.UdpClient
                $udpClient.Client.ReceiveTimeout = 10000
    
                $instanceNameData = [System.Text.Encoding]::Default.GetBytes($instanceName)
                [byte[]] $requestData = @(0x04) + $instanceNameData + 0x00
                [byte[]] $responseData = $null
    
                try
                {
                    $udpClient.Connect($ipEndPoint)
    
                    $bytesSent = $udpClient.Send($requestData, $requestData.Length)
    
                    $responseData = do
                    {
                        $udpClient.Receive([ref] $ipEndPoint)
                    } while ($udpClient.Available -gt 0)
                }
                finally
                {
                    $udpClient.Close()
                    $udpClient.Dispose()
                }
    
                $instances = Parse-ServerResponse $responseData
            }
    
            return $instances
        }
    }
    
    function Get-SqlBrowserInstanceDac
    {
        <#
        .SYNOPSIS
        Gets the Dedicated Administrator Connection port number for the specified SQL Instance on the server.
        .DESCRIPTION
        Gets the Dedicated Administrator Connection port number for the specified SQL Instance on the server by querying the SQL Browser Service on port 1434.
        .EXAMPLE
        Get-SqlBrowserInstanceDac servername instancename
        .EXAMPLE
        Get-SqlBrowserInstanceDac servername.dnsdomain.tld instancename
        .EXAMPLE
        Get-SqlBrowserInstanceDac $env:COMPUTERNAME instancename
        .PARAMETER $ServerName
        The name or IP Address of the server.
        .PARAMETER $InstanceName
        The name of the SQL Instance.
        #>
        [CmdletBinding(SupportsShouldProcess = $False)]
        param
        (
            [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
            [string] $ServerName,
            [Parameter(Mandatory = $True, ValueFromPipeLine = $False)]
            [string] $InstanceName
        )
    
        process
        {   
            [System.UInt16] $dacPort = 0
            [System.Net.IPAddress] $ipAddress = $null
    
            $ipAddress = [System.Net.Dns]::GetHostAddresses($serverName) | Select -First 1
    
            if ($ipAddress -ne $null)
            {
                [System.Net.IPEndPoint] $ipEndPoint = New-Object System.Net.IPEndPoint($ipAddress, 1434)
                [System.Net.Sockets.UdpClient] $udpClient = New-Object System.Net.Sockets.UdpClient
                $udpClient.Client.ReceiveTimeout = 30000
    
                $instanceNameData = [System.Text.Encoding]::Default.GetBytes($instanceName)
                [byte[]] $requestData = @(0x0F) + 0x01 + $instanceNameData + 0x00
                [byte[]] $responseData = $null
    
                try
                {
                    $udpClient.Connect($ipEndPoint)
    
                    $bytesSent = $udpClient.Send($requestData, $requestData.Length)
    
                    $responseData = do
                    {
                        $udpClient.Receive([ref] $ipEndPoint)
                    } while ($udpClient.Available -gt 0)
                }
                finally
                {
                    $udpClient.Close()
                    $udpClient.Dispose()
                }
    
                $dacPort = Parse-ServerResponseDac($responseData)
            }
    
            return $dacPort
        }
    }
    
2

Another way of identifying possible SQL Instances is to look at the Service Principle Names (SPNs) listed in Active Directory. When you connect to SQL Server remotely with Windows Authentication an SPN is used in the authentication process. The presence of an SPN does not mean that the server/instance is definitely there and running but it does give you a list of possible instances which I've found to be more comprehensive the some of the other approaches.

To make life easier I use the Get-SPN cmdlet from: https://gallery.technet.microsoft.com/scriptcenter/Get-SPN-Get-Service-3bd5524a

Download the Get-SPN.ps1 script, save it to C:\powershell_scripts\Get-SPN.ps1 and run the following in PowerShell:

. "C:\powershell_scripts\Get-SPN.ps1"
Get-SPN -ServiceClass MSSQLSvc

(Obviously you can save the script to another location, just update the first line as required.)

This will list all SQL Server SPNs on the current domain, including the "specification" which relates to the port/instance of the service.

  • I've noticed that most of our SQL Server machines are unable to get SPNs (or some such warning in the maintenance log). Will they still show up using that script? – Elsimer May 20 '15 at 18:18
  • That's usually because the service runs as a user which isn't a domain admin or local system (required to create SPN). The domain admin has probably added SPNs using the SetSPN utility and their domain admin account, so that domain auth works properly for these machines. So likely, yes. – Matt May 20 '15 at 23:30
0

Get-Service -ComputerName *MSSQL* | Where-Object {$_.status -eq "Running"}

That should get named and default instances. Just iterate your list of machines, etc.

-4

Just tried this :[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Not a lot of data returned but it did detect all the sql servers I have running in a VM environment.

  • 6
    That method is already included in Thomas Stringer's answer. – MDCCL Nov 28 '18 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.