SQL Server Configuration Manager is found under Programs or All ProgramsMicrosoft SQL Server 2005Configuration Tools. It can also be started from the command line by typing sqlservermanager.msc at a command prompt (assuming you’ve modified your path appropriately). This tool provides quick access for managing and maintaining SQL Server services.
SQL Server Surface Area Configuration provides basic facilities for managing service startup and startup type. You can use the Services utility or SQL Server Configuration Manager to manage more advanced features. With the Services utility, you manage SQL Server services as you would any other service. With SQL Server Configuration Manager, you can manage the service login account, the startup type, and status. If applicable, you can also manage advanced features such as dump directory, error reporting, and startup parameters. The advantage of SQL Server Configuration Manager over the Services utility is that it streamlines the information available so you only see SQL Server services rather than all system services. Additionally, some advanced options, such as the dump directory, can only be configured using SQL Server Configuration Manager.
Using SQL Server Configuration Manager, you can stop, start, pause, or restart a server service by completing the following steps:
Start SQL Server Configuration Manager, and then select the SQL Server 2005 Services node.
In the right pane, you will see a list of services used by SQL Server and its configured components (see Figure 3-6). You can work with services in several ways:
Click the name of the service to select it. Use the Start, Pause, Stop, and Restart buttons on the menu bar to manage the service run state, or click the Properties button to view the service properties.
Right-click or double-click the service, and then use the shortcut menu to manage the service run state, or click Properties to view the service properties.
You can set a service’s start mode by following these steps:
Start SQL Server Configuration Manager, and then select the SQL Server 2005 Services node.
In the right pane, right-click the name of a service, and then select Properties from the shortcut menu.
On the Service tab of the Properties dialog box, use the Start Mode drop-down menu to select the desired start mode, as shown in Figure 3-7. Options include Automatic, Disabled, and Manual.
Click OK.
SQL Server and its components have specific rights and permissions from the startup service account. These permissions are used whenever the database engine or another SQL Server component performs tasks on the local system or across the network. As you learned in the section "Service Accounts for SQL Server" in Chapter 1, you can use two types of accounts: local system accounts and domain accounts. If SQL Server performs only local operations, use the local system account. Otherwise, use a properly configured domain account. That said, SQL Server does allow you to use three different built-in accounts:
Local Service Allows SQL Server to perform operations as a system service and use local resources only.
Local System Allows SQL Server to perform operations as part of the operating system (with Act As Part Of The Operating System rights) and use local resources only.
Network Service Allows SQL Server to perform operations as a network service and use both local and remote resources.
You can specify a local system account for a SQL Server service by completing the following steps:
Start SQL Server Configuration Manager, and then select the SQL Server 2005 Services node.
In the right pane, right-click a service to select it, and then select Properties.
On the Log On tab of the Properties dialog box, select Built-In Account and then use the drop-down list to choose the service to use.
If the service is running, you must restart the service to stop it and start it again using the new credentials.
Click OK.
You can specify a domain account for a SQL Server service by completing the following steps:
Start SQL Server Configuration Manager, and then select the SQL Server 2005 Services node.
In the right pane, right-click a service to select it, and then select Properties.
On the Log On tab of the Properties dialog box, choose the This Account option button, as shown in Figure 3-8. Then type the designated account name and password. If necessary, specify the domain as part of the account name, such as CPANDLsqlprimary, where CPANDL is the domain name and sqlprimary is the account name.
If the service is running, you must restart the service by clicking Restart to stop it and start it again using the new credentials.
Click OK.
You can use advanced service configuration options to configure reporting and error logging features. When you install SQL Server, you are asked whether you want to enable two types of reports:
Error reports
Feature reports (also called Customer Feedback Reporting)
When error reporting is enabled, error reports are generated and sent to Microsoft or a designated corporate error-reporting server whenever fatal errors cause a service to terminate. Error reports help determine the cause of the fatal error so that it can be corrected, and they contain details to identify what caused the error, including the version of SQL Server being used, the operating system and hardware configuration, and data from the memory or files of the process that caused the error.
Error information is also logged in a designated dump directory. The dump directory used depends on the component and its related instance. For example, the dump directory for the default SQL Server instance might be located under %ProgramFiles%Microsoft SQL ServerMSSQL.1MSSQLLOG, and the Reporting Services dump directory might be located under %ProgramFiles%Microsoft SQL ServerMSSQL.3Reporting ServicesLogFiles.
Customer Feedback Reporting generates reports about component usage that are sent to Microsoft when this feature is configured. These reports help Microsoft understand how components and features are being used.
You can manage reporting and error dumps for each service individually. To do so, complete the following steps:
Start SQL Server Configuration Manager, and then select the SQL Server 2005 Services node.
In the right pane, right-click a service to select it, and then select Properties.
Select the Advanced tab in the Properties dialog box. You can now:
Use the Dump Directory to view the current dump directory. To change the dump directory, simply enter the new directory to use. Be sure that the logon account for the selected service has appropriate read and write access to this directory.
Use the Error Reporting and Customer Feedback Reporting drop-down menus to enable or disable reporting as appropriate. Select Yes to enable reporting. Select No to disable reporting.
If you have made changes and the service is running, you must restart the service by clicking Restart on the Log On tab to stop it and start it again using the new settings.
Click OK.