Running SQL Server Services

Unlike JET, SQL Server Everywhere, or other DLL-based DBMS engines, SQL Server runs as a Windows service. Reporting Services and several other SQL Server extensions also run as additional, separate Windows services. This means you'll have to ensure that the specific SQL Server instance service you wish to access is running before trying to open a connection. This section discusses the ways you can inspect and configure the service(s) installed by SQL Server setup and set them to run whenever Windows starts or on demand. Depending on how the intended "server" system that hosts these SQL Server services is used, you might find it necessary to start and stop SQL Server programmatically instead of letting it sit there idle chewing up resources like a visiting in-law lounging on your sofa watching your TV and eating your last bag of Fritos.

Inspecting SQL Server Services

There are a variety of tools included with SQL Server that permit you to determine the visibility and state of any of the SQL Serverrelated services. Figure 2.10 illustrates the SQL Server Configuration Manager utility that exposes (just) the SQL Server 2005related services. In this case, the utility has found several instances of the SQL Server service, as well as other support services like SQL Server Reporting Services, FullText Search, Integration Service, and Analysis Services, as well as the SQL Server Browser and SQL Server Agent services. Ah, no. This installation is clearly not just a SQL Server Express edition rig.

Figure 2.10. Inspecting the installed SQL Server 2005related services.

Notice that only the SQL Server Browser and FullText Search Services are started (indicated by the green arrow); the SQL Server Browser service is stopped. This tells me that I won't be able to open any connections to any instance of SQL Server on this systemnot until I start one of the instances. I often leave my demo systems configured like thisenabling just the features that I needhopefully, before you show up to the session.

The SQL Server Configuration Manager can also be used to manually start, stop, pause, or resume the service, as shown in Figure 2.9, or set the Start Mode of each service by right-clicking on the service and choosing the appropriate running state or Properties | Service to set the Start mode, as shown in Figure 2.11.

Figure 2.11. Setting the Start Mode for a specific SQL Server service.

IMHO

The SQL Server Configuration Manager can be as contrary as my grandkids at bedtime.

I have not always had consistent results with this application. It seems to get confused sometimes and shows the service state incorrectly. If this occurs, stop and restart the tool or use the Services.msc MMC console application to manage your SQL Server services. Frankly, since the MMC console application also exposes SQL Server 2000 instances, it might be a better choice anyway, if you know how to use it.

Starting a SQL Server Service

One thing the setup routines don't include (which I think they should) is the ability to determine whether or not a SQL Server instance should be automatically started during system startupby default, SQL Server Express instances are not started. In contrast, other SQL Server versions are configured to start automatically when Windows starts. As I said, if you try to connect to any server instance that's not started, your application (or tool) will receive an exception message that says the server cannot be found. If you want to avoid this problem, you're going to have to make a decision:

• Set up the SQL Server instance to start each time the system starts. In this case, your client system has to bear the memory and CPU load of SQL Server at all timeswhether or not your application is running. This might not matter at all if the system is dedicated to this application. This option is required if you plan to use SSEE as a server.
  
  
• Add code to your application that starts the SQL Server instance of your choosing before trying to connect to it. I think this is the more viable approachespecially if you know that yours is the only application using the instancebut not if the SQL Server instance is going to act as a central server for other users. Given that the SQLExpress instance is designed to serve as a "common" instance, it might be dangerous to arbitrarily shut down the instancebut it should not hurt to start it. I talk about how to start an instance of SQL Server programmatically in Chapter 9.
  
  

If you choose either option, use the SQL Server Configuration Manager to set the startup behavior (Start Mode) to "Automatic" or "Manual", as shown in Figure 2.11.

Using SQL Express as a Server

Sure, it's possible to configure a SQL Server Express instance (or any instance of SQL Server, for that matter) as a shared server that anyone on the local area network (or WAN, for that matter) can see and access. While I'm not a fan of exposing SQL Server on the Internet, it can be donebut at your own peril. Even SQL Express is capable of supporting hundreds of users in a LAN configuration if the demand per user is not that high. If you choose this route, you'll need to be sure to set up the ports and protocols to permit the server to see the LAN and to enable the SQL Browser service as described earlier. You'll also have to make sure that the system hosting the "server" instance is availablethat is, it's not locked away in someone's office where it can't be started or reset if something goes wrong. Of course, you'll also need to ensure that the instance Start Mode is set to "Automatic", as shown in Figure 2.10.

Remember that SQL Server is optimized through judicious use of RAM and CPU resources. If the system hosting SQL Server is also required to handle other duties, such as acting as a print, email, or IIS server, it won't be able to handle nearly as many users.

Any version of SQL Server can also be accessed by IIS and ASP applications.

Connecting (Gaining Access) to SQL Server Service Instances

Microsoft and I suggest that you learn to break any dependencies on existing COM and the OLE DB providers in favor of using the new "native" SQL Native Client (SNAC) provider when accessing SQL Server from Visual Basic 6.0 or other COM-based applications. This new OLE DB provider does not require installation of the MDAC stack and exposes all of SQL Server 2005's new datatypes and many other features, like (gulp) MARS. Sure, when using ADO.NET, the SqlClient.NET Data Provider is still your best choice when accessing SQL Server. Yes, if you must connect to SQL Server Express from COM-based applications using MDAC and OLE DB, you can do so by using the old SqlOleDB or ODBC providers, as well as the new SNAC provider. Be aware that there are some subtle differences between how the old SQLOLEDB and SNAC providers workespecially when it comes to handling parameters.

ADO.NET also supports a way to access and automatically attach a selected database .MDF file. When working with a JET database, all you needed to do as you deployed your application was to copy the database .MDB file to the target client system. SSEE now supports a similar technique by exposing a connection string option (AttachDBFilename). This lets you point to an .MDF database file that's attached to the master on first open. This makes it very easy to deploy an SSEE database. I'll discuss this option when we start talking about opening connections using ADO.NET in Chapter 9.