Skip to content

Instantly share code, notes, and snippets.

@guzmanda
Created February 16, 2019 12:26
Show Gist options
  • Save guzmanda/a98574b95014b757ec1da6c29f0b42df to your computer and use it in GitHub Desktop.
Save guzmanda/a98574b95014b757ec1da6c29f0b42df to your computer and use it in GitHub Desktop.
Test SQL Server Browser
# #######################################################################
# ### 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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment