Enumerating Instances of SQL Server (ADO.NET)

SQL Server permits applications to find SQL Server instances within the current network. The SqlDataSourceEnumerator class returns the visible servers in the DataTable object. The table lists the server instances available on the network, like the server list in the Connection Properties dialog box. The results aren't always complete.

With Microsoft.Data.SqlClient, GetDataSources requires Windows with native networking. On Linux, macOS, or Windows with managed networking enabled, the method throws NotImplementedException. In those environments, specify the server address and port directly in your connection string instead of server discovery.

Retrieving an Enumerator Instance

To retrieve information about available SQL Server instances, first retrieve an enumerator with the shared/static Instance property:

Dim instance As Microsoft.Data.Sql.SqlDataSourceEnumerator = _
   Microsoft.Data.Sql.SqlDataSourceEnumerator.Instance

Microsoft.Data.Sql.SqlDataSourceEnumerator instance =
   Microsoft.Data.Sql.SqlDataSourceEnumerator.Instance;

After you retrieve the static instance, call GetDataSources to retrieve information about available servers in the DataTable object:

Dim dataTable As System.Data.DataTable = instance.GetDataSources()

System.Data.DataTable dataTable = instance.GetDataSources();

The table returned from the method call contains the following columns, all of which contain string values:

Enumeration Limitations

All of the available servers may or may not be listed. The list can vary depending on factors such as timeouts and network traffic. This can cause the list to be different on two consecutive calls. Only servers on the same network will be listed. Broadcast packets typically won't traverse routers, which is why you may not see a server listed, but it will be stable across calls.

Listed servers may or may not have additional information such as IsClustered and version. This is dependent on how the list was obtained. Servers listed through the SQL Server browser service will have more details than those found through the Windows infrastructure, which will list only the name.

SQL Server provides information for the SqlDataSourceEnumerator through the external Windows service SQL Browser. This service is enabled by default, but administrators might turn it off or disable it, making the server instance invisible to this class.

Example

The following console application retrieves information about visible SQL Server instances and displays the information in the console window. Install the Microsoft.Data.SqlClient NuGet package.

Imports Microsoft.Data.Sql
Imports System.Data

Module Module1
  Sub Main()
    ' Retrieve the enumerator instance and then the data.
    Dim instance As SqlDataSourceEnumerator = _
     SqlDataSourceEnumerator.Instance
    Dim table As System.Data.DataTable = instance.GetDataSources()

    ' Display the contents of the table.
    DisplayData(table)

    Console.WriteLine("Press any key to continue.")
    Console.ReadKey()
  End Sub

  Private Sub DisplayData(ByVal table As DataTable)
    For Each row As DataRow In table.Rows
      For Each col As DataColumn In table.Columns
        Console.WriteLine("{0} = {1}", col.ColumnName, row(col))
      Next
      Console.WriteLine("============================")
    Next
  End Sub
End Module

using Microsoft.Data.Sql;

class Program
{
  static void Main()
  {
    // Retrieve the enumerator instance and then the data.
    SqlDataSourceEnumerator instance =
      SqlDataSourceEnumerator.Instance;
    System.Data.DataTable table = instance.GetDataSources();

    // Display the contents of the table.
    DisplayData(table);

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
  }

  private static void DisplayData(System.Data.DataTable table)
  {
    foreach (System.Data.DataRow row in table.Rows)
    {
      foreach (System.Data.DataColumn col in table.Columns)
      {
        Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
      }
      Console.WriteLine("============================");
    }
  }
}

See also

• SQL Server and ADO.NET
• ADO.NET Overview