Common Configuration Scenarios


SQL Server configuration tools enable a number of scenarios that you may encounter in your daily activities. The following sections describe a number of the common configuration scenarios and how to use the configuration tools to perform these tasks.

Identifying SQL Server Instances and Components on a Computer

Whether it is your initial configuration or part of your daily administration of SQL Server, you will encounter situations where you need to identify instances of SQL Server that are installed on a given computer. You may also need to find out the physical association of components and instances, and the state of services for each of the installed components. But before we describe how you can perform this sort of tasks, you need to understand the relationship between SQL Server instances, components and services.

As described in Chapter 2, SQL Server allows multiple instances of SQL Server to run simultaneously on the same computer. Each instance of SQL Server consists of a number of components, and each component has a separate set of services. For example, on a given computer that has two SQL Server instances named Accounting and Sales, the Accounting instance may have a Database Engine and a Reporting Services component, and the Sales instance may have a Database Engine and an Analysis Services component. End-user applications can connect to any of these components in the Accounting or Sales instances the same way they would connect to these components if they were installed on two separate computers. You should also know that not all SQL Server components can be installed in multiple instances on the same computer. For example, Integration Services is not associated with any instances of SQL Server and it can only be installed once on a computer. There are also services that are shared between all instances on a computer. For example, SQL Server Browser service, which provides instance name resolution for all instances, is not associated with a particular instance. In addition, there are auxiliary services that are not exposed to client applications but support other services such as SQL Server Active Directory Helper and SQL Server VSS Writer. These services are typically installed along with the main services and you do not need to install them explicitly.

Table 3-1 Describes SQL Server components and their corresponding services.

Table 3-1. SQL Server Components and Service Descriptions

Display Name

Service Name

Instance-based

Description

SQL Server Database Services

MSSQLSERVER

Yes

Provides storage,processing, and controlled access of relational data and rapid transactionprocessing.

SQL Server Analysis Services

MSSQLServer OLAPService

Yes

Supplies online analytical processing (OLAP) and data mining functionality for business intelligence.

SQL Server Reporting Services

ReportServer

Yes

Manages, executes, renders, schedules, and delivers reports.

SQL Server FullText Search

SQL Server Full-Text Search

Yes

Quickly creates full-text indexes on content and properties of structured and semi-structured data to allow fast linguistic searches on this data.

SQL Server Agent

SQL Server Agent

Yes

Executes jobs, monitors SQL Server, fires alerts and allows automation of administrative tasks.

SQL Server Integration Services

MsDtsServer

No

Provides support for Integration Services package storage and execution.

Notification Services

NS$instance_name

Yes

A registered instance of a programming framework for generating and sending notifications.

SQLBrowser

SQL Server Browser

No

Provides instance name resolution service to clients that are connecting to any instances of SQL Server. This service is shared across multiple instances of SQL Server.

SQL Server Active DirectoryHelper

MSSQLServerADHelper

No

Enables integration with Active Directories.

SQL Server VSS Writer

SQLWriter

No

Provides the interface to back up/restore Microsoft SQL server through the Windows VSS infrastructure.


To identify SQL Server instances and components on a computer, you can use SQL Server Configuration Manager or Surface Area Configuration tools.

To launch SQL Server Configuration Manager, point to Windows Start menu, All Programs, Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Configuration Manager. SQL Server Configuration Manager has two panes. Selecting an entry in the left pane displays members of the entry in the right pane. To view services, select SQL Server 2005 Services in the left pane and the right pane (see Figure 3-7) displays all services that are installed on the computer.

Figure 3-7. The right pane of Configuration Manager displays information about SQL Server services.


The service names in the right pane of SQL Server Configuration manager appear in the format, component name (instance name). For example, SQL Server Analysis Services (Sales) refers to the service for the Analysis Services component in the Sales instance of SQL Server. The instance name MSSQLSERVER refers to the default instance. SQL Server Reporting Services (MSSQLSERVER) refers to the service for the Reporting Services component in the default instance of SQL Server. You may have noticed that the association of instances with services is not very distinguishable in SQL Server Configuration Manager. In fact, if you have multiple instances and several components in each of the instances, the list of services and instances becomes too long and it becomes difficult to understand the relationship between instances and components. In such scenarios, you can use the Surface Area Configuration tool to discover the instances.

In addition to discovery of SQL Server instances and components on a computer, you can use the Surface Area Configuration tool to easily identify the relationship between instances and components.

To launch Surface Area Configuration from Windows Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Surface Area Configuration. To view or change SQL Server service states, and to enable or disable remote connections, select Surface Area Configuration for Services and Connections from the main panel. The Surface Area Configuration for Services and Connections dialog has two panes. The left pane (see Figure 3-8) provides a hierarchal view of the installed instances and components, and the right pane displays service information for the selected component in the left pane.

Figure 3-8. The left pane of SQL Server Surface Area Configuration provides a hierarchal view of installed instances.


The information in the left pane of Surface Area Configuration can be used to quickly answer questions like:

  • How many instances of SQL Server are installed on this computer?

  • What components are in a particular instance of SQL Server?

Let's go through an example and discover how you can use the tool to answer these questions. In Figure 3-8, the View by Instance tab in the left pane shows four instances of SQL Server, namely MSSQLSERVER (or default instance), ACCOUNTING, SALES, and FlightInstance. In addition, you see Integration Services and SQL Server Browser components that are not associated with any instances of SQL Server. Figure 3-8 also shows that the ACCOUNTING instance has four components: Database Engine, Reporting Services, SQL Server Agent, and Full-Text Search.

If you switch to View by Component (see Figure 3-9), you can view the instances by components. The information now can be used to answer questions such as

  • Which components of SQL Server are installed on this computer?

  • What instances of SQL Server contain a particular component?

Figure 3-9. The left pane of SQL Server Surface Area Configuration provides a hierarchal view of installed components.


Following the previous example, Figure 3-9 now displays the same information by component. The left pane displays that there are eight installed components on this computer. Furthermore, the Database Engine is installed in three instances, namely MSSQLSERVER (or default instance), ACCOUNTING, and SALES.

You should be aware that not all SQL Server services described in Table 3-1 are displayed in SQL Server Configuration Manager or the Surface Area Configuration tool. Auxiliary services such as SQL Server Active Directory Helper and SQL Server VSS Writer are not exposed in these tools. You do not need to configure these services, and their initial configuration during setup is sufficient to run these services. However, in emergency situations, you may want to configure or manage these services. In that case, you can view and configure these services from the Windows Service Manager. You can launch the Windows Service Manager from the Start menu by pointing to All Programs, Administrative Tools, and selecting Services.

