Lesson 2: Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services

3 4

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.


After this lesson, you will be able to

  • Understand the default configuration for each SQL Server 2000 service
  • Use several different tools to start, stop, pause, and modify SQL Server services
  • Change the service account after setup

Estimated lesson time: 45 minutes


What Is the Default Configuration for Each SQL Server Service?

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.

Note


There is one additional service called the MSSQLServerAdHelper service. This service is used to communicate with Active Directory directory services and is covered in Chapter 12.

Starting, Stopping, and Pausing SQL Server 2000 Services

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.

Note


The terms SQL Server service and SQL Server Agent service are used to refer generically to these services, regardless of whether the services of the default instance or a named instance are being referred to. The names of these services for the default instance are MSSQLServer and SQLServerAgent. The names of these services for each named instance are MSSQL$InstanceName and SQLAgent$InstanceName.

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.

 figure 3.6 - the sql server service manager dialog box.

Figure 3.6

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.

 figure 3.7 - the sql server service manager options dialog box.

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.

Note


The SQL Server Service Manager taskbar icon changes slightly to reflect the SQL Server service being displayed. Also, if you look closely, you can see a red pulse flash on and off at the lower right of its icon corresponding to how often the service is being polled.

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.

 figure 3.8 - starting a stopped sql server instance when connecting.

Figure 3.8

Starting a stopped SQL Server instance when connecting.

 figure 3.9 - options on the context menu for starting, stopping, or pausing sql server.

Figure 3.9

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.

Note


Any of the preceding tools can also be used to control the state of SQL Server services on remote computers that you have permission to administer.

Practice: Starting SQL Server Services

In this practice you start several SQL Server services using different tools.

To start SQL Server services

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Service Manager.

    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.

    Note


    The service will not be stopped if you have rebooted your computer since you installed SQL Server 2000.

  3. Click the Start/Continue button (green triangle) to start the SQL Server service.

    The status line indicates that the SQL Server service on your computer is starting, and then indicates that it is running.

  4. In the Services drop-down list, change the displayed service to SQL Server Agent.

    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.

  5. Select the Auto-Start Service When OS Starts check box.
  6. Click the Start/Continue button to start the SQL Server Agent service.

    The status line indicates that the SQL Server Agent service on your computer is starting, and then indicates that it is running.

  7. In the Server drop-down combo box, change the server to display your named instance.

    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.

  8. Select the Auto-Start Service When OS Starts check box, but do not start the SQL Agent service.
  9. Close the SQL Server Service Manager dialog box.

    Notice that the SQL Server Service Manager icon remains on the taskbar.

  10. Click Start, point to Programs, point to Accessories, and then click Command Prompt.

    The Command Prompt window appears.

  11. Type net start and then press Enter.

    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.

  12. Type net start sqlagent$mynamedinstance and then press Enter.

    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.

  13. Close the Command Prompt window.
  14. On the taskbar, right-click the SQL Server Service Manager icon, and then click Options.

    The SQL Server Service Manager Options dialog box appears.

  15. Clear the Verify Service Control Action check box, and then click OK.

Changing the SQL Server or SQL Server Agent Service Account After Setup

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.

Note


When you change the service account, the permissions in the NTFS file system and the Windows registry held by a domain user account previously used are not removed. You should either remove them manually, or disable (or delete) the domain user account previously used if no other service is using this domain user account.

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.

 figure 3.10 - supplying the name, password, and domain of an administrator account.

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.

Lesson Summary

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.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net