SQL Server Services

3 4

A service is a program or process that performs a specific function to support other programs. When you start SQL Server, the SQL Server service starts up on Windows NT or Windows 2000. This service manages database files, processes Transact-SQL (T-SQL) statements, allocates resources among concurrent user connections, ensures data consistency, and much more. If you install one or more instances of SQL Server, the service name for each instance of SQL Server is MSSQL$InstanceName, where $InstanceName is the instance name you designated at installation time. The corresponding SQL Server Agent service for an instance is called SQLAGENT$InstanceName. For multiple instances of SQL Server, however, there will be only one installation each of Microsoft Distributed Transaction Coordinator and Microsoft Search.

The three component services are provided with your licensed copy of SQL Server. SQL Server Agent is installed by default when you install SQL Server. If you don't have Microsoft Distributed Transaction Coordinator and Microsoft Search installed, you can run the SQL Server installation program again to install those components, which are called DTC Client Support and Full-Text Search, respectively. Now let's see what support each of these services provides.

SQL Server Agent supports scheduling and executing jobs, alerts, notifications, and database maintenance plans. Without this service, you will find your job as administrator much more difficult, if not impossible. SQL Server Agent allows you to automate routine database maintenance tasks. For example, you can create a job to automatically perform a database backup every night at 1 A.M. and another job to perform transaction log backups every 30 minutes. To keep checks on your system performance, you can create a performance condition alert to inform you if the server CPU utilization goes above 90 percent. SQL Server Agent must be running to perform these kinds of tasks. This service can be configured to start up automatically when SQL Server starts, or it can be started manually. You should configure it to start automatically to ensure that your scheduled jobs, alerts, and notifications will be able to execute. See Chapter 30 to learn how to create a database maintenance plan and Chapter 31 to learn how to set up jobs, alerts, and notifications using SQL Server Agent.

Microsoft Distributed Transaction Coordinator is a transaction manager that provides the capability of including different sources of data, including data from remote databases, in your client application transactions. This means that a single transaction can update data on multiple remote servers. This transaction manager ensures that all updates will be made permanent on all data sources if the transaction is committed or, in case of an error, ensures that the modifications will be rolled back on all data sources. See Chapter 25 for more details about using Microsoft Distributed Transaction Coordinator.

The Microsoft Search service starts up when you start Microsoft Search, which supports full-text indexing and searching. Full-text indexing allows more complex searches to be performed on character string data. For example, you can search for words that are in close proximity to a given word, or you can search for strings that contain a particular phrase.

As stated earlier in this chapter, there are several tools you can use to stop and start the SQL Server services: SQL Server Service Manager, SQL Server Enterprise Manager, and Windows 2000 Service Control Manager. First let's look at SQL Server Service Manager, which can be used to control the services for SQL Server, SQL Server Agent, Microsoft Distributed Transaction Coordinator, and Microsoft Search.