Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator |
Starting, Stopping, and Configuring the Microsoft Search Service |
SQL Server Management Studio is the primary tool you will use to manage database servers. Other tools that are available to manage local and remote servers include SQL Server 2005 Surface Area Configuration, SQL Server Configuration Manager, Performance Monitor, and Event Viewer. You will use Configuration Manager to manage SQL Server services, networking, and client configurations. Performance Monitor is available to track SQL Server activity and performance, and Event Viewer provides a way to examine events generated by SQL Server, which can provide helpful details for troubleshooting. In this chapter, you will learn how to use SQL Server Management Studio. SQL Server 2005 Surface Area Configuration and SQL Server Configuration Manager are discussed in Chapter 3. For details on Performance Monitor and Event Viewer, see Chapter 13.
The SQL Server Database Engine has two modes of operation. It can run as a command-line application (SQLServr.exe) or as a service. Use the command-line application when you need to troubleshoot problems or modify configuration settings in single-user mode. Other than that, you will normally run SQL Server as a service.
In Chapter 3, you learned that you can use SQL Server Configuration Manager to manage the SQL Server (MSSQLSERVER) service, related services for other Database Engine instances, and other SQL Server-related services. Any of these services can be configured for automatic startup or can be prevented from starting automatically. To enable or prevent automatic startup of a service, follow these steps:
Start SQL Server Management Studio by clicking the Start button, pointing to Programs or All Programs, Microsoft SQL Server 2005 and then selecting SQL Server Management Studio. Or type sqlwb at a command prompt.
In the Connect To Server dialog box, use the Server Type drop-down list to select the type of server to which you want to connect, such as Database Engine, and then, in the Server Name box, type the fully qualified domain name or host name of the server on which SQL Server is running, such as corpsvr04.cpandl.com or CorpSvr04 (see Figure 5-1).
Note
You can only connect to registered servers. If the SQL Server you want to work with is not registered, you will need to register the server before you can work with it. See the section titled "Managing Servers" later in this chapter for details.
Use the Authentication selection menu to choose the authentication type, either Windows Authentication or SQL Server Authentication (based on the allowed authentication types when you installed the server). Provide a Windows user name or SQL Server login ID and password as necessary.
Windows Authentication. Uses your current domain account and password to establish the database connection. This authentication type works only if Windows authentication is enabled and you have appropriate privileges.
SQL Server Authentication. Allows you to specify a SQL Server login ID and password. To save the password so that you do not have to re-enter it each time you connect, select Remember Password.
Click Connect. You connect to the default instance (unless you have configured another default previously). To change the instance to which you connect, click Options, select the Connection Properties tab, and then use the Connect To Database drop-down list to select the instance to which you want to connect.
Right-click the server you want to configure in the SQL Server Management Studio Object Explorer view, and then choose SQL Server Configuration Manager. This starts SQL Server Configuration Manager with the target computer set to the computer where the server you want to configure is located.
Select the SQL Server 2005 Services node. Right-click the SQL Server service that you want to start automatically, and then select Properties. You can now:
Enable automatic startup. On the Service tab, set the Start Mode to Automatic. If the server state is Stopped, click Start on the Log On tab to start the service.
Prevent automatic startup. On the Service tab, set the Start Mode to Manual.
Click OK.
You can also use Computer Management to configure services. To configure automatic startup of a service using Computer Management, follow these steps:
Click the Start button, point to Programs or All Programs, and then select Administrative Tools | Computer Management.
By default, you are connected to the local computer. To connect to a remote computer, right-click the Computer Management node and select Connect To Another Computer. In the Select Computer dialog box, select Another Computer, and then type the name of the computer. The name can be specified as a host name, such as CorpSvr04, or a fully qualified domain name, such as corpsvr04.cpandl.com.
Expand Services And Application, and then select Services.
Right-click the SQL Server service that you want to start automatically, and then select Properties.
You can now:
Enable automatic startup. On the General tab, set the Startup Type to Automatic. If the Service Status reads Stopped, click Start.
Prevent automatic startup. On the General tab, set the Startup Type to Manual.
Click OK.
Startup parameters control how the SQL Server Database Engine starts and which options are set when it does. You can configure startup options using SQL Server Configuration Manager or Computer Management. SQL Server Configuration Manager is the recommended tool for this task because it provides the current default settings and allows you to easily make modifications.
Tip
Startup parameters can be passed to the command-line utility SQLServr.exe as well. Passing the -c option to this utility starts SQL Server without using a service. You must run SQLServr.exe from the Binn directory that corresponds to the instance of the SQL Server database engine that you want to start. For the default instance, the utility is located in MSSQL.1mssqlBinn. For named instances, the utility is located in mssql$instancenameBinn.
You can add startup parameters by completing the following steps:
In SQL Server Management Studio, right-click the server you want to configure in the SQL Server Management Studio Object Explorer view and choose choose SQL Server Configuration Manager. This starts SQL Server Configuration Manager with the target computer set to the computer where the server you want to modify is located.
Select the SQL Server 2005 Services node. Right-click the SQL Server service that you want to modify, and then select Properties.
On the Advanced tab, click in the Startup Parameters box, and then press End to go to the end of the currently entered parameters. The -d, -e and -l parameters are set by default. Be careful not to modify these or other existing parameters accidentally.
Each parameter is separated by a semicolon. Type a semicolon and then a dash followed by the letter of the parameter you are adding, such as ;-g512.
Type the value for the parameter.
Repeat step 3 through step 5 as necessary to specify additional parameters and values.
Click Apply to save the changes. The parameters are applied the next time the SQL Server instance is started. To apply the parameters right away, you must stop and then start the service by clicking Restart on the Log On tab.
You can remove startup parameters by completing the following steps:
In SQL Server Management Studio, right-click the server you want to configure in the SQL Server Management Studio Object Explorer view and choose Manage Service.
Select the SQL Server 2005 Services node. Right-click the SQL Server service that you want to modify, and then select Properties.
On the Advanced tab, click in the Startup Parameters box. Each parameter is specified with a dash, parameter letter, and parameter value. A semicolon is used to separate parameter values, as shown in the following example:
-g512;
Remove the parameter by deleting its related parameter entry.
The change is applied the next time the SQL Server instance is started. To apply the parameters right away, you must stop and then start the service by clicking Restart.
Table 5-1 shows startup parameters and how they are used. The first three parameters (-d, -e, and -l) are the defaults for SQL Server. The remaining parameters allow you to configure additional settings.
Table 5-1. Startup Parameters for SQL Server
Parameter | Description | Example |
|---|---|---|
-d<path> | Sets the full path for the master database. If omitted, the registry values are used. | -dC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf |
-e<path> | Sets the full path for the error log. If omitted, the registry values are used. | -eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG |
-l<path> | Sets the full path for the master database transaction log. If omitted, the registry values are used. | -lC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf |
-B | Sets a breakpoint on error; used with the -y option when debugging. | |
-c | Prevents SQL Server from running as a service. This makes startup faster when you are running SQL Server from the command line. | |
-f | Starts SQL Server with minimal configuration. Enables the sp_configure Allow Updates option, which is disabled by default. | |
-g number | Specifies the amount of virtual address space memory in megabytes to reserve for SQL Server. This memory is outside the memory pool and is used by the extended procedure .dlls, OLE DB providers referenced in distributed queries, and the automation object referenced in Transact-SQL (T-SQL). | -g256 |
-m | Starts SQL Server in single-user mode. Only a single user can connect, and the checkpoint process is not started. | |
-n | Tells SQL Server not to log errors in the application event log. Use with -e. | |
-p<level> | Sets the precision level for numeric and decimal data types. Default is 38 digits, but the range is 1 to 38. Maximum precision (38) is assumed if you use the switch without setting a level. | -p38 |
-s instance | Starts the named instance of SQL Server. You must be in the relevant Binn directory for the instance. | -sdevapps |
-T<tnum> | Sets a trace flag. Trace flags set nonstandard behavior and are often used in debugging or diagnosing performance issues. | -T237 |
-t<tnum> | Sets an internal trace flag for SQL Server. Used only by SQL Server support engineers. | -t8837 |
-x | Disables statistics tracking for CPU time and cache-hit ratio. Allows maximum performance. | |
-ynumber | Sets an error number that causes SQL Server to dump the stack. | -y1803 |
You can start, stop, and pause SQL Server as you would any other service. On a local system, you can type the necessary command at a standard command prompt. On a remote system, you can connect to the system using Telnet, and then issue the necessary command. With Windows Server 2003, you can also establish a remote Terminal Server session to the server and access the command console remotely. To manage the Default database server instance, use these commands:
NET START MSSQLSERVER. Starts SQL Server as a service.
NET STOP MSSQLSERVER. Stops SQL Server when running as a service.
NET PAUSE MSSQLSERVER. Pauses SQL Server when running as a service.
NET CONTINUE MSSQLSERVER. Resumes SQL Server when running as a service.
To manage named instances of SQL Server, use the following commands:
NET START MSSQL$instancename. Starts SQL Server as a service, where instancename is the actual name of the database server instance.
NET STOP MSSQL$instancename. Stops SQL Server when running as a service, where instancename is the actual name of the database server instance.
NET PAUSE MSSQL$instancename. Pauses SQL Server when running as a service, where instancename is the actual name of the database server instance.
NET CONTINUE MSSQL$instancename. Resumes SQL Server when running as a service, where instancename is the actual name of the database server instance.
The SQL Server command-line executable file (SQLServr.exe) provides an alternative to the SQL Server service. You must run SQLServr.exe from the Binn directory that corresponds to the instance of the SQL Server Database Engine that you want to start. For the default instance, the utility is located in MSSQL.1mssqlBinn. For named instances, the utility is located in MSSQL.1mssql$instancenameBinn.
When SQL Server is installed on a local system, start SQL Server by changing to the directory where the instance of SQL Server you want to start is located, and then type sqlservr at the command line. On a remote system, connect to the system by using Telnet, change to the appropriate directory, and then issue the startup command. In Windows Server 2003 Editions, you can also establish a remote Terminal Server session and access the command line remotely. Either way, SQL Server reads the default startup parameters from the registry and starts execution.
You can also enter startup parameters and switches that override the default settings. (The available parameters were summarized in Table 5-1.) You can still connect SQL Server Management Studio to the server (although when you do, it may incorrectly report that it is starting the SQL Server service).
To stop an instance of SQL Server started from the command line, complete the following steps: