- Before You Begin
- Lesson 1: Creating and Configuring Connection Objects
- Lesson 2: Connecting to Data Using Connection Objects
- Lesson 3: Working with Connection Pools
- Lesson 4: Handling Connection Errors
- Lesson 5: Enumerating the Available SQL Servers on a Network
- Lesson 6: Securing Sensitive Connection String Data
- Chapter Review
- Suggested Practices
- Take a Practice Test
Lesson 5: Enumerating the Available SQL Servers on a Network
This lesson describes how to return a list of visible SQL Server instances on a network comparable to the Server Name drop-down list in the Add Connection dialog box.
The .NET Framework offers applications a way to discover SQL Server instances on a network so your programs can process this information when necessary. To retrieve the list of available SQL Servers, use the Instance property of the SqlDataSourceEnumerator class and call the GetDataSources method. The GetDataSources method returns a DataTable that contains information for each SQL server that is visible on the network. The returned data table contains the columns listed in Table 5-11.
Table 5-11 DataTable Schema Returned by the GetDataSources Method
Name of the SQL server containing the visible instance
Name of the server instance, or empty for servers running default instances
Indicates whether the server is part of a cluster
The version number of the SQL server
Why Do Only Some or No SQL Servers Appear in My Grid?
Depending on how your network or even your individual machine is set up, the list of available servers might or might not be complete. In addition to things such as network traffic and timeout issues, the way your network implements security can cause servers to be hidden from the returned list as well. If you are running SQL Server 2005, a service named SQL Browser needs to be running for you to see SQL Server instances. And even if your SQL Browser service is running, your firewall might be blocking the request for SQL information. The firewall is likely to be blocking communication requests through port 1433, which is the default port that SQL Server default instances are set up to use. There are obvious security implications concerning turning on the SQL Browser service as well as enabling communications through specific ports through your firewall, but these are beyond the scope of this book. A good resource is the “SQL Browser Service” section of SQL Server 2005 Books Online, and I encourage you to read that before changing any settings on your firewall or SQL Server configuration.
Lab: Returning the List of Visible SQL Servers
In this lab you will practice enumerating the SQL Servers on your network.
EXERCISE 1: Enumerating the SQL Servers on a Network
To demonstrate how to retrieve the list of visible SQL servers, let’s create a small application to display the information returned from the GetDataSources method in a DataGridView.
Create a new Windows application named SqlServerEnumerator.
Add a DataGridView to the form and name it VisibleSqlServers.
Add a Button control below the grid and set its Name property to GetDataSourcesButton.
Set the Button’s Text property to Get Visible Servers.
Double-click the Get Visible Servers button to create the Click handler and switch to code view.
Add code so that the handler looks like the following:
' VB Dim instance As System.Data.Sql.SqlDataSourceEnumerator = _ System.Data.Sql.SqlDataSourceEnumerator.Instance VisibleSqlServers.DataSource = instance.GetDataSources // C# System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance; VisibleSqlServers.DataSource = instance.GetDataSources();
Now run the application and click the Get Visible Servers button. All visible SQL servers on your network appear in the grid, looking similar to Figure 5-3.
Figure 5-3 Grid showing all visible SQL servers on your network
You can use the SqlDataSourceEnumerator object to return a list of visible SQL servers on a network.
The list of servers returned might not be complete, due to factors such as firewall settings and protocol configurations on the SQL Server services.
The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.
What object is used to return the list of visible SQL Servers?
What factors can cause SQL servers to be invisible on the network? (Choose all that apply.)
The computer’s firewall settings
The amount of network traffic
The availability of the SQL Browser service
The Visibility property of the SQL Server
Which of the following pieces of information is available through the SqlServerEnumerator object? (Choose all that apply.)
The name of the SQL server
The number of databases currently on the server
The version number of the server
The instance name for servers that are not running default instances