SQL Server 2005 – Testing Troubleshooting

In this tutorial you will learn about Testing and Troubleshooting, SQL server services: Distributed Transaction Coordinator, Registry settings, SQL server settings, Configuring SQL server internal settings, Configuring Network Protocols.

Testing and Troubleshooting

Components of SQL Server may fail individually or individual components may fail when the SQL Server is run. This may be due to installation problems or other errors. A best practice that is followed is to enable the Error and usage Report settings at the time of installation. In SQL server 2005 the Usage data collection and Error Reporting features are enabled by default.

When Error Reporting is enabled SQL Server is configured to sent a report to Microsoft automatically if a fatal error occurs in any of the following installed components.

• The Database Engine
• SQL Server Agent
• Analysis Services
• Reporting Services
• Notification Services
• Integration Services
• Replication

The purpose is to enable Microsoft improve SQL Server functionality. This information is treated as confidential and is sent over secure connections to Microsoft. Error reports contain the following information:

• The condition of SQL Server when the problem occurred.
• The operating system version and computer hardware information.
• The Digital Product ID, which is not used to identify the license.
• The network IP address of the computer or proxy server.
• Information from memory or file(s) of the process that caused the error.

When an error of this type occurs, the user can see the response from Microsoft in the Windows Event log. This feature can be disabled by navigating to the Error and usage Report settings dialog box and clear the checkboxes for Feature usage and Error Reporting.

Feature Usage reporting configures SQL Server periodically to send a report to Microsoft. This includes details about the hardware configuration and how the SQL Server 2005 software and services are being used. The components monitored by this feature include:

• The Database Engine
• Analysis Services
• Reporting Services
• Notification Services
• Integration Services
• Replication
• Business Intelligence Workbench

This information is stored at Microsoft and has limited access. This feature can be disabled as in the case of Error Reporting.

SQL Server creates log files for all SQL Server Components during setup. Logging begins as soon as the Server is launched. Logs can be accessed by opening Windows Explorer and navigating to the relevant folder under All Programs > Microsoft SQL Server > 90 > Setup bootstrap > LOG.

The logging process for Microsoft SQL Server Setup has been changed in SQL Server 2005. The main Log is located in the LOG folder as Summary.txt. If a product failure is displayed in the Log, it is important to investigate the cause. The Product logs are located under files in the LOG folder.

The logs follow a specific naming convention. It contains the enumerated number of the install, the computer name, the Product name and if Microsoft Windows Installer file has been installed more than once during a single setup.

SQL Server Books online, Tutorials, FAQs and detailed documentation on likely problems for the different services provide a mine of information to the user. However, a detailed study of each of these problems is beyond the scope of the tutorials and the user is advised to examine the documentation and the logs for further information in this context.

SQL server services: Distributed Transaction Coordinator

Registry settings

Each instance of SQL server has its own set of data files and program files. The IDs are incrementally generated for each new instance. If any gaps occur due to uninstalls of MSSQL, new ID numbers are not generated to fill the gaps. The instances also share some common files. For purpose of uniquely identifying an instance an all the related program and data files unique instance IDs are generated for each component of a given instance. All common files are located in the folder called 90 under Microsoft SQL Server.

During the installation of SQL Server the instance ID generated for each server component is an ordinal number in the format MSSQL.n. This instance ID is used in the file directory and the registry root.

A registry hive is created for each instance under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n for instance aware components. The mapping of the instance ID is also maintained in the registry.

SQL server settings

Configuring SQL server internal settings

Post installation the components and internal settings of an SQL Server instance can be configured. The installation path is displayed in setup only for features with a user configurable destination folder.

The Database Administrator needs to ensure that the Program Files\Microsoft SQL Server\Folder is protected with limited permissions. Since the COM directory is used by all instances of the SQL Server, an update impacts all instances of SQL Server on the computer.

SQL Server Configuration Manager Tool helps manage the services and for configuring the network protocols. This is a Microsoft management snap in console that can be accessed from the start menu. It uses the Windows Management Instrumentation (WMI) to view and change server settings. It is a unified way for interfacing with the API calls that manage the registry operations requested by SQL Server tools. SQL Services can also be managed with the configuration manager. Services can be started, stopped or paused. Passwords can be changed and impacted even before starting a service.

Configuring Network Protocols.

The Client and server network protocols and connectivity options can be configured using the SQL Server configuration manager. The protocols are enabled using Surface Area configuration tool and the network connections can be changed. The configuration manager can be used to reconfigure the server connections so that SQL Server listens on a particular network protocol, pipe or port. The ability to force encryption can be set. Alias properties can be viewed or a protocol can be enabled or disabled. Aliases can be created or removed or the order of protocols can be changed. The configuration manager also provides the DBA the ability to view information on failover cluster instances in addition to the abilities being provided by the Cluster Administrator.

The available network protocols of SQL Server are Shared memory, TCP/IP, Named pipes and VI A protocols.