Starting, Stopping, and Viewing State of Services

As described earlier, there is a Windows service associated with each component of SQL Server. SQL Server components use Windows services to connect and respond to requests from client applications. Stopping a service results in refusal of new connections and termination of the existing connections. There are a number of scenarios where you need to stop, pause, or start a service. For example, you may have to terminate all connections to investigate a problem or to perform a system update. Similarly, a DBA may need to pause the Database Engine service to deploy a new build of an application. There are circumstances where you need to change a setting, but the new setting will not take effect until the service is stopped and restarted. You can also change the behavior of service startup. You may need to change the startup behavior of a service so it does not automatically start when the operating system is started. You can also view the state of a service. An application may suddenly stop connecting to the server and you need to view the state of the service to isolate the problem to either service or network issues.

SQL Server Configuration Manager is the primary tool for starting, stopping, and viewing SQL Server service states. But you can also use Surface Area Configuration to perform these tasks. The right pane of SQL Configuration Manager provides a summary view for the state of all services on a given computer (see Figure 3-10). SQL Server Configuration does a better job of grouping components by instances.

Figure 3-10. The right pane of SQL Server Configuration Manager provides a summary view for the state of all services.


A service could have one of these three states: Stopped, Started, or Paused. In the right pane of SQL Server Configuration Manager, the state of the service is displayed in the State column and in a small traffic light icon next to its name. In Figure 3-10, you can see that Integration Services and Notification Services are stopped and SQL Server (MSSQLSERVER), which is the default instance of SQL Server Database Engine, is paused. The right pane also has a column named Start Mode, which describes when and how a service is started. A service can have one of the three start modes: Automatic, Manual, and Disabled. In Figure 3-10, SQL Server Reporting Services has start mode of Manual, and the Notification Services instance named NS$FlightInstance is disabled.

To start, stop, or resume a service, right-click on the service and select the appropriate action. You can select the Properties option to view additional information about the service, as shown in Figure 3-11.

Figure 3-11. SQL Server services can be started, stopped, paused, resumed, and restarted from the right pane of SQL Server Configuration Manager.


Alternatively, you can start, stop, pause, and resume a service from the Surface Area Configuration tool (see Figure 3-12). To launch Surface Area Configuration for Services and Connections, select a component in the left pane and click on one of the buttons to Start, Stop, Pause, or Resume the service.

Figure 3-12. SQL Server Surface Area Configuration tool can be used to stop, start, pause, and resume services.


Configuring SQL Server Services in Windows Service Manager

By now you may ask, can we use Windows Service Manager to configure SQL Server services? The answer is maybeit depends on the nature of your task. If you are trying just to stop, start, or change the startup behavior of a service, you can use Windows Service Manager. If your goal in stopping a service is to reduce the surface area of SQL Server, we highly recommend that you use Surface Area Configuration. Using Surface Area Configuration to stop some services, such as Reporting Services, not only stops its service but it also disables its Web services and URLs. In general, it is good practice to use SQL Server tools rather than Windows tools when you are working with SQL Server components. SQL Server configuration tools are specifically designed to configure SQL Server services and they do more than Windows Service Manager.


Configuring Service Account and Password

SQL Server services need to access system resources such as files, folders, and protected Registry keys. This requires that the service account logs on to an account to access these resources. The account to which a service logs on can be a built-in system account or a user account. To specify a service account, launch SQL Server Configuration Manager (see Figure 3-13), select the service, and choose Properties. In the service properties dialog, you can use either one of the built-in accounts or a user account.

Figure 3-13. SQL Server Configuration Manager provides the functionality to view or change a service account.


Changing a service logon account requires restarting the service. However, new in SQL Server 2005, changes to the account password do not require restarting the service.

Whether you decide to log on as one of the built-in accounts or a user account, it is highly recommended that you run SQL Server services with the lowest possible privileges. This minimizes the escalation of privileges in the event that your service is compromised.

Choosing the Right Account for a Service

SQL Server services can be configured to be logged on to through a user account or any of the three available built-in accounts: Local System, Local Service, and Network Service. Some system administrators choose to log on to the service through the Local System account, which may not be the best practice. You should think of the System Account as the same account as that on which the operating system runs. Local System is an authoritative account that has full access to all objects in the system. For example, if a service is logged on through the Local System account and the system happens to be a domain controller, that service has access to the entire domain. It may be more suitable for the service to be logged on to through the Local Service or Network Service accounts, which are similar to authenticated user accounts. Local Service or Network Service accounts have the same level of access to system resources and objects as members of the Users group. This limited access helps protect your system if the service is compromised.


Note

Do not use Windows Service Manager to change SQL Server service accounts. Properly changing SQL Server service accounts requires setting special permissions on the file system and Registry keys that can be performed only with SQL Server Configuration Manager. Always use SQL Server Configuration Manager to change service account information.


Configuring Advanced Service Settings

In addition to the status and startup mode, additional configuration properties of a service can be viewed or manipulated in the Advanced tab of the Service Properties dialog (see Figure 3-14). Although some of this information is available if you make a connection to the server and execute system queries, SQL Configuration Manager also makes this information available without a connection. This is useful for situations in which the service is not running and you need to look up a particular property quickly, especially on a remote computer.

Figure 3-14. SQL Configuration Manager enables you to view and change advanced properties of a service without a connection.


Advanced properties can be used to answer questions such as

  • What version of SQL Server is running on this instance?

  • Which service pack is applied to this instance?

  • What edition of SQL Server is installed on this instance?

  • Is this instance cluster aware? If so, in which virtual server is it installed?

  • Is this a 32-bit instance running on a 64-bit operating system?

  • What is the Instance ID of this instance?

Following is a description of the advanced properties and how you can use these properties to answer these questions.

Clustered and Virtual Server Name

As mentioned in Chapter 2, a service can be installed as a resource of a clustered server or as a regular standalone service. You can use the values of the Clustered property to quickly identify whether a service is clustered. You can look up the Virtual Server Name that hosts the instance of SQL Server. These two properties are particularly useful for identifying SQL Server clustered services on remote computers.

Customer Feedback Reporting

SQL Server 2005 provides the option for its users to send information about their product usage to Microsoft. You can use the Customer Feedback Reporting property to opt in or out of sending usage information for a service. Notice Customer Feedback Reporting performs the same functionality as the Error and Usage Reporting tool, but at a more granular level. Customer Feedback Reporting property controls a service, whereas the Error and Usage Reporting tool controls one or more components of an instance, or all components of all instances of SQL Server. The functionality of the Error and Usage Reporting tool is described earlier in this chapter.

Dump Directory and Error Reporting

When a service has a fatal error, SQL Server creates a memory dump and optionally sends it to the SQL Server team. The Microsoft SQL Server team uses the error reports to improve the functionality and quality of SQL Server products. You can use the Dump Directory property to specify the location of the memory dump, and Error Reporting to specify whether you want to send the memory dump to Microsoft. Error Reporting performs the same functionality as the Error and Usage Reporting tool but at a more granular level. The Error Reporting property controls a service, whereas the Error Reporting tool controls one or more components of an instance, or all components of all instances of SQL Server.

Instance ID

Instance ID displays the internal identifier of this instance of SQL Server assigned by the system during an installation of the instance. Instance ID is used by the system for installing instance files in a folder named as the Instance ID. Instance ID is a read-only field in the format, SQL.1, SQL.2, SQL.3,... and is different from Instance Name, which is specified by the user during installation of an instance. For example, on a computer with multiple instances of SQL Server, an instance could be named Sales with an Instance ID of MSSQL.6. This means all the files relevant to the Sales instance are installed under the folder C:\Program Files\Microsoft SQL Server\MSSQL.6. Without knowing the Instance ID, it would be difficult for the user to find the database or backup files of the Sales instance.

Language

Language is a read-only property that displays the identifier of the default language for SQL Server messages. The mapping between the language identifier and language name is listed in Table 3-2.

Table 3-2. Language ID and Language Name Correlation

Language ID

Language Name

1028

Traditional Chinese

1031

German

1033

English

1036

French

1040

Italian

1041

Japanese

1042

Korean

1043

Dutch

1046

Portuguese Brazilian

1049

Russian

1053

Swedish

2052

Simplified Chinese

3082

Spanish


Registry Root

Registry Root is a read-only property that displays the root of the Registry that holds the keys for the instance of SQL Server.

Service Pack Level

Service Pack Level is a read-only property that displays the service pack level for the instance of SQL Server. Service Pack level of 0 implies that no service packs have been applied to the instance.

Hint

Look up the value of the Service Pack Level property for situations in which you need to determine the version of SQL Server service pack on a local or remote computer, even if the service is not running.


Startup Parameters

Startup Parameters specifies the parameters that are applied when the instance of SQL Server is started. Each of the parameters is separated by a semicolon from the previous parameter. For further information about startup parameters and their supporting scenarios, see "Configuring Database Engine Startup Parameters" in this chapter.

Stock Keeping Unit Name

Stock Keeping Unit Name is a read-only property that displays the instance edition information. The edition value could be Express Edition, Workgroup Edition, Standard Edition, Enterprise Edition, or Evaluation Edition.

Version

Version is a read-only property that displays the version of SQL Server service.

Hint

You can use the value of the Version property for situations in which you need to determine the version of SQL Server service on a local or remote computer, even if the service is not running.


Configuring Database Server Network Protocols

SQL Server Database Engine and client applications communicate via network packets inside a standard communication protocol. This is done using network libraries on both server and client computers. The network packets are formatted in Tabular Data Stream (TDS) format. The network protocol could be one of the SQL Server-specific protocols, namely Shared Memory, TCP/IP, Named Pipes, or Virtual Interface Adapter (VIA). There is no need to install network libraries on the server computer; they are always installed as part of the database engine installation. However, the client network libraries need to be installed as part of the SQL Native Client installation. There is no need for the system administrator to configure the network packet or network libraries on a client or server computer. However, the communication protocols need to be configured on both the client and server computers.

To enable and configure network protocols for an instance of Database Engine server, launch SQL Server Configuration Manager on the server computer, expand SQL Server 2005 Network Configuration in the left pane and select the instance. The right pane (see Figure 3-15) displays all available SQL Server 2005 protocols on the server and each protocol's status.

Figure 3-15. The right pane of SQL Server Configuration Manager provides a list of available network protocols for a Database Engine instance.


A Database Engine instance could use one or many network protocols to communicate with client applications. To enable a protocol (see Figure 3-16), select the protocol, right-click, and choose Enable.

Figure 3-16. SQL Server Database Engine network protocols can be enabled or disabled from the right pane of SQL Server Configuration Manager.


Additionally, you can choose Properties to view or change network properties. SQL Server Configuration Manager reads and writes the status of protocols and their properties from the Windows Registry. These properties are read when the Database Engine service starts. Therefore, making any changes to the protocols while the service is running has no effect.

Changes to the state of server network protocols and their properties require a service restart for the changes to take effect.

Configuring Database Client Application Network Protocols

As described earlier, a Database Engine server can be configured to communicate with clients using one or more network protocols. A client must also be configured to communicate with the server using one of the enabled protocols on the server.

To enable and configure client network protocols for an instance of Database Engine, launch SQL Server Configuration Manager on the server computer, expand SQL Server 2005 Network Configuration in the left pane, and select the instance. The right pane (see Figure 3-17) displays all available SQL Server 2005 protocols on the client computer and each protocol's status.

Figure 3-17. SQL Server Configuration Manager provides a list of available client network protocols on a computer.


A client computer could use one or many network protocols to communicate with a database engine instance. To enable a protocol (see Figure 3-18), select the protocol, right-click, and choose Enable. Also, a server and client could be configured to use multiple network protocols. When a client tries to connect to a server, it sequentially tries each of the enabled protocols until there is a match with one of the protocols on the server. The order in which the client tries each protocol is stored in the Registry, and it can be viewed and changed in SQL Server Configuration Manager. After a connection is established, the client and server continue to communicate through the specified protocol until the connection is terminated.

Figure 3-18. SQL Server client network protocols can be enabled/disabled and reordered from the right pane of SQL Server Configuration Manager.


The Shared Memory protocol is not available to client applications on remote computers and is available only for client applications that run on the same computer as the server instance.

Note

For troubleshooting scenarios where you suspect client network and server protocols are not configured properly or client applications cannot connect to the server, use the shared memory protocol to connect locally to the server.


The SQL Server client network protocols are installed as part of the SQL Native Client library installation. If the client and server are on the same computer, the SQL Native Client library is installed automatically during the installation of the SQL Server instance. If the client is a remote computer, you must install the SQL Native Client manually by running sqlncli.msi (or sqlncli_x64.msi for Windows 64-bit). The file sqlncli.msi can be found on SQL Server 2005 DVD or media. There are two options for configuring network protocols on a remote client computer. You can either install and run SQL Configuration Manager on the remote computer, or run SQL Server Configuration manager on a remote computer and connect to the client computer. To connect SQL Server Configuration Manager to a remote computer, see the following "Configuring a Remote Computer" section.

Configuring a Remote Computer

Configuring a remote computer is a common administration task. However, connecting SQL Configuration Manager to a remote computer is not intuitive (see Figure 3-19). If you launch SQL Server Configuration Manager from the Windows Start menu, it can connect to only the local computer. However, you can launch SQL Server Configuration Manager from Computer Management or SQL Server Management Studio to connect to a remote computer.

Figure 3-19. Computer Management provides the functionality for SQL Server Configuration to connect to a remote computer.


To connect SQL Server Configuration Manager to a remote computer from Computer Management, follow these instructions:

1.

On the Start menu, point to All Programs, Administrative Tools and select Computer Management.

2.

In the right pane, select Computer Management (Local) and from the Action menu, select Connect to another computer. Alternatively, you can right-click on Computer Management (Local) and select Connect to another computer.

3.

In the Select Computer dialog (see Figure 3-20), choose Another computer, enter the name of the remote computer, and click OK.

Figure 3-20. Select Computer dialog allows Computer Management to connect a remote computer.


4.

Back in the Computer Management right pane, you can see the name of the remote computer appended to the Computer Management label. For example, if the name of the remote computer is ComputerXYZ, you will see Computer Management (ComputerXYZ).

5.

In the right pane, expand Services and Application and select SQL Server Configuration Manager to configure the remote computer.

Alternatively, you can connect SQL Server Configuration Manager to a remote computer from the Registered Servers window in SQL Server Management Studio (see Figure 3-21).

Figure 3-21. SQL Server Management Studio provides the functionality for SQL Server Configuration Manager to connect to a remote computer.


1.

On the Start menu, point to All Programs, Microsoft SQL Server 2005 and select SQL Server Management Studio.

2.

Click on Cancel if the Connect to Server dialog pops up.

3.

In Management Studio, click on the View menu and select Registered Servers.

4.

In the Registered Servers window, right-click on Database Engine, select New and then Server Registration. This will launch the New Server Registration dialog.

5.

In the New Server Registration dialog, enter the name of the remote computer in the Server name field, click on Test and then Save. If the Database Engine on the remote computer requires SQL Server authentication, you have to change Authentication to SQL Server Authentication to enter the username and password.

6.

Back in the Registered Servers window in SQL Server Management Studio, right-click on the remote computer and select SQL Server Configuration Manager to configure the remote computer.

Encrypting Connections to Database Engine

You can enable encrypted connections between clients and an instance of SQL Server Database Engine by configuring a certificate for the Database Engine. Note that the same certification authority must issue a certificate for the clients and Database Engines. To configure a certificate for an instance of Database Engine on a computer, you must first install a certificate on the computer and then configure the Database Engine instance to use the certificate and accept encrypted connections.

To install certificates on a computer follow these steps:

1.

Log on to the computer as an Administrator. Make sure the certificate file is accessible to import.

2.

On the Windows Start menu, select Run, type mmc, and click OK.

3.

On the File menu in MMC console, select Add/Remove Snap-in..., and then click Add.

4.

In Add/Remove Snap-in dialog, click Add.

5.

In Add Standalone Snap-in dialog, select Certificates and click Add.

6.

In Certificate snap-in dialog, select the Computer account option and click Next.

7.

In the Select Computer dialog, select Local Computer and then click Finish.

8.

Back on Add Standalone Snap-in dialog, click Close.

9.

In Add/Remove Snap-in dialog, click OK.

10.

Back in the mmc Console Root tree, expand Certificates, expand Personal, and right-click on Certificates. Point to All Tasks and select Import (see Figure 3-22).

Figure 3-22. You can use the Certificates snap-in in Microsoft Management Console to import a computer account certificate.


11.

In Certificate Import Wizard, click on Next.

12.

In the next page of the wizard, click Browse, select the file for your certificate, and click Next.

13.

Select the option Place all certificates in the following store and make sure Personal is the selected Certificate Store. Click Next and Finish.

Tip

You can install a certificate on a remote computer. Follow steps 1 to 6 on a client computer and in step 7 choose the option Another computer. Type the name of the remote computer in the Select Computer dialog. Alternatively, after you have added the certificates snap-in to an mmc console, you can right-click on the Certificates (Local Computer) node, select the menu option Connect to Another Computer, and enter the name of the remote computer in the Select Computer dialog.


To configure a Database Engine instance to use the certificate and accept encrypted connections, follow these steps:

1.

Launch SQL Server Configuration Manager, and expand the SQL Server 2005 Network Configuration node.

2.

Right-click on the node Protocols for MSSQLSERVER if you are configuring the default instance of Database Engine. If you are configuring a named instance of Database Engine, right-click on the node Protocols for named-instance.

3.

In the Protocols for <instance name> Properties dialog (see Figure 3-23), change ForceEncryption to Yes. Switch to the Certificate tab, and in the Certificate drop-down select the certificate from the previous section and click OK.

Figure 3-23. Protocol Properties in SQL Server Configuration Manager provides the functionality to install a certificate for SQL Server Database Engine.


4.

Restart the Database Engine service.

Configuring Database Engine Startup Parameters

As discussed earlier, each instance of SQL Server Database Engine has a service that needs to be started for the instance to service client requests. When a service starts, it reads its startup parameters from the Registry. Some of these startup parameters, such as location of master database and log files, are specified during installation of the service. Other parameters, such as behavior of the engine and enabling or disabling of features, can be specified by the system administrator after the service installation. You can use SQL Server Configuration Manager to view or change the startup parameters.

To view or change the startup parameters of a SQL Server Database Engine instance, launch SQL Server Configuration Manager, right-click on the service, and select the Properties option in the menu (see Figure 3-24). On the Properties dialog, choose the Advanced tab and select Startup parameters in the grid.

Figure 3-24. Service Properties dialog in SQL Server Configuration Manager provides the functionality to view or change the startup parameters.


Each startup parameter is specified by a dash character followed by a letter and its value. A Database Engine service has multiple startup parameters. Each parameter is separated by a semicolon. For example, in Figure 3-24 there are three startup parameters.

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\DATA\     master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\     mastlog.ldf 


The first parameter starts with -d, which specifies the location of the master database data file. The second parameter starts with -e which specifies the location the operation log file. The last parameter starts with -l, which specifies the location of the master database log file.

Starting Database Engine in Single User Mode

There are situations in which a DBA needs to change certain Database Engine configuration parameters or recover the master database, which require starting the Database Engine in single-user mode. This can be done by following the same instructions as in the previous scenario to edit the startup parameters and append ;-m to the startup parameters. For example, to start the service in Figure 3-24 in single-user mode, the startup parameters would be

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\     master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\     mastlog.ldf;-m 


Remember, any change to service startup parameters requires a service restart for the change to take effect.

Starting Database Engine with a Trace Flag

Database Engine trace flags are startup parameters that change the server behavior or enable/disable a particular feature. Starting a server with a trace flag is typically temporary and it is used for troubleshooting server problems, particularly to investigate performance issues. To start a database engine with a particular trace flag, you need to append the trace switch t followed by the trace flag number. For example, trace flag 1204 registers the resources and types of locks participating in a deadlock and the current command affected in the database engine log To start the database engine in Figure 3-24 with trace flag 1204, the startup parameters would change to

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\     master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\     mastlog.ldf;-t1204 


Enabling Database Mirroring

Database Mirroring is a powerful feature for increasing availability of a database. In the first release of SQL Server 2005, this feature is not supported and is available for evaluation purposes only. However, there is a good chance that this feature may be supported in the future service packs or releases of SQL Server 2005. To enable database mirroring for a particular instance of Database Engine, change the service startup parameter to include trace flag 1400 (see Figure 3-25). Follow the same instruction as in the previous section and append ;-t1400 to the startup parameters.

Figure 3-25. Database Mirroring can be enabled by adding trace flag 1400 to startup parameters.


After appending the trace flag 1400 to the startup parameters, you need to restart the service to enable Database Mirroring. To make Database Mirroring fully functional, you need to further configure it in SQL Server Management Studio.

Configuring Surface Area of SQL Server

As mentioned before, there are three concepts behind surface area configuration in SQL Server 2005:

  • Reducing the surface area of SQL Server by shutting down unused components (by default, some components are disabled after installation)

  • Reducing the surface area of SQL Server by disabling remote connections

  • Reducing the surface area of SQL Server by disabling higher-risk features in SQL Server Database Engine, Analysis Services, and Reporting Services.

The following sections describe each aspect of surface area configuration.

Note

Reducing surface area of your server reduces the exposed surface area for security attacks, but it is not a sufficient measure for securing your SQL Server installation. In addition to reducing the surface area of your SQL Server, you must implement the general security practices to prevent unauthorized access to your server. SQL Server Books Online has a number of security topics that you may find useful in securing your server.


Reducing Surface Area of SQL Server by Shutting Down Unused Components

You can reduce the surface are of SQL Server by stopping and disabling unused components and their corresponding services. These services include

  • SQL Server Database Engine (including SQL Server Agent and Full-text Search)

  • Analysis Services

  • Reporting Services

  • Integration Services

  • Notification Services

  • SQL Server Browser

Each SQL Server service has a Startup attribute that specifies when the service is started. A service could have one of the startup modes: Automatic, Manual, and Disabled. A service with Automatic startup is automatically started when the operating system starts. A service with Manual startup remains stopped until the system administrator manually starts the service or a dependent service starts the service. A service with Disabled startup cannot be started until its Startup attribute is changed to Manual or Automatic.

You can use the Surface Area Configuration tool to stop and disable SQL Server services. Launch Surface Area Configuration for Services and Connections, select the unused component in the left pane, and click on Stop in the right pane to stop its service (see Figure 3-26). If you want to further disable the service, you can set its Startup type to Disabled. Conversely, the tool allows starting, pausing, and resuming of a service.

Figure 3-26. You can start, stop, pause, and resume SQL Server services with the Surface Area Configuration tool.


Note

Although at a glance you may think stopping and starting a component in the Surface Area Configuration tool merely stops and starts its corresponding service, the tool actually performs more than service manipulation for some of the services. For example, stopping and starting Reporting Services also enables and disables the Reporting Services web services and URLs.


For this reason, it is recommended that you use the Surface Area Configuration tool for disabling or enabling all SQL Server 2005 components.

Alternatively, you can use SQL Server Configuration Manager to start and stop a SQL Server service and change its startup mode. You already saw how to start and stop a service earlier in this chapter. To disable a service, launch SQL Server Configuration Manager, select a service on the right pane, right-click, and choose Properties. This launches the Service Properties dialog (see Figure 3-27). In the Service Properties dialog click on the Service tab and set the Start Mode to Disabled.

Figure 3-27. Start mode of a service can be specified in SQL Server Configuration Manager.


Reducing Surface Area of SQL Server by Disabling Remote Connections

A SQL Server client application can reside either locally on the same computer as the SQL Server installation or remotely on a separate computer. One of the effective ways to reduce security attacks is to disable the capability for remote client applications to connect to SQL Server.

SQL Server 2005 provides the functionality to disable remote connections for Database Engine and Analysis Services components. For Database Engine, you do so by disabling network protocols that are used for remote connections. A remote client application uses one of the protocols TCP/IP, Named Pipes, or VIA to connect to a SQL Server Database Engine. So, by disabling TCP/IP, Named Pipes, and VIA protocols, you can disallow remote connections to your server. A local client application can use Shared Memory protocol to connect to the server. You can further disable Shared Memory protocol, but then no local client applications including SQL Server tools can connect to manage the server. In most cases you want to enable Shared Memory protocol, so tools such as SQLCMD and Management Studio can connect to the server.

To enable or disable remote connections for Database Engine and Analysis Services, launch Surface Area Configuration for Services and Connections, select the instance, the component, and Remote Connection in the left pane (see Figure 3-28). The right pane displays the current remote connection state of the selected component. To disable remote connections, select Local connections only. To enable remote connections, select Local and remote connections and choose one or both of the TCP/IP and Named Pipes protocols. It is good security practice to only enable the protocol that is needed by your application but not both protocols. Enabling or disabling remote connections for a component requires restarting the service associated with the component.

Figure 3-28. Use the Surface Area Configuration tool to enable remote connections to Database Engine and Analysis Services.


Note

By default, remote connections are disabled in certain editions of SQL Server 2005 such as Express, Evaluation, and Developer editions, and are enabled in all other editions. After installing SQL Server, you can use the Surface Area Configuration tool to enable or disable remote connections.


Reducing Surface Area of SQL Server by Disabling Features in Database Engine, Analysis Services, and Reporting Services

The third aspect of surface areas configuration that can reduce the surface areas of SQL Server, as mentioned previously, is concerned with the features in Database Engine, Analysis Services, and Reporting Services.

Enabling and Disabling Database Engine Features

SQL Server 2005 Database Engine provides the functionality to configure surface area by disabling or enabling its features. The option to enable or disable is not available for all features of Database Engine. The Microsoft SQL Server team made an extensive effort in reviewing all product features and decided on providing configuration options only for those features that were subject to a security threat. The goal of the team has also been to design a product that is secure by default. Most of these features are disabled by default. Accessing any of the disabled features returns an error indicating that the feature is disabled for the purposes of surface area reduction. For example, executing the stored procedure xp_cmdshell when it is disabled generates the following error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online. 


To enable or disable a feature of Database Engine, launch Surface Area Configuration from the Windows Start menu, select Surface Area Configuration for Features, select the Database Engine instance and the feature in the left pane, and enable or disable the feature on the right pane (see Figure 3-29). The right pane also provides a provisionary description of the feature, with additional details available if you click on Help or from Books Online.

Figure 3-29. Use the Surface Area Configuration tool to enable or disable Database Engine features.


Enabling or disabling SQL Server Database Engine features for a particular instance requires the Database Engine service for that instance to be running. In fact, the left pane of the Surface Area Configuration for Features dialog displays only components that are currently running. For example, in the scenario illustrated in Figure 3-29, the default instance (MSSQLSERVER) has two components: Database Engine and Analysis Services. If Database Engine is stopped, the left pane shows only Analysis Services under MSSQLSERVER instance. If Analysis Services is also stopped, the left pane does not display the MSSQLSERVER instance at all. In the event that all components are stopped, launching of Surface Area Configuration for Features dialog displays the error message show in Figure 3-30.

Figure 3-30. Enabling or disabling of features of a SQL Server component requires its service to be running.


The following sections describe the Database Engine features that could be enabled or disabled, along with a brief description of each feature.

Ad-hoc Remote Queries

The two T-SQL functions OPENROWSET and OPENDATASOURCE provide the functionality to run ad-hoc remote queries without necessarily defining a linked server. By default, these two functions are disabled on new installations of SQL Server 2005 Database Engine but enabled in instances upgraded from SQL Server 2000 or SQL Server 7.0.

CLR Integration

The integration of Common Language Run-time (CLR) inside SQL Server 2005 Database Engine provides the functionality to create and execute CLR-based objects. These objects include types, user-defined functions, stored procedures, and triggers. By default, running CLR objects is disabled, which means you can create, alter, or drop CLR-based objects but you cannot access these objects and run the CLR code.

Remote DAC

The dedicated administrator connection (DAC) is a high-priority connection to the server for troubleshooting when the server is "hung" and does not accept new connections. This feature is always available for tools and applications that run locally on the same computer as Database Engine. By default, DAC is disabled from a remote computer.

Database Mail

Database Mail is a new subsystem for sending e-mail messages from Database Engine. Database Mail relies on Service Broker and a few extended stored procedures that are disabled by default. Disabling Database Mail while it is busy processing emails in its queues does not stop Database Mail from sending the emails. If you need to stop Database Mail immediately, execute the stored procedure msdb.dbo.sysmail_stop_sp. For more information about Database Mail see Chapter 10.

SQL Mail

SQL Mail is the legacy subsystem for sending and receiving email messages from Database Engine. SQL Mail relies on a few extended stored procedures that are disabled by default in a new installation of SQL Server Database Engine but enabled in installations upgraded from SQL Server 2000 or SQL Server 7.0.

Note

The dependency of SQL Mail on Microsoft Outlook has been problematic for many customers. In SQL Server 2005, Microsoft has introduced a new subsystem called Database Mail, which uses SMTP for sending email messages. SQL Mail will be removed in future versions of SQL Server. If you are planning to use SQL Mail, consider using Database Mail instead.


Native XML Web Service

Native XML Web Service is a new feature in SQL Server 2005 that provides access to Database Engine through use of Simple Object Access Protocol (SOAP) messages over user-defined HTTP endpoints. HTTP endpoints can be stopped and disabled to reduce risk of malicious access to Database Engine.

OLE Automation

OLE Automation provides functionality to create and access OLE Automation objects from Database Engine. The OLE Automation functionality is exposed through a number of extended stored procedures, namely, sp_OAGetProperty, sp_OASetProperty, sp_OAMethod, and sp_OAGetErrorInfo. These stored procedures are disabled in new installations of SQL Server Database Engine, but enabled in installations upgraded from SQL Server 2000 or SQL Server 7.0.

Service Broker

Service Broker is a new feature in SQL Server 2005 that provides queuing and reliable messaging for Database Engine. Service Broker uses Simple Object Access Protocol (SOAP) messages over an endpoint to communicate with remote applications. Service Broker endpoints can be stopped and disabled to reduce the surface area of SQL Server.

Web Assistant

Web Assistant consists of a few stored procedures that generate HTML files from tables of a Database Engine. These stored procedures are sp_makewebtask, sp_dropwebtask, sp_runwebtask, and sp_enumcodepages. The Web Assistant stored procedures are disabled in new installations of SQL Server Database Engine, but enabled in instances upgraded from SQL Server 2000 or SQL Server 7.0.

Note

Web Assistant will be removed in future releases of SQL Server. If you are planning to use this feature, consider using Reporting Services instead.


xp_cmdshell

xp_cmdshell is a system extended stored procedure that runs a given operating system shell command. By default, xp_cmdshell is disabled in new installations of SQL Server Database Engine but enabled in instances upgraded from SQL Server 2000 or SQL Server 7.0.

SQL Server Agent Stored Procedures

SQL Server Agent is a component of SQL Sever that executes scheduled jobs. SQL Server Agent consists of a service and a number of stored procedures that provide the interface to manage features of the agent. SQL Server Agent stored procedures are enabled and disabled when the service is enabled or disabled.

Enabling and Disabling Analysis Services Features

SQL Server 2005 Analysis Services has a number of features that can be disabled to reduce surface area. By default, these features are disabled on a new installation of Analysis Services, and you should enable these features only if they are needed for your application. To enable or disable these features for an instance of Analysis Services, you can use SQL Server Surface Area Configuration tool (see Figure 3-31).

1.

Verify the instance of Analysis Services that the server is running.

2.

From Windows Start menu, point to All Programs/SQL Server 2005, Configuration Tools and select SQL Server Surface Area Configuration.

