Enumerating SQL Servers

  • Article

Continuing with the discussion on enumeration, I will go over ways to enumerate active SQL servers on the network in this blog. Pre-Whidbey, to get list of active servers on the network, we could have done Inter-Op calls to server enumeration APIs in OleDb. If we wanted to write only managed code,we could have broad-casted a carefully crafted UDP packet to the network on port 1434 and waited for response from active SQL Servers and then list them.

In Whidbey, there is an API provided to do the server enumeration. It allows to list active servers on the network. Here is the sample code:

using System.Data;

using System.Data.Sql;

using System;

public class Repro

{

public static int Main(string[] args)

{

SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;

DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();

foreach (DataRow row in datatable1.Rows)

{

Console.WriteLine("****************************************");

Console.WriteLine("Server Name:"+row["ServerName"]);

Console.WriteLine("Instance Name:"+row["InstanceName"]);

Console.WriteLine("Is Clustered:"+row["IsClustered"]);

Console.WriteLine("Version:"+row["Version"]);

Console.WriteLine("****************************************");

}

return 1;

}

}

Output:
****************************************
Server Name:TESTSERVER-D1
Instance Name:
Is Clustered:No
Version:8.00.194
****************************************
****************************************
Server Name:TESTSERVER01
Instance Name:INST2
Is Clustered:No
Version:8.00.194
****************************************
****************************************
Server Name:TESTSERVER3
Instance Name:
Is Clustered:No
Version:8.00.194
****************************************

What did we do here? SqlDataSourceEnumerator has a public static property that returns an instance of SqlDataSourceEnumerator . The GetDataSources() method on the Enumerator returns a datatable that returns list of active Servers on the network. Following information about the server is included in the table:
1.Server Name: Name of the server.
2.Instance Name: Name of the SQL Server instance. If its a default instance then this is DBNull.
3.Is Clustered: returns Yes, if Server is clustered, else No.
4.Version: returns the full version number of the server listed.

Q:Why do we have a static property Instance on SqlDataSourceEnumerator ?
A:The Instance property is a result of the factorization of the class. The base class, DbDataSourceEnumerator, defines the abstract GetDataSources method. This way, all derived enumerators have to implement it. This implies the method has to be instance method and cannot be static. To make it easy to write code we just added a static property on SqlDataSourceEnumerator that gives its instance.

Q:Why are some values for InstanceName, IsClustered and Version DBNull?
A:The Enumeration API that SQL Client Managed Provider calls, first broadcasts the 'special' packet on port 1434 and waits for a timeout. The replies that it receives with-in the timeout contain all the required information to populate the DataTable. In addition to this,the API looks at the Active Directory (AD) to list the other servers that did not reply for the broadcast packet. There is no way to know the values for InstanceName, IsClustered and Version when listed via AD and hence they are DBNull.

Q:Why are all servers on the network not listed in the list?
A:Since we wait for a time-out it is not guaranteed that we return all the active servers on the network. In fact, the list is most likely to give a different list depending on the network I/O, server performance and other time-depending constraints.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments