During the installation of SQL Server 2000 on Windows 2000 or Windows NT 4.0, the SQL Server services were installed and configured. After setup is complete, it is important to familiarize yourself with the default configuration of these SQL Server services. A number of tools are provided by SQL Server 2000 as well as by Windows 2000 or Windows NT 4.0 for this purpose. These tools are also used to start, stop, pause, and modify these SQL Server services. You need to become proficient in the use of these tools. Finally, changing the SQL Server or SQL Server Agent service account after setup must be done properly, or you might have NTFS permissions problems or Windows registry permissions problems.
During the installation of SQL Server 2000, the Setup program gave you the option to define parameters for the SQL Server and SQL Server Agent services. Remember that each instance of SQL Server 2000 has its own version of each of these two services, each with its own parameters. The first parameter defined for each of these services is the startup type. By default, the SQL Server service is configured to start automatically with the Windows operating system, whereas the SQL Server Agent service is configured to be started manually. The second parameter defined for each of these services during setup is the service account for each service. By default, the same defined domain user account is used for each of these services. The domain user account of the administrator installing SQL Server 2000 is the default of the Setup program; however, using a dedicated domain user account is highly recommended.
The Setup program configures the parameters of the MS DTC and Microsoft Search services automatically. Remember that there is only one version of each of these services for all instances of SQL Server 2000. By default, each is configured to start automatically with the Windows operating system and to use the local system account. A domain user account is not required because neither of these services needs authentication beyond the local server.
Before you can use a SQL Server 2000 service, the service must be started. The SQL Server 2000 Setup program starts the MS DTC and Microsoft Search services if they are not already running, and leaves them running. It starts the SQL Server service during installation to configure this instance of the SQL Server service, but leaves this service stopped when installation is complete. The Setup program does not start the SQL Server Agent service during installation.
There are several different tools to start, stop, or pause SQL Server services. SQL Server Service Manager is perhaps the most commonly used tool. SQL Server Service Manager is located in the Microsoft SQL Server program group, as well as the Windows Startup group. When this application is launched, it installs itself as a taskbar application, and appears to the left of the taskbar clock. Once started, SQL Server Service Manager will always appear on the taskbar unless you right-click its icon and click Exit. The SQL Server Service Manager always displays the state of the default service when initially started. Services are polled, by default, every five seconds to verify their current state.
When you double-click the SQL Server Service Manager taskbar tray icon, you can start, stop, or pause SQL Server services on each instance of SQL Server 2000 installed on the computer. See Figure 3.6.
Notice that by setting or clearing a check box, you can also configure whether a given service starts automatically when the Windows operating system starts. When you choose to pause or stop a service, you receive a confirmation box.
The SQL Server Service Manager dialog box.
You can disable this confirmation box by right-clicking the icon, clicking Options, and then clearing the Verify Service Control Action check box. See Figure 3.7.
The SQL Server Service Manager Options dialog box.
Notice that you can also change the default service that is displayed when SQL Server Service Manager starts, as well as modify the polling interval in seconds.
There are a number of additional methods of controlling the state of SQL Server services. SQL Query Analyzer and SQL Profiler provide a check box option in the Connect To SQL Server dialog box to start a selected SQL Server instance if it is stopped. See Figure 3.8.
SQL Server Enterprise Manager allows you to start, stop, or pause a registered SQL Server 2000 instance by right-clicking on the instance and selecting the desired state. See Figure 3.9.
The Services MMC in Windows 2000 and the Services applet in Windows NT 4.0 also allow you to start, stop, or pause any SQL Server 2000 service. Finally, SQL Server Enterprise Manager, the Services MMC, and the Services applet also allow you to configure whether a particular SQL Server service starts automatically, as well as other configuration parameters.
Starting a stopped SQL Server instance when connecting.
Options on the context menu for starting, stopping, or pausing SQL Server.
You can also use the NET command to start, stop, and pause SQL Server services from a command prompt; for example, NET START MSSQLServer or NET START SQLServerAgent. You can also start an instance of SQL Server or SQL Server Agent as an application by double-clicking on it in Windows Explorer or by typing the name of the executable at a command prompt (provided it is not already running as a service). The names of these applications are: Sqlservr and Sqlagent. To stop either of these services running as an application, press Ctrl+C in the Command Prompt window.
In this practice you start several SQL Server services using different tools.
To start SQL Server services
The SQL Server Service Manager dialog box appears. In the Server drop-down combo box, SelfPacedCPU appears with SQL Server displayed in the Services drop-down list as the default service. This dialog box shows that the SQL Server service is configured to auto-start when the operating system starts, and the status line indicates that it is stopped.
The status line indicates that the SQL Server service on your computer is starting, and then indicates that it is running.
The dialog box changes to display the status of the SQL Server Agent service. Notice that this service is not configured to start automatically, and that the status line indicates this service is stopped.
The status line indicates that the SQL Server Agent service on your computer is starting, and then indicates that it is running.
The dialog box changes to display the status of the SQL Server Agent service for SelfPacedCPU. The service is stopped and is not configured to start automatically.
Notice that the SQL Server Service Manager icon remains on the taskbar.
The Command Prompt window appears.
A list of all started Windows 2000 services is displayed. Notice that MSSQLSERVER and SQLSERVERAGENT are both started. Also notice that the Distributed Transaction Coordinator and Microsoft Search services are also started.
Notice that the SQL Server Agent service for your named instance starts. The SQL Server service is also started because the SQL Server Agent service requires the SQL Server service to also be running. Finally, notice that the SQL Server Service Manager icon on the taskbar indicates that the SQL Server Agent service for this instance is started.
The SQL Server Service Manager Options dialog box appears.
If you want to change the service account used by the SQL Server or SQL Server Agent services after setup, you must use SQL Server Enterprise Manager to ensure the proper functioning of these SQL Server services and SQL Server 2000. You launch SQL Server Enterprise Manager by clicking Start, pointing to Programs, pointing to Microsoft SQL Server, and then clicking Enterprise Manager. When you use SQL Server Enterprise Manager to change the service account of the SQL Server or SQL Server Agent services, SQL Server Enterprise Manager ensures that proper permissions are set in the NTFS file system and in the Windows registry for this new service account. SQL Server Enterprise Manager also ensures that this service account is granted the following required user privileges: log on as a service, lock pages in memory (used for AWE), and enable trusted for delegation (required for impersonation). If you use the Services MMC in Windows 2000 or the Services applet in Windows NT 4.0, some of these permissions and privileges are not set.
If you are running the SQL Server service under a non-administrator account, when you attempt to change either the SQL Server or the SQL Server Agent service account (or its password), you are prompted to supply the name and password of an administrator account. This account is used to apply the required permissions and privileges to the NTFS file system and the Windows registry. See Figure 3.10.
Supplying the name, password, and domain of an administrator account.
In addition, changing the SQL Server service domain user account in SQL Server Enterprise Manager is required for proper use and administration of the Microsoft Search service by SQL Server 2000. Although this service runs in the security context of the local system account, the SQL Server service must be registered as an administrator of the Microsoft Search service for SQL Server 2000 to use and administer the Microsoft Search service. For this relationship to be maintained when a change occurs in the service account used by the SQL Server service, the change in the service account must be made using SQL Server Enterprise Manager.
The SQL Server 2000 Setup program configures the SQL Server service to start automatically when the Windows operating system starts, but configures the SQL Server Agent service to start manually. To use SQL Server 2000 after installation, you must manually start the SQL Server service (unless you restart the Windows operating system). Several tools are provided that you can use to start any of the SQL Server services. SQL Server Service Manager is the tool used most frequently. Finally, changing the service account used by the SQL Server or SQL Server Agent service should only be done using SQL Server Enterprise Manager. This ensures the proper setting of all permissions and privileges in the NTFS file system and the Windows registry.