3.

On the main dialog of Surface Area Configuration, click on Surface Area Configuration for Features.

4.

On the left pane of Surface Area Configuration for Features dialog, select the instance of Analysis Services and its features. The right pane displays the current state of the feature. Using the controls in the right pane, you can enable or disable each of the Analysis Services features.

Figure 3-31. Use the Surface Area Configuration tool to enable or disable Analysis Services features.


Enabling or disabling of Analysis Services features requires the Analysis Services service to be running. In addition, configuring named instances (instances other than the default instance) requires SQL Server Browser service to be running. Viewing features of a named instance of Analysis Services while the SQL Server Browser Service is not running generates the error message shown in Figure 3-32.

Figure 3-32. Connections to named instances of Analysis Services require the SQL Server Browser service to be running.


The following sections detail the Analysis Services features that can be enabled or disabled in the Surface Area Configuration tool, along with a brief description of each feature:

Ad Hoc Data Mining Queries

Data Mining Extensions (DMX) is a language that allows execution of Data Manipulation Language (DML) and Data Definition Language (DDL) statements against a mining model in Analysis Services. The OPENROWSET function in DMX provides the functionality to use an Object Linking and Embedding Database (OLEDB) provider to execute an ad-hoc data mining query on a remote server. By default, Ad Hoc Data Mining functionality is disabled and should be enabled only if it is needed in your application.

To further control the surface area of the OPENROWSET function, you can enable or disable the passing of the OLEDB provider name and connection string from the Analysis Server Properties dialog in SQL Server Management Studio (see Figure 3-33).

1.

On Windows Start menu, point to All Programs/SQL Server 2005, and select SQL Server Management Studio.

2.

In the Connect to Server dialog, change the Server type option to Analysis Services and enter the Analysis Services server name.

3.

In Management Studio, locate the Object Explorer window, right-click on the server node (top-level node in the tree) and select Properties from the exposed menu.

4.

In the Analysis Server Properties dialog, select the Show Advanced (All) Properties option, located on the bottom of the dialog.

5.

Locate the server property named DataMining\AllowProvidersInOpenrowset and set its Value to true or false to enable or disable it.

Figure 3-33. Although most of the Analysis Services surface area settings are exposed in the Surface Area Configuration tool, some of the advanced settings can be controlled only from the Server Properties dialog in SQL Server Management Studio.


Anonymous Connections

The Anonymous Connections feature of Analysis Services enables users to connect to Analysis Services with no authentication. The Anonymous Connections feature is disabled by default and it is highly recommended that you keep this feature disabled unless it is absolutely needed by your application.

Linked Objects

Analysis Services provides the functionality to link objects such as measure groups and dimensions between two instances of Analysis Services. To enable this functionality, you need to enable one option on each of the instances. On the instance where the linked object is created, you need to enable the Enable links to other instances option, and on the referenced instance you need to enable the Enable links from other instances option. Both these options are disabled by default on new installations of Analysis Services.

User-Defined Functions

Analysis Services provides the functionality to create and run user-defined functions based on Common Language Runtime (CLR) or Component Object Model (COM) objects. By default, execution of CLR and COM-based user-defined functions is disabled, which means you can create or delete this type of user-defined function, but you cannot run the CLR code by accessing these functions.

Enabling and Disabling Reporting Services Features

SQL Server 2005 Reporting Services provides the functionality to configure surface area by disabling or enabling some of its features. By default, these features are disabled on a new installation of Reporting Services. You should enable these features only if they are needed in your applications. To enable or disable these features for a specific instance of Reporting Services, you can use SQL Server Surface Area Configuration (see Figure 3-34):

1.

Verify the instance of Reporting Services that the server is running.

2.

From the Windows Start menu, point to All Programs, SQL Server 2005, Configuration Tools and select the SQL Server Surface Area Configuration tool.

3.

On the main dialog of Surface Area Configuration, click on Surface Area Configuration for Features.

4.

On the left pane, select the instance of Reporting Services and one of its features. In the right pane, you can view, enable, or disable the feature.

Figure 3-34. Use the Surface Area Configuration tool to enable or disable Reporting Services features.


Enabling or disabling of Reporting Services features for a particular instance requires the Reporting Services service for that instance to be running. The right pane of the Surface Area Configuration for Features dialog does not display Reporting Services instances that are disabled.

The following sections describe the Reporting Services features that can be enabled or disabled in the Surface Area Configuration tool.

Scheduled Events and Report Delivery

You can reduce the surface area of SQL Server 2005 Reporting Services by disabling its Scheduled Events and Report Delivery feature. Scheduled Events and Report Delivery provides the functionality to schedule operations such as taking report snapshots, delivering reports, and sending cache expiration notifications. You should enable this feature if your implementation of Reporting Services requires report subscription or scheduling report snapshots.

Web Service and HTTP Access

Tools and client applications can use SOAP and URL requests to access a Reporting Server. You can reduce the surface area of Reporting Services by disabling the Web Service and HTTP Access feature. However, disabling this feature also prevents some of the SQL Server tools such as Report Manager, Report Builder, and Management Studio from connecting to the Reporting Services instance.

Windows Integrated Security

A report in Reporting Services accesses and aggregates data from a variety of data sources. With the Windows Integrated Security feature enabled, the credentials of the user running the report are used to access the data. This could create security concerns if user's credentials are used without the user's consent. You can disable the Windows Integrated Security feature and use other methods for accessing report data. These methods include Prompted Credentials and Stored Credentials.

Copying Surface Area Settings between Two Computers

There are situations in which you need to copy all or part of surface area settings from one computer to another. The You may even need to deploy these surface area configuration settings to hundreds or thousands of computers.

The SAC command line utility allows exporting and importing of SQL Server surface area configuration settings to and from a file. These settings include service states, remote connections, and feature states. Here are the steps for copying surface area settings for all SQL Server components from Computer_A to Computer_B:

1.

Using the Surface Area Configuration tool, verify the surface area settings on Computer_A. This includes the states of all services, remote connection and state of features.

2.

On Computer_A, open a command prompt window and change the current directory to the same directory as SAC.EXE. For example, if SQL Server tools are installed on the C: drive, change the current directory to C:\Program Files\Microsoft SQL Server\90\Shared.

3.

At the command prompt, run SAC.EXE to export the surface area settings of Computer_A to a file name sac_settings.xml, as follows:

sac out sac_settings.xml S Computer_A 


4.

