Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

The process of connecting your ADO objects to SQL Server can seem like making a long-distance call to a remote island near Fijiunless you happen to live in Fiji, when it's more like getting a call placed to Kabul, Afghanistan by way of Lake of the Ozarks. The connection process is impeded all along the way by security checks and unreliable "carriers"the network hardware, protocols, handshakes, and interfaces. Even if you gain access to the server hosting SQL Server, you face another set of security checks and pat-downs once you try to check into the database. Sometimes, it seems like SQL Server is trying to look for toothpaste in your luggage. Fortunately, when connecting to SQL Server, you won't have to put up with long lines, cramped seats, terrible food, and surly flight-attendants just off of their latest strikeif you're lucky.

A successful connection assumes that the SQL Server process is actually running. In a typical shared SQL Server installation, this might (just might) be a given, but in a SQL Express installation, it's likely that SQL Server will not be startedthat will be up to you and your code. This call also assumes the network is up and stays up for the duration of the connection. Anyone with a modicum of experience knows that lots of stuff can happen to the NIC card and the cables, hubs, routers, switches, ISA servers, firewalls, and modems that carry you to the destination and the equivalent hardware infrastructure on the server end of the wire. Microsoft provides a few new helper classes in the 2.0 Framework to discover whether the server and network are still viable. However, these classes do not automatically code the exception handlers in your application to deal with the stuff that happens. I think Microsoft could have done more...but there always seems to be a next version on the horizon. For example, the SqlConnection object has a new State.Broken enumeration on its State property. Sadly, this is not used (for anything), and when the connection to the server is broken, the StateChange event does not fire (except if your server is moved to Ohio). Wouldn't it be nice if this event fired when the server went down or a stored procedure closed the connection from the server end? The problem is, the data layers don't know that the server has failed without (expensive) polling. This means there is no way of knowing (with today's technology) that a "circuit" is dead without trying to use it. I don't expect this to be fixed until some fundamental changes are made to the lowest-level network infrastructure. Don't hold your breath.

Finding Servers and Instances in your Domain

If you're writing a tool (like Visual Studio's Server Explorer), you'll need help finding SQL Server instances or data sources exposed by other providers. You might also want to determine whether the targeted system has SQL Server installed or running. The 2.0 Framework includes several new classes that can make this easy (or, at least, easier). Once you find the SQL Server instance, you'll also want to know whether it's running, and if it's not, you'll want to start it. Let's walk through the new classes to help in each of these tasks. Too bad there aren't equivalent methods to get through airport security.

The example shown in Figure 9.9^[8] retrieves a list of SQL Server instances on the domain. It uses the DbProviderFactories.GetFactory method to find all servers that are visible to System.Data.SqlClient. This returns both SQL Server 2005 (Version 9), SQL Server 2000 (Version 8), and (theoretically) SQL Server 7.0 servers and installed instances. Behind the scenes, these classes make two "calls" out to the networka SQL Serverspecific mechanism (SSRP) and NetServEnum. The former returns the version number and clustering info; the latter (if it works at all) returns just the server (machine) name. Don't be impatientthese calls are notoriously slow.

^[8] This application can be found on the DVD under "ListSQLServerInstances". The example also includes code to pause, continue, and stop selected SQL Server instances.

I added code to test the status of these instances so you can see at a glance which are running, paused, or stopped. Note that the code uses "MSSQL" as the service name root (if there is an instance name, it's concatenated with "$<instance name>".

Once you've found the right SQL Server service and instance (what I call the ServiceName in the example in Figure 9.9), you can ask the System.ServiceProcess.ServiceController WFC class to start, pause, continue, or stop the service. Ah, you need to be careful here. Arbitrarily stopping a service is not a particularly good way to stop SQL Server. While it may be safe to pause, continue, or start the server using the technique I'm about to show you, unless you know SQL Server is in a stable, idle state, it's never a good idea just to stop its service. While the server knows how to protect itself and shut down gracefully, it does not notify other users that the service is going down and that they need to commit their work. Using the T-SQL command "SHUTDOWN," as shown in Figure 9.10, does about the same thing. Sure, you can execute this from ADO.NET using a SqlCommand object as well. No, it won't be possible to use SQLCMD^[9] to restart the server, as you won't be able to connect to it.

^[9] Remember, SQLCMD is simply the SQL Server 2005 version of OSQL that replaced ISQL.

The following example (shown in Figure 9.11) shows how to start a specific SQL Server ServiceName. As discussed earlier, the service name is made up of the Microsoft SQL Server service name root "MSSQL" concatenated with a "$" sign and the instance namefor example, "MSSQL$SS2K5".

To see which services are running (and provide yet another way to start, stop, pause, or configure any Windows service, you can use the Services.msc snap-in application, as shown in Figure 9.12. In this case, I drilled into the SQL Server (SS2K5) instance properties page. Note the "service name" is shown at the top of the dialog. This is the name you pass to the WFC class used to alter or view its running state.