Enumerating SQL Server Instances

Background

Starting from version 2.0, .NET supports a mechanism to enumerate the SQL Server instances in the local network. This is done by System.Sql.SqlDataSourceEnumerator class that resides on the assembly System.Data.

This class is a implements the singleton pattern, means that you can not instantiate it, and only a single instances serves all, accessed via the static property Instance.

Using the GetDataSources method of SqlDataSourceEnumerator, you get a DataTable object that contains four columns, ServerName, InstanceName, IsCulstered, and Version (Clear names, right?).

Implementation

The following code retrieves information about all visible SQL Server instances in the local network:

static void Main()
{
DataTable table = SqlDataSourceEnumerator.Instance.GetDataSources();
DisplayTable(table);
}

private static void DisplayTable(DataTable table)
{
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
Console.WriteLine(new string('-', 30));
}
}

If you have a firewall installed on your machine, you will be asked to give permissions to the application.

GetDataSources() demands the FullTrust permission set.

Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call.