Test SQL Server Browser

Test-SqlServerBrowser.ps1

# #######################################################################
# ### Test SQL Server Browser connection and display datagram results ###
# ### to help troubleshoot named instance connectivity problems.      ###
# #######################################################################

# verify UDP port 1433 connectivity and query SQL Server Browser for all instances
Function Get-SqlServerBrowerDatagramForAllInstances($hostNameOrIpAddress)
{

    Write-Host "Querying SQL Browser for all instances on host $hostNameOrIpAddress ..."

    try
    {
        $udpClient = New-Object Net.Sockets.UdpClient($hostNameOrIpAddress, 1434)
        $bufferLength = 1
        $browserQueryMessage = New-Object byte[] 1
        $browserQueryMessage[0] = 2
        $bytesSent = $udpClient.Send($browserQueryMessage, $browserQueryMessage.Length)
        $udpClient.Client.ReceiveTimeout = 10000
        $remoteEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Broadcast, 0)
        $browserResponse = $udpClient.Receive([ref]$remoteEndPoint)
        $payloadLength = $browserResponse.Length - 3
        $browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength)
        $elements = $browserResponseString.Split(";")
        Write-Host "SQL Server Browser query results:`r`n"
        for($i = 0; $i -lt $elements.Length; $i = $i + 2)
        {
            if ($elements[$i] -ne "")
            {
                Write-Host "`t$($elements[$i])=$($elements[$i+1])"
            }
            else
            {
                Write-Host ""
                # next instance
                $i = $i - 1
            }
        }
    }
    catch [Exception]
    {
        Write-Host "ERROR: $($_.Exception.Message)" -ForegroundColor Red
    }

}

# verify UDP port 1433 connectivity and query SQL Server Browser for single instance
Function Get-SqlServerBrowerDatagramByInstanceName($hostNameOrIpAddress, $instanceName)
{

    Write-Host "Quering SQL Browser for host $hostNameOrIpAddress, instance $instanceName ..."

    try
    {
        $instanceNameBytes = [System.Text.Encoding]::ASCII.GetBytes($instanceName)
        $udpClient = New-Object Net.Sockets.UdpClient($hostNameOrIpAddress, 1434)
        $bufferLength = $InstanceNameBytes.Length + 2
        $browserQueryMessage = New-Object byte[] $bufferLength
        $browserQueryMessage[0] = 4
        $instanceNameBytes.CopyTo($browserQueryMessage, 1)
        $browserQueryMessage[$bufferLength-1] = 0
        $bytesSent = $udpClient.Send($browserQueryMessage, $browserQueryMessage.Length)
        $udpClient.Client.ReceiveTimeout = 10000
        $remoteEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Broadcast, 0)
        $browserResponse = $udpClient.Receive([ref]$remoteEndPoint)
        $payloadLength = $browserResponse.Length - 3
        $browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength)
        $elements = $browserResponseString.Split(";")
        $namedInstancePort = ""
        Write-Host  "SQL Server Browser query results:`r`n"
        for($i = 0; $i -lt $elements.Length; $i = $i + 2)
        {
            if ($elements[$i] -ne "")
            {
                Write-Host  "`t$($elements[$i])=$($elements[$i+1])"
                if($elements[$i] -eq "tcp")
                {
                    $namedInstancePort = $elements[$i+1]
                }
            }
        }

    }
    catch [Exception]
    {
        Write-Host "ERROR: $($_.Exception.Message)" -ForegroundColor Red
    }

}

# ############
# ### main ###
# ############

Get-SqlServerBrowerDatagramForAllInstances -hostNameOrIpAddress "servername"

Get-SqlServerBrowerDatagramByInstanceName -hostNameOrIpAddress "servername" -instanceName "INSTANCE"