Enumerating SQL Server instances

Question

I have a simple network with a single Hyper-V VM running Windows Server which hosts a single instance of SQL Server. My desktop app is build in C#/.Net 6. I picked up server enumeration from oUeG

However, the SQL Server instance is not returned - it returns an empty list.

However, if I implement a Data Link Properties dialog box using the code at S2-P the SQL Server instance IS returned.

There is a 'Note' on the SqlDataSourceEnumerator.GetDataSources page which states that the returned list may not be the same every call due to "the nature of the mechanism used" (WTF?). But there is minimal network load or load on the SQL Server, so I don't think that is the reason. The Datalinks ALWAY works and the SqlDataSourceEnumerator.GetDataSources always fails.

I've seen SmoApplication.EnumAvailableSqlServers. Is this likely to work more consistently? Or, alternatively, what is the most robust way of enumerating the SQL Servers in my C#/wpf application?

Thanks!

Pete

Answer 1

In general SMO is the preferred approach as it is designed for managing SQL Server instances. Additionally SMO is supported on .NET Core+ whereas SqlDataSourceEnumerator is only supported on .NET Framework.

Irrelevant of your approach no code is going to be reliable. The only way to know what SQL Server instances are available on a network is to broadcast to all the machines on the network. Besides swamping your network with calls just to find the 1 or 2 servers that are available it is also highly dependent upon the network and remote machines being responsive. Since pinging a machine with no SQL instance will return nothing then the calls are heavily time-dependent. If a machine is particularly business then it may not respond fast enough even if SQL is installed. On top of that you're relying on the SQL Browser service to be running if you want any reliable behavior. That service itself pings the SQL instances that are running. So if there is a delay there then the instance will be ignored. So, as the docs clarify, there is no reliable way to detect all SQL instances on a network. You may get some or you may get all, no guarantees. Whether you're using data enumeration or SMO doesn't change that.

There is no reliable way to find SQL Server instances on a network. Personally I would find such a solution questionable and think you should rethink your design to not require this. For example if you have control over the network then you could make it a rule that SQL server instances are only hosted on machines that have sql in the name. Then you can scan the network for matching machines. Alternatively you could run some custom code on any machine that runs SQL server that provides back the list of instances when it is called. This requires that SQL instances be "registered" somewhere. Or, just require the user to know where the SQL server instances are at. Dynamically finding anything on a network is going to be unreliable.

As a final caution be aware that your network topology will impact this behavior as well. Security folks tend to lock stuff down so you cannot "browser" network software. This is a great way to attack a network. Additionally proxies that may be added to the network tend to break this kind of code as well.

If you must go this route then SMO would be the preferred approach if for no other reason than it is newer and works with later frameworks.

Answer 2

to make sql enumeration more reliable:

1) be sure the sql browser service is running of the machine hosting sql server and its port 1434 is open on the firewall for UDP packets
2) be sure the network is configured to pass UDP broadcast packets between your desktop network segment and the sql server network segment (this is often disabled). have the network people enable UDP forwarding for port 1434.

Answer 3

@Michael Taylor

Thank you so much for your comprehensive answer! I do accept and understand the issues you described but what bothered me is that my code using Microsoft.Data.Sql.SqlDataSourceEnumerator NEVER returned my VM's SQL instance. (I would expect it to succeed occasionally, surely?)

However, I'm not sure if it's a result of reboots or whatever, but I'm finding today that Microsoft.Data.Sql.SqlDataSourceEnumerator is now consistently returning the SQL instance.

FWIW, I wrote the following console application to try to better understand the options:

using Microsoft.IdentityModel.Tokens;  
using Microsoft.SqlServer.Management.Smo;  
using System.Data;  
  
////////////////////////////////////////////////////////////////////////////////////////////  
//// Datalinks launcher  
Console.WriteLine("Option 1: Use Datalinks dialog:");  
ADODB.Connection conn = new ADODB.Connection();  
object oConn = (object)conn;  
  
MSDASC.DataLinks dlg = new MSDASC.DataLinks();  
dlg.PromptEdit(ref oConn);  
Console.WriteLine("Connection string: " + conn.ConnectionString);  
  
////////////////////////////////////////////////////////////////////////////////////////////  
//// Enumerate using SmoApplication.EnumAvailableSqlServers (always fires exception!)  
Console.WriteLine("\n\nOption 2: Use SmoApplication.EnumAvailableSqlServers:");  
try  
{  
    DataTable dt = SmoApplication.EnumAvailableSqlServers(false);  
}  
catch (System.Exception ex)  
{  
    Console.WriteLine(ex.InnerException.Message);  
}  
  
////////////////////////////////////////////////////////////////////////////////////////////  
/// Use the SqlDataSourceEnumerator option  
Console.WriteLine("\n\nOption 3: Use SqlDataSourceEnumerator:");  
Microsoft.Data.Sql.SqlDataSourceEnumerator instance = Microsoft.Data.Sql.SqlDataSourceEnumerator.Instance;  
System.Data.DataTable dataTable = instance.GetDataSources();  
  
foreach (System.Data.DataRow row in dataTable.Rows)  
    Console.WriteLine(row[0].ToString()!);  

I found that option 1 (Datalinks dialog) always works. Option 2 (SmoApplication.EnumAvailableSqlServers) immediately throws an exception ("Failed to retrieve data for this request."). Option 3 (which I would not have expected to work based on yesterday's work!) is working as expected - i.e. finding the SQL instance on my VM.

I would like to use SmoApplication.EnumAvailableSqlServers in preference, based on your advice, but am clearly doing something wrong. (I am a newbie to C# / ADO.Net, coming from a 20 year+ native C++ - please excuse my ignorance!)