At the same command prompt, run SAC.EXE on to import the surface area settings from file sac_settings.xml to Computer_B:

sac in sac_settings.xml S Computer_B 


There are a couple of tips worth noting: SAC exports the feature settings only for components that are running. After exporting and importing the surface area settings, if you notice that certain features are not deployed on your target computer it is most likely because the corresponding components are not running on the source computer. To verify the export operation, you can open the export file in Notepad or Internet Explorer and search for an XML tag named <Features>. If you cannot find the tag, start the component and rerun the command in step 3. Second, execution of the command in step 4 requires remote connections to be enabled on Computer_B. There are two ways to work around this issue. You can run Surface Area Configuration on Computer_A, connect it remotely to Computer_B, enable remote connections on Computer_B, restart the service, and run the command in step 4. Another option is to copy the exported file to Computer_B and run the command in step 4 in a command prompt window on Computer_B.

If you want to deploy the surface area settings to several computers (e.g., Computer_B, Computer_C, Computer_D, ...), you can create a batch file with the command in step 4 for each of the computers. Once again, this assumes remote connections are enabled on the target computers:

sac out sac_settings.xml S Computer_A sac in sac_settings.xml S Computer_B sac in sac_settings.xml S Computer_C sac in sac_settings.xml S Computer_D sac in sac_settings.xml S Computer_E 


The commands in steps 3 and 4 copy the surface area settings for all components of SQL Server on the source computer. If you need to deploy a subset of the settings, you can use the SAC command line switches. The SAC command line switches enable you to fine-tune the settings deployed between two computers. For example, if Computer_A has several instances of SQL Server and you want to deploy just the Database Engine features of one instance to Computer_B, you can use the -I, -DE, and -F switches to accomplish this.

sac out sac_DE_mssqlserver_settings.xml S Computer_A I MSSQLSERVER DE F 


Remember, the default instance of SQL Server is named MSSQLSERVER.

Stopping SQL Server Services Across Multiple Instances

Consider the situation in which you urgently need to stop all SQL Server services on all computers in your network. One option is to log on to every computer and stop the services manually, but the manual operation will not scale if you have to stop tens or hundreds of instances of SQL Server.

You can use the SAC command line utility to stop all SQL Server services on a computer. Here are the steps:

1.

Choose a computer that has the same instances and components as your production computers. Using the Surface Area Configuration tool or SQL Server Configuration Manager, stop all SQL Server services on this source computer.

2.

Using the SAC command line utility, export the service settings of the source computer to a file:

sac out sac_stop_all_services.xml T 


3.

Use SAC to import the file in step 3 to any computer in your network to stop its services. You can create a batch file with multiple SAC commands to stop services on several computers (e.g., Computer_B, Computer_C, Computer_D, ...)

sac in sac_stop_all_services.xml S Computer_B sac in sac_stop_all_services.xml S Computer_C sac in sac_stop_all_services.xml S Computer_D sac in sac_stop_all_services.xml S Computer_E 


Notice that the export file also includes the services startup mode, which is applied to the target computers upon import of the file. As part of step 1, you need to make sure the service startup modes are set the same as those on the target computers.

Configuring Surface Area of a Remote Computer

Both the Surface Area Configuration tool and SAC command line utility provide the functionality to configure the surface area of a SQL Server on a remote computer. To connect SAC to a remote computer, you can use its command line S switch. To connect Surface Area Configuration to a remote computer, click on the link change computer on its main dialog (see Figure 3-35).

Figure 3-35. Surface Area Configuration can connect to a remote computer.


Clicking on change computer launches the Select Computer dialog, where you can enter the name of a remote computer or select the option to connect to the local computer (see Figure 3-36).

Figure 3-36. The Select Computer dialog.


The name of the remote computer is displayed back on the main Surface Area Configuration dialog (see Figure 3-37). This is useful for situations in which you need to quickly figure out the name of the computer to which Surface Area Configuration is connected.

Figure 3-37. Surface Area Configuration can connect to a remote computer.


Now, if you launch the Surface Area Configuration for Services and Connections or Surface Area Configuration for Features dialogs, they will be launched in the context of the remote computer. The name of the remote computer is also displayed in the title bar of these dialogs.

Sending Information About Feature Usage and Serious Errors to Microsoft

SQL Server 2005 provides functionality to send information about feature usage and serious system errors to Microsoft. This information helps the SQL Server team to improve the product by fixing the most common bugs and gives them a better understanding of product usage scenarios. You can choose to opt in or out of each of these options for all instances on your computer or for a specific component.

By default, SQL Server error and usage information is collected once per day at 12:00 a.m. If 12:00 a.m. is not a suitable time for your server, you can change the time of collection by manually editing the Registry key that controls the collection time. There is a Registry key for each SQL Server instance identified by its instance ID. For example, the following is the Registry key with instance ID of MSSQL.1.

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\CPE\ TimeofReporting 


The value of the Registry key is the number of minutes from 12:00 a.m. when the information will be sent to Microsoft. For example, a value of 30 would set the collection at 12:30 a.m., and value of 600 would run the collection at 10:00 a.m.

The information that is sent to Microsoft is classified in two categories: Error Reporting and Feature Usage. These two options can be found in the Error and Usage Report Settings dialog. As you can see in Figure 3-38, the labels for these two options are somewhat wordier than what we have been calling them. This happened after the labels were reviewed by the Microsoft legal team.

Figure 3-38. The simple view of Error and Usage Report Settings provides the functionality to opt in or out of sending information for all components.


The Error and Usage Report Settings dialog (see Figure 3-38) has two modes: Simple and Advanced. In the simple mode, you can choose to opt in or out of error and usage reporting for all components in all instances of SQL Server.

The advanced mode of the Error and Usage Report Settings dialog (see Figure 3-39) provides a more granular control of components that can send error and usage reports to Microsoft. On the Error and Usage Report Settings dialog, click on Options to view the error and usage settings for every component in every instance.

Figure 3-39. The advanced view of Error and Usage Report Settings provides the functionality to opt in or out of sending information for a specific component.


At first glance, you may find the information in the grid rather confusing. However, as soon as you understand the rules applied to classify the information, you will likely find it intuitive. There is a row for each of the instance-aware components such as Database Engine, Analysis Services, and Reporting Services. Other components are presented as Others in All Instances. Also, unlike other tools, the default instance is displayed as <Default>.




Inside SQL Server 2005 Tools
Inside SQL Server 2005 Tools
ISBN: 0321397967
EAN: 2147483647
Year: 2006
Pages: 149

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