使用.NET Core 3.1在网络上搜索所有SQL Server实例

Question

我的应用程序要求用户在其网络上选择一个SQL Server实例作为应用程序的数据库服务器。我需要填充所有可用SQL Server实例的列表，以便从中进行此选择。

该应用程序是在.NET Core 3.1中开发的。我无法简单地使用SqlDataSourceEnumerator，因为它在.NET Core中不可用。

是否有其他选项可用于进行此查询

如果可能的话，我希望避免将PowerShell功能导入应用程序，因为这可能需要在计算机上安装额外的PowerShell模块，但如果这是唯一的方法，我会考虑。

Answer 1

我在.NET Core中找到的唯一方法是手动发送和接收UDP消息。

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Threading;
using ReporterAPI.Utilities.ExtensionMethods;
using Serilog;
public class SqlServerScanner : ISqlServerScanner
{
private readonly ConcurrentDictionary<string, SqlServerInstance> serverInstances = new ConcurrentDictionary<string, SqlServerInstance>();

private DateTime lastScanAttemptTime = DateTime.MinValue;
private readonly TimeSpan cacheValidTimeSpan = TimeSpan.FromSeconds(15);
private readonly TimeSpan responseTimeout = TimeSpan.FromSeconds(2);
private readonly List<UdpClient> listenSockets = new List<UdpClient>();
private readonly int SqlBrowserPort = 1434; // Port SQL Server Browser service listens on.
private const string ServerName = "ServerName";
public IEnumerable<SqlServerInstance> GetList()
{
ScanServers();
return serverInstances.Values;
}
private void ScanServers()
{
lock (serverInstances)
{
if ((DateTime.Now - lastScanAttemptTime) < cacheValidTimeSpan)
{
Log.Debug("Using cached SQL Server instance list");
return;
}
lastScanAttemptTime = DateTime.Now;
serverInstances.Clear();
try
{
var networksInterfaces = Dns.GetHostEntry(Dns.GetHostName()).AddressList;
foreach (var networkInterface in networksInterfaces.Where(ip => ip.AddressFamily == AddressFamily.InterNetwork))
{
Log.Debug("Setting up an SQL Browser listen socket for {address}", networkInterface);
var socket = new UdpClient { ExclusiveAddressUse = false };
socket.Client.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReuseAddress, true);
socket.Client.Bind(new IPEndPoint(networkInterface, 0));
socket.BeginReceive(new AsyncCallback(ResponseCallback), socket);
listenSockets.Add(socket);
Log.Debug("Sending message to all SQL Browser instances from {address}", networkInterface);
using var broadcastSocket = new UdpClient { ExclusiveAddressUse = false };
broadcastSocket.Client.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReuseAddress, true);
broadcastSocket.Client.Bind(new IPEndPoint(networkInterface, ((IPEndPoint)socket.Client.LocalEndPoint).Port));
var bytes = new byte[] { 0x02, 0x00, 0x00 };
broadcastSocket.Send(bytes, bytes.Length, new IPEndPoint(IPAddress.Broadcast, SqlBrowserPort));
}
Thread.Sleep(responseTimeout);
foreach (var socket in listenSockets)
{
socket.Close();
socket.Dispose();
}
listenSockets.Clear();
}
catch (Exception ex)
{
Log.Warning(ex, "Failed to initiate SQL Server browser scan");
throw;
}
}
}
private void ResponseCallback(IAsyncResult asyncResult)
{
try
{
var socket = asyncResult.AsyncState as UdpClient;
var localEndpoint = socket.Client.LocalEndPoint as IPEndPoint;
var bytes = socket.EndReceive(asyncResult, ref localEndpoint);
socket.BeginReceive(new AsyncCallback(ResponseCallback), socket);
if (bytes.Length == 0)
{
Log.Warning("Received nothing from SQL Server browser");
return;
}
var response = System.Text.Encoding.UTF8.GetString(bytes);
Log.Debug("Found SQL Server instance(s): {data}", response);
foreach (var instance in ParseInstancesString(response))
{
serverInstances.TryAdd(instance.FullName(), instance);
}
}
catch (Exception ex) when (ex is NullReferenceException || ex is ObjectDisposedException)
{
Log.Debug("SQL Browser response received after preset timeout {timeout}", responseTimeout);
}
catch (Exception ex)
{
Log.Warning(ex, "Failed to process SQL Browser response");
}
}
/// <summary>
/// Parses the response string into <see cref="SqlServerInstance"/> objects.
/// A single server may have multiple named instances
/// </summary>
/// <param name="serverInstances">The raw string received from the Browser service</param>
/// <returns></returns>
static private IEnumerable<SqlServerInstance> ParseInstancesString(string serverInstances)
{
if (!serverInstances.EndsWith(";;")
{
Log.Debug("Instances string unexpectedly terminates");
yield break; 
}
// Remove cruft from instances string.
var firstRecord = serverInstances.IndexOf(ServerName);
serverInstances = serverInstances[firstRecord..^2];
foreach (var instance in serverInstances.Split(";;"))
{
var instanceData = instance.Split(";");
yield return new SqlServerInstance
{
ServerName = instanceData[1],
InstanceName = instanceData[3],
IsClustered = instanceData[5].Equals("Yes"),
Version = instanceData[7]
};
}
}
}

这似乎比旧的SMO EnumerateServers方法更快，可能是因为它没有测试每个实例的连接，这是我喜欢的。我想知道外面有什么服务器，即使我必须先修复一些东西才能真正连接。

此函数从随机选择的UDP端口发送，因此请注意需要防火墙规则来允许可执行文件的流量，但您可以限制为远程端口1434。

Answer 2

接受的答案是不完整的(错过了一个类(，并使用了一些无法解释的变量，如"ServerName"(可以肯定的是，本地服务器名称？什么是"删除cruft"？(

无论如何，我发现这个版本更好：https://stackoverflow.com/a/64980148/56621

(也是一个在NET Core上工作的UDP扫描仪(