What is the most effective way to discover all running instances of SQL Server using PowerShell?

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.


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.


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

Tags:

Sql Server

Powershell

Smo

Recent Posts