Dot Net Thoughts

One of the project I am worked, I had to enumerate SQL Server instances of the network for creating dynamic connection string. Here is a code snippet which will retrieve all the SQL Server instance in a network using C# and ADO.Net.

using System.Data;
using System.Data.Sql;

SqlDataSourceEnumerator sqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance;
DataTable sqlServerInstances = sqlDataSourceEnumerator.GetDataSources();

The Data Table contains 4 columns, and columns are

1. ServerName - Name of the Server.
2. InstanceName - Name of the server instance. Blank if the server is running as the default instance.
3. IsClustered - Indicates whether the server is part of a cluster.
4. Version - Version of the server (8.00.x for SQL Server 2000, 9.00.x for SQL Server 2005, and 10.0.x for SQL Server 2008).

Another way is using SQL Server Management Objects (SMO). For this you need to add reference of Microsoft.SqlServer.Smo assembly.

DataTable sqlServerInstances = SmoApplication.EnumAvailableSqlServers();

It will also return a Data Table, with 6 columns and the columns are

1. Name- Name of the Server.
2. Server - The name of the server on which the instance of SQL Server is installed.
3. Instance-The instance of SQL Server.
4. IsClustered -A Boolean value that is True if the instance is participating in failover clustering, or False if it is not.
5. Version -Version of the SQL Server (8.00.x for SQL Server 2000, 9.00.x for SQL Server 2005, and 10.0.x for SQL Server 2008).
6. IsLocal-A Boolean value that is True if the instance is local, or False if the instance is remote.