The ability to discover all local and network based SQL data source instances can be a useful feature, especially when a user has to supply SQL database information on the spot. More than a few applications sport this feature, such as Microsoft’s own SQL Management Studio, where a user can choose what server to log on to via a graphical representation of SQL servers on the network.

The graphical rendering of representations for the data sources is trivial; the core component here is the discovery mechanism itself. There exists a multitude of ways to go about achieving this sort of functionality. Which one is the most sensible way to gather a collection of SQL server instance information, is the question you should be asking.

In order to answer this question, this post covers four different approaches to solving the data source instance discovery problem.?Each approach also has some rudimentary performance analysis included, allowing us to truly decide which method takes the cake for most general scenarios.

The metrics I’m taking are what they are: wall clock time. Since the main antagonist here in terms of performance stems from network latency (usually), I believe it’s a valid point of comparison.

We will start things off with the simplest approach, and most likely the worst.

The SqlDataSourceEnumerator Class

Part of the .NET Framework since version 2.0, this class is described as one that, “Provides a mechanism for enumerating all available instances of SQL Server within the local network“. This is probably the easiest way for the novice to provide a data discovery feature to potential users of the software.?

What the class provides in terms of output is a DataTable containing relevant server information of any discoveries made. Its use is simple, and is demonstrated below.

Method #1: Using the .NET SQL Data Source Enumerator


static void Main()

{

SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;

DataTable dataTable = instance.GetDataSources();

}

The returned data table will contain the columns: ServerName, InstanceName, IsClustered, and?Version. The nature of these properties should be self-explanatory; needless to say, you are presented with all the information you immediately need to present it at a choice to the user.

Before we jump to conclusions and give this approach the Weber Stamp of Approval, let’s take a closer look at it. The first thing we should be asking is: how efficient is this mystery class? In order to answer this question (and the rest of the timing analysis questions answered in this article), I clocked this approach’s execution time using the QueryPerformanceCounter and QueryPerformanceFrequency functions.

This test utilized the code displayed above on a network with over a fair number of SQL servers.

The discovery process clocked in at 8.15830876 seconds with 6?servers returned, giving us an elapsed time per server of 1.359718 seconds/server. As I will show to you later in this writeup, this borders on being almost completely unacceptable. What is even more unacceptable is the number of servers returned itself. Only six server results were returned; in actuality, the number of SQL servers running on my network far exceeds this number.

This is extremely problematic. Faith in one’s procedures far outweighs any roughly measured metric. It seems that the SqlDataSourceEnumerator doesn’t exactly excel at what it does. In fact, Microsoft admits to this in that the SqlDataSourceEnumerator is not guaranteed to list all of the servers actually on the network.?

The reason for lack of any sort of guarantee is because of the SqlDataSourceEnumerator’s reliance on a “UDP request” which relies on a response to be picked up within a small time frame. Microsoft supposedly has plans?to ensure better reliability by adding more thorough checks, such as one with Active Directory.

Taking all of this in, my opinion is that this is a sub-par class to use and by doing so you would prove that you are not aware of what you are doing in any measurable amount. Luckily for you, however, there are some more promising alternatives which are covered further along in this writeup.

Trying to move as fast away as possible from this trash heap, we sprint towards the next possible approach; a method brought to you via the wonders of packet sniffing.

Method #2: IP Broadcast Datagrams

Discovery through the broadcast of a datagram packet was one of the first approaches I found myself using. I would not be surprised if the SqlDataSourceEnumerator class attempted to achieve discovery using something similar to what I’m about to talk about; but, however they did it, they did it wrong in terms of performance as you will see. I’m not going to spend time packet sniffing SqlDataSourceEnumerator, but I’d welcome anyone else to; I’d be interested in hearing the results.

Basically, this approach works by broadcasting a datagram packet on port 1434 with a payload of 0×02. Following the broadcast, you will get an “echo” back from any SQL server on the network with some standard SQL server information (information very similar to what we get with SqlDataSourceEnumerator). I figured out this juicy little operation by packet sniffing the communication between a Microsoft SQL server command line tool and the servers on the network. I believe the tool was “osql.exe”, but I can’t remember for sure.

