Programmatically Listing SQL Servers

Using the .NET Framework there are a variety of ways you can search for available SQL Servers, but no single technique does the whole job. The best approach is to use a combination of methods to ensure you get the most complete list possible.

Most methods rely on a UDP broadcast to locate servers - this is good for finding unknown servers which haven't been registered but doesn't guarantee to return the definitive list. Timeouts and firewalls blocking the relevant ports can lead to servers missing from the list and, if you're not on a network then even local servers will disappear from the list, as the UDP broadcast fails completely. In addition the list of servers is not guaranteed to be the same if you run the search twice.

The main alternative is to search the registry for registered SQL Servers. Whilst this is fine for servers you know about (and works when the network is unavailable) it doesn't allow you to discover new SQL Servers.


Method 1: UDP Broadcast

Used by:

The first two actually are actually the same under the covers: EnumAvailableSqlServers is simply an abstraction of the SqlDataSourceEnumerator class.

EnumAvailableSqlServers example

Pros:
+ Includes SQL Servers which are not registered

Cons:
- Doesn't work when there's no network connection
- Subject to firewall rules (Blocked TCP 1433 and UDP 1434)
- Doesn't find SQL Servers if SQL Browser is off
- Doesn't find SQL Servers if they are hidden
- List contents not guaranteed to be repeatable


Method 2: Reading Registered Servers from the Registry

Used by:
Microsoft.SqlServer.Management.Smo.SmoApplication.SqlServerRegistrations.RegisteredServers

RegisteredServers example

Pros:
+ Works with no network connection
+ Works regardless of firewalls
+ Shows all registered servers, regardless of SQL Browser/hidden status

Cons:
- Only shows SQL Servers which have been registered
- Will be removed in a future version of SQL Server according to MSDN


Method 3: SMO WMI

Windows Management Instrumentation provides a third way to locate SQL Servers.

WMI example

Pros:
+ Works with no network connection

Cons:
- Only lists SQL Server 2005 and later
- Only finds registered servers


Putting the methods together

If you're after a list which matches that shown in SSMS (or Enterprise Manager) then looking for registered servers will probably be an acceptable solution. If, on the other hand, you want to show all available SQL Servers (as in the SSMS "Browse for Servers" dialog), then your best bet is probably to combine one of the UDP methods with a list of registered servers. This will give you a reliable list of registered servers even when the network is down, and will include unregistered servers where possible.

One of the most user-friendly ways of doing this is to initially populate your control with the registered servers (which can be done very quickly) but allow the user to initiate a search for further network servers (which can take several seconds) in case they're looking for an unregistered server. You should always allow the user to type in a server name too, as the server may be available even though it's not returned by the UDP methods described above.

 
And another thing...

Depending on your requirements, you may need to include the instance names in the list. The methods I've mentioned all return instance names of the non-default instances (e.g. MyPC\SQLEXPRESS) but they handle them in different ways - the MSDN documentation gives full details.

Published 10 February 2008 10:37 PM by jonsayce
Filed under: ,

Comments

# Bookmarking the web - w07/2008 said on 16 February, 2008 06:48 PM

PingBack from http://www.d2design.be/bookmarking/bookmarking-the-web-w072008/

# [Blog Reference]: Listing SQL instances in your environment « Troubleshootingsql's Blog said on 19 March, 2010 06:06 PM

Pingback from  [Blog Reference]: Listing SQL instances in your environment «  Troubleshootingsql's Blog

# SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance - Programmers Goodies said on 28 July, 2011 01:50 AM

Pingback from  SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance - Programmers Goodies