有没有办法记录往返SQL Server浏览器的流量? [英] Is there a way to log traffic to and from SQL Server Browser?

问题描述

我不得不从备份中重建和还原的数据库服务器发生了奇怪的事情.

Something strange is happening with a database server I had to rebuild and restore from backup.

我要使用ADODB.Connection指向一个旧的VB6应用程序，并使用现代的C#EF6应用程序指向这两个应用程序，两者的形式均应使用相同的连接字符串

I'm pointing an old VB6 application using ADODB.Connection and a modern C# EF6 application at it, using what should be the same connection string for both, of the form

servername\INSTANCE

在运行SQL Server的同一台计算机上运行时，VB6应用程序和EF6应用程序都可以使用此连接字符串进行连接.

When run on the same machine that's running SQL Server, the VB6 application and EF6 application are both able to connect using this connection string.

在网络上的其他计算机上运行时，VB6应用程序会连接，但EF6应用程序不会.

When run on a different machine on the network, the VB6 application connects, but the EF6 application doesn't.

(带有标准的找不到服务器"消息，错误:26-找不到在System.Data.SqlClient.SqlInternalConnectionTds..ctor中指定的服务器/实例的错误)

(with a standard "server not found" message, error: 26 - Error Locating Server/Instance Specified at System.Data.SqlClient.SqlInternalConnectionTds..ctor)

如果我查看特定的实例端口并与之连接

If I look at the specific instance port and connect with

servername,instance_port_number

然后，无论我在哪个计算机上运行它们，这两个应用程序都将连接.因此，似乎SQL Server Browser可能正在发生某些事情来导致此问题.

then both applications connect, whatever machine I run them on. So it seems like something might be happening with SQL Server Browser to cause the issue.

是否有一种方法可以从SQL Server浏览器中获取某种诊断信息，将哪些数据发送到哪里，而又不监视所有网络流量呢?

Is there a way I can get some kind of diagnostic information out of SQL Server Browser, what data it's sent to where, without going as far as to monitor all network traffic?

推荐答案

网络跟踪的另一种解决方法是将实例枚举查询发送到SQL Server浏览器服务并检查结果.这将验证SQL Server浏览器可以通过UDP端口1434访问，并且返回的数据报包含客户端连接到命名实例所需的实例名称和端口信息.

An alternative to a network trace for troubleshooting is to send an instance enumeration query to the SQL Server Browser service and examine the results. This will verify the SQL Server Browser is reachable over UDP port 1434 and that the returned datagram contains the instance name and port information needed for the client to connect to a named instance.

在问题机器上运行下面的PowerShell脚本.

Run the PowerShell script below on the problem machine.

# 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 "Querying 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
    }

}


Get-SqlServerBrowerDatagramForAllInstances -hostNameOrIpAddress "servername"

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

这篇关于有没有办法记录往返SQL Server浏览器的流量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！