The following achieves SQL data source instance discovery via datagram broadcasting:


public static void BroadcastDiscovery()

{

Socket discoveryOutlet = new Socket(

  AddressFamily.InterNetwork,
  SocketType.Dgram,
  ProtocolType.Udp);

IPEndPoint endPoint = new IPEndPoint(IPAddress.Broadcast, 1434);
ArrayList returnedServers = new ArrayList();
byte[] payload = new byte[] { 0x02 };
byte[] buffer = new byte[1024];
int activity = 0;

try
{
  discoveryOutlet.ReceiveTimeout = 300;
  discoveryOutlet.EnableBroadcast = true;
  discoveryOutlet.SendTo(payload, endPoint);

  do
  {
    activity = discoveryOutlet.Receive(buffer);
    returnedServers.Add(new SqlServerInfo(null, buffer));
  } while (activity != 0);

}

catch(SocketException sEx)

{
.
.
.
}

The SqlServerInfo class be instantiated here is a class I wrote that parses the returned bytes into something more human readable. I have no plans to post that on here, simply for time’s sake, unless there’s a demand for it.

As you should be able to clearly see, we broadcast a datagram packet with payload ’0×02′ on the network on port 1434. We get a bunch of binary data back from the SQL servers which offers the traditional set of data one needs to be able to present data source instances to the user.

So to the meat of things: how did this method fare in the performance and reliability departments?

The entire transaction took only?0.8109374236111 seconds to complete for a total of 6?servers returned, giving us an elapsed time per server of?0.1351562372685 seconds, a whole order of magnitude better than Microsoft’s provided solution.

But, as you can see, we still only got six servers back, which is just as troubling in terms of reliability. Clearly the .NET provided solution to data source discovery went about accomplishing it along the lines of what I just showed you here, although I have not confirmed this to be entirely true.

Although the performance has vastly improved, I don’t recommend it completely due to the unreliability exhibited as well as the dependency of servers needing to be actively listening on that port (perhaps with a certain discovery service turned on…).

Perhaps the above method will prove useful to someone in a specific solution. I found it to be interesting nonetheless, and something I myself used until I found something better. The final two methods I’m going to cover are ones I actively use; one I use for discovering local (as in, on the local machine) servers, the other for discovering network SQL servers.

Method #3: Windows Management Instrumentation

For local discovery, I believe that this is the best way to go about discovering the running SQL instances in an efficient and reliable manner. I’m going to assume the reader knows what WMI is, so I’m not going to lecture on it here.

The following demonstrates how to achieve SQL data source instance discovery with WMI.


public static void DiscoverLocal()

{

ManagementScope instanceScope = new ManagementScope(

  "root\\Microsoft\\SqlServer\\ComputerManagement");

  ManagementClass instanceClass = new ManagementClass(

  instanceScope, new ManagementPath("ServerNetworkProtocol"), null);

instanceClass.Get();

foreach (ManagementObject instance in instanceClass.GetInstances())

{

  instance.Get();

  SqlInstance sqlInstance =

    new SqlInstance(instance.GetPropertyValue("InstanceName").ToString());

  if (!instanceList.Contains(sqlInstance))

  {

    instanceList.Add(sqlInstance);

    _totalEntries++;

  }

 }

}

The above will create a number of SqlInstance class instances, depending on the number of servers running on the local machine, which provides us with enough information for most purposes. The execution of this approach is almost (but of course, not quite) instantaneous due to the lack of the requirement of dealing with, but I’ll provide you all with a time measurement as it moves me.

The final approach I’m going to go over here is my ideal choice for data source instance discovery method over a local network.?

Method #4: NetServerEnum

Truly the?quintessential approach to data source instance discovery, I’ve found no equal. Its performance is in the same league as the IP datagram broadcast, and is vastly better than what’s offered by the SqlDataSourceEnumerator class.

What really makes this a star player, however, is the fact that it is actually reliable?(gasp) in returning all of the SQL server instances that are actually on the network. Although I haven’t stress tested this method out in thousands of different environment, I’ve never seen it fail to report a server (if it did, then I didn’t notice!).

This approach which I am presenting as the ideal one, is achieved through the NetServerEnum?function. It originates from the depths of the Netapi32.dll library. The only requirement we must meet when using this is that the Computer Browser service needs to be running, which isn’t that much to ask for, but just FYI in case you are in an environment where that service cannot be ran.

/// <summary>

/// Lists all servers of the specified type that are visible in a domain.

/// </summary>

/// <param name="ServerName">This must always be null, or else.</param>

/// <param name="dwLevel">

/// The information level of the data requested, can be either 100 or 101.

/// 100 deals with server names and platform information, while 101 also deals with

/// types and associated software.

/// </param>

/// <param name="pBuf">

/// Pointer to the buffer that receives the data. This is dependant on the information

/// level?requested.

/// </param>

/// <param name="dwPrefMaxLen">

/// Preferred maximum length of returned data, in bytes. Using MAX_PREFERRED_LENGTH

/// will allocate?enough memory to fit all of the received data.

/// </param>

/// <param name="dwEntriesRead">

/// Pointer to a value that receives the count of elements actually enumerated.

/// </param>

/// <param name="dwTotalEntries">

/// Pointer to a value that receives the total number of visible servers and workstations?

/// on the network. As per Microsoft documentation, this value should not always

/// be trusted.

/// </param>

/// <param name="dwServerType">

/// Value that filters server entries to return from enumeration.

/// SV_TYPE_SQLSERVER, with byte value {0×04}.

/// </param>

/// <param name="domain">

/// Pointer to a constant string that specifies the name of the domain for which a list?

/// of servers is to be returned. Passing null implies the primary domain.

/// </param>

/// <param name="dwResumeHandle">This should be zero, lest bad things occur.</param>

/// <returns>

/// Returns the value of NERR_Success if successful, otherwise it returns a variety of?

/// error codes.

/// </returns>

?? ?[DllImport("Netapi32", CharSet = CharSet.Auto, SetLastError = true),

?? ?SuppressUnmanagedCodeSecurityAttribute]

?? ?public static extern int NetServerEnum(

?? ? ? ? ? ?string ServerName,

?? ? ? ? ? ?int dwLevel,

?? ? ? ? ? ?ref IntPtr pBuf,

?? ? ? ? ? ?int dwPrefMaxLen,

?? ? ? ? ? ?out int dwEntriesRead,

?? ? ? ? ? ?out int dwTotalEntries,

?? ? ? ? ? ?int dwServerType,

?? ? ? ? ? ?string domain,

?? ? ? ? ? ?out int dwResumeHandle

?? ? ? ? ? ?);

The data is returned in SERVER_INFO_100 and SERVER_INFO_101 structures, which you can implement as follows:

/// <summary>

/// Structure that contains information about the specified server, including the

/// name and platform.

/// </summary>

?? ?[StructLayout(LayoutKind.Sequential)]

?? ?public struct _SERVER_INFO_100

?? ?{

?????? ?/// <summary>

?????? ?/// Specifies the information level to use for platform-specific information.

?? ? ? /// Values?are defined?in the lmcons.h file.

?????? ?/// </summary>

?????? ?internal int sv100_platform_id;

?????? ?/// <summary>

?????? ?/// Pointer to a Unicode string specifying the name of a server.

?????? ?/// </summary>

?????? ?[MarshalAs(UnmanagedType.LPWStr)]

?????? ?internal string sv100_name;

?? ?}

?? ?/// <summary>

?? ?/// Structure that contains information about the specified server, including name,

?? ?/// platform, type of?server,?and associated software.

?? ?/// </summary>

?? ?[StructLayout(LayoutKind.Sequential)]

?? ?public struct _SERVER_INFO_101

?? ?{

?????? ?/// <summary>

?????? ?/// Specifies the information level to use for platform-specific information.

?? ? ? ?/// Values are defined?in the lmcons.h file.

?????? ?/// </summary>

?????? ?internal int sv101_platform_id;

?????? ?/// <summary>

?????? ?/// Pointer to a Unicode string specifying the name of a server.

?????? ?/// </summary>

?????? ?[MarshalAs(UnmanagedType.LPWStr)]

?????? ?internal string sv101_name;

?????? ?/// <summary>

?????? ?/// Specifies, in the least significant 4 bits of the byte, the major release

?? ? ? ?/// version number of the?operating system.

?????? ?/// The most significant 4 bits of the byte specifies the server type. The mask

?????? ?///?MAJOR_VERSION_MASK should be used?to ensure correct results.

?????? ?/// </summary>

?????? ?internal int sv101_version_major;

?????? ?/// <summary>

?????? ?/// Specifies the minor release version number of the operating system.

?????? ?/// </summary>

?????? ?internal int sv101_version_minor;

?????? ?/// <summary>

?????? ?/// Specifies the type of software the computer is running.

?????? ?/// </summary>

?????? ?internal int sv101_type;

?????? ?/// <summary>

?????? ?/// Pointer to a Unicode string specifying a comment describing the server.

?? ? ? ?/// The comment can be?null.

?????? ?/// </summary>

?????? ?[MarshalAs(UnmanagedType.LPWStr)]

?????? ?internal string sv101_comment;

You can use all of this like so:

public static void FindThosePeskyInstances()

{

?? ?IntPtr buffer, tmpBuffer;

?? ?int entriesRead, totalEntries, resHndl, szInfo100 = 0;

?? ?_szInfo100 = Marshal.SizeOf(typeof(NetAPI._SERVER_INFO_100));

?? ?try

?? ?{

?????? ?int ret = NetAPI.NetServerEnum(

?? ??? ??? ??? ??? ??? ?null,

?? ? ? ? ? ? ? ? ? ? ? ?100,

?? ? ? ? ? ? ? ? ? ? ? ?ref _buffer,

?? ? ? ? ? ? ? ? ? ? ? ?MAX_PREFERRED_LENGTH,

?? ? ? ? ? ? ? ? ? ? ? ?out _entriesRead,

?? ? ? ? ? ? ? ? ? ? ? ?out _totalEntries,

?? ? ? ? ? ? ? ? ? ? ? ?SV_TYPE_SQLSERVER,

?? ? ? ? ? ? ? ? ? ? ? ?null,

?? ? ? ? ? ? ? ? ? ? ? ?out _resHndl);

?

?? ?if (ret == 0)

?? ?{

?????? ?for (int i = 0; i < _totalEntries; i++)

?????? ?{

?????? ??? ?SqlInstance instance;

?

?? ??? ??? ?_tmpBuffer = new IntPtr((int)_buffer + (i * _szInfo100));

?????? ??? ?//Grabbing an individual struct from the buffer

?????? ??? ?instance =

?? ? ? ? ? ? ? ?new SqlInstance(_tmpBuffer, SqlInstance.NetEnumInfoType.server_info_100);

?????? ??? ?instanceList.Add(instance);

?????? ?}

?? ?}

}

A bit more work is involved in getting this to work, but it’s more than worth it.

Upon running this, all SQL servers were returned on the network this time, with a total elapsed time of only 0.26992576 seconds; the best time thus far, all while sporting the greatest reliability.

Taking all of the above in: I feel that the WMI method is the best data source instance discovery for instances running off the local machine only, and that using NetServerEnum is the ideal choice for network data source instance discovery for general scenarios due to both methods’ excellent performance and reliability.

Matt Weber

I'm the the Senior Software Architect at Emergingsoft where I lead the software development team. I am also the owner of this website. I enjoy well-designed code, independent thought, and the application of rationality in general. You can reach me at matt@badecho.com.

  One Response to “SQL Data Source Instance Discovery Method Comparison”

  1. While this is an old post I found this very relevant to a project I am working on. The SqlDataSourceEnumerator was way too slow (up to a minute to return) and in our test environment it rarely returned anything useful.

    I found that for network based instances the custom UDP broadcast was the best option. The NetServerEnum approach was just as bad as SqlDataSourceEnumerator in my testing.

    For those interested the structure of the responses to the UDP broadcast are available on the MSDN site under their Open Specification thing – http://msdn.microsoft.com/en-us/library/cc219703.aspx

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
   
© 2012-2013 Matt Weber. All Rights Reserved. Terms of Use.