Clustered SQL Server do's, don'ts, and basic warnings

Good practice for SQL Server installation

When you install SQL Server 2000 or SQL Server 2005, we highly recommend that you have your domain administrator follow the instructions that are described in the following Microsoft Knowledge Base article:

319723 How to use Kerberos authentication in SQL Server

Microsoft Knowledge Base article 319723 describes how to use Kerberos authentication in SQL Server. The article also describes how to enable Kerberos authentication on SQL Server failover clusters.

The instructions that are listed in Microsoft Knowledge Base article 319723 offer the following benefits:

• You can eliminate problems on the Service Principal Names (SPNs) that are registered correctly.
• You can avoid a warning message during SQL Server installation if you specify a domain user account as the SQL Server service startup account. This warning message is documented in the following Microsoft Knowledge Base article:
  303411 You receive a "Warning SuperSocket Info" warning information when a SQL Server service account is a domain user
  
  
• If you later change the IP address or the domain name, you do not have to manually update the SPNs of the SQL Server service.

Security

SQL Server 2000

The service account that you use to start the SQL Server service in a SQL Server failover cluster must be a direct member of the local administrators group. This service account must not be a member of the local administrators group through a group membership. That configuration may cause transitory security issues. If you do not want the service account to be in the local administrators group, you must grant the following user rights to the service account:

• Act as Part of the Operating System = SeTcbPrivilege
• Bypass Traverse Checking = SeChangeNotify
• Lock Pages In Memory = SeLockMemory
• Log on as a Batch Job = SeBatchLogonRight
• Log on as a Service = SeServiceLogonRight
• Replace a Process Level Token = SeAssignPrimaryTokenPrivilege

SQL Server 2005

For each clustered service in an instance of SQL Server 2005 that you install, you are prompted to enter a domain name and a group name on the Domain Groups for Clustered Services page of the SQL Server Installation Wizard. You enter the domain name and the group name in the DomainName\GroupName format. You must use the following guidelines when you specify the domain groups:

• The domain groups must be in the same domain as the account that you use to log on to the system. For example, if the account that you use to log on to the system is in the SQLSVR domain and if the SQLSVR domain is a child domain of the MYDOMAIN domain, you must specify a domain group in the SQLSVR domain. The SQLSVR domain may contain user accounts from the MYDOMAIN domain.
• The service account for a specific clustered service must be a direct member of the domain group. This service account must not be a member of the domain group through a group membership. The SQL Server 2005 Setup program will not check the subgroups to determine whether a service account is in a domain group.
• The service account for a specific clustered service must not be a direct member of the local administrators group.
• The domain and the domain group must already exist when you run the SQL Server 2005 Setup program. You can obtain the names of existing domain groups from the domain administrator. Or, you can create domain groups for the SQL Server 2005 failover cluster. If you create a domain group for the SQL Server 2005 failover cluster shortly before you run the SQL Server 2005 Setup program, you must wait awhile for the new domain group to replicate across the corporate network.
• The domain groups must contain appropriate service accounts. If the service accounts are not members of the appropriate domain groups when you install SQL Server 2005, the SQL Server 2005 Setup program adds the service accounts to the domain groups. Therefore, the account that you use to run the SQL Server 2005 Setup program must have sufficient permissions to add accounts to the domain groups.
  
  If the SQL Server Setup program runs under an account that does not have the permission to add accounts to the domain groups, the service accounts must already be members of the appropriate domain group.
• To maintain the most precise control over permissions, we recommend that you specify different domain group and a different service account for each clustered service and for each virtual server. However, you can still use the same domain group and the same service account for all SQL Server services. Or, you can use the same domain group and different service accounts for each SQL Server service.
• The domain group for the SQL Server clustered services must not be shared with any other application.
• The domain group must be the global domain group instead of the universal domain group.

For more information about these guidelines, click the following article number to view the article in the Microsoft Knowledge Base:

910708 You must specify the domain groups for the clustered SQL Server services when you install a SQL Server 2005 failover cluster

Note To troubleshoot domain group issues, you must have access to the domain controller.

Additionally, we do not support installing SQL Server 2005 failover cluster instances in an environment in which any node is a domain controller.

For more information about the user rights and permissions that are granted for the SQL Server service accounts and about the Access control lists (ACLs) that are created for the SQL Server service accounts, see the "Setting Up Windows Service Accounts" topic in SQL Server 2005 Books Online.

Start and stop SQL Server services

SQL Server 2005 failover cluster instances

SQL Server 2005 supports starting and stopping SQL Server services by using any of the following tools:

• Cluster Administrator
• SQL Server Configuration Manager
• SQL Server surface area configuration
• SQL Server Management Studio

Note Do not use the Services snap-in to stop a clustered instance of SQL Server. If you use the Services snap-in, the cluster service restarts SQL Server.

SQL Server 2000 failover cluster instances

SQL Server 2000 failover cluster instances do not have the above restrictions. We recommend that you use SQL Server Enterprise Manager, the SQL Server Services applet, or Cluster Administrator to start and to stop SQL Server 2000 virtual server services. Although you can use Service Control Manager or the Services item in Control Panel to start and to stop the services without damaging the registry, these options will not cause the services to stay in a stopped state. Instead, the services will be detected by the clustered server, and you will receive multiple event ID 17052 error messages in your SQL Server. These error messages resemble the following:After you receive these error messages, the cluster service restarts SQL Server. This behavior is expected for these types of errors.

SQL Server 6.5 and SQL Server 7.0 failover cluster instances

To start or stop SQL Server, SQL Server Executive, or SQL Agent services from a SQL Server 6.5 or SQL Server 7.0 virtual server, you must use the Microsoft Cluster Administrator or the Cluster.exe command line tool.

If you attempt to start or stop services in any other way (for instance, from Control Panel, SQL Service Manager, or SQL Enterprise Manager), the registry may be corrupted, and you may need to uncluster or completely reinstall SQL Server.

The most common sign of having started a service incorrectly is that the service accounts appear as a jumble of ASCII characters.

If you need to start SQL Server from a command line, you must use the Cluster Administrator or Cluster.exe tool to first take the SQL Server, SQL Executive, or SQL Agent services offline.

When you start SQL Server from a command line, connectivity takes place using the virtual server name. The only way to make a local connection is if the resources are owned by the node from which you originally installed SQL Server.

SQL Enterprise Manager

SQL Server 6.5 and SQL Server 7.0 failover cluster instances

You cannot change the service account names from SQL Enterprise Manager. If you need to change names, Microsoft recommends that you uncluster and then recluster SQL Server with the new domain user account. For more information on changing SQL Server service accounts, see the following article in the Microsoft Knowledge Base:

239885 How to change service accounts for a clustered computer that is running SQL Server

Warning If you fail to follow the instructions in article Q239885, you may need to manually remove SQL Server completely from both nodes, and then reinstall SQL Server after you secure your SQL Server databases.

If the service account for SQL Server is not an administrator in a cluster, the administrative shares cannot be deleted on any nodes of the cluster. The administrative shares must be available in a cluster for SQL Server to function.

SQL Server 2000 failover cluster instances

You must use SQL Enterprise Manager to make all changes to SQL service accounts or passwords.

SQL Server 2005 Analysis Services (OLAP)

SQL Server 2005 OLAP is fully cluster aware, and you can select clustered installations during initial setup.

SQL Server 2000 Analysis Services (OLAP)

Although the SQL Server 2000 Analysis Services (OLAP) component is not cluster-aware, it is possible to attain high-availability Analysis Services solutions by following the steps in the following article in the Microsoft Knowledge Base:

308023 How to cluster SQL Server 2000 Analysis Services in Windows 2000 and in Windows Server 2003

SQL connectivity

SQL Server 2005 network libraries

With the release of SQL Server 2005, the SQL Native Client has been added to the supported list of protocols. Supported protocols include the following:

• Shared memory
  
  Note Clients that use Microsoft Data Access Components (MDAC) 2.8 or earlier versions of MDAC cannot use a shared memory protocol. If you try to use a shared memory protocol, the clients are automatically switched to the Named Pipes protocol.
• Named Pipes
• TCP/IP
• VIA
• SQL Native Client
  
  SQL Native Client (SQLNCLI) is a data access technology that is new in SQL Server 2005. The SQL Native Client is a stand-alone data access application programming interface (API) that is used for both OLE DB and ODBC. The SQL Native Client combines the SQL Server OLE DB Provider and the SQL Server ODBC Driver into one native DLL. The SQL Native Client also provides new functionality that is separate and distinct from MDAC. Use the SQL Server Setup program to install the SQL Native Client as part of SQL Server 2005 Tools. For more information about this and the other network libraries, see SQL Server 2005 Books Online.
  
  Note SQL Server 2005 does not support the Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols. Clients that previously connected by using these protocols must select a different protocol to connect to SQL Server 2005.

SQL Server 2000 network libraries

Clustered SQL Server installations require the TCP/IP protocol, and we recommend that you install and enable the Named Pipes protocol. TCP/IP is required because it is the only supported protocol for use with server clusters.

For more information about the Named Pipes requirement, click the following article number to view the article in the Microsoft Knowledge Base:

831127 Named Pipes support cannot be removed on a virtual server that is running SQL Server 2000 Service Pack 3

Any additional resources that are added to a SQL group must have their own dedicated NetworkName and IPAddress resources.

SQL Server 6.5 and SQL Server 7.0 failover cluster instances

If any cluster resources are dependent on any SQL Server resources, you must remove those dependencies before you uncluster your virtual server. If you do not do this, your virtual server will be incompletely removed and will not be able to be re-clustered until the failed SQL cluster removal is completed.

Note If the quorum drive is used for additional MSCS resources and those resources cause a failover, all cluster resources are unavailable until that cluster resource and the cluster IP address and network name are back online.

Warning Any changes to the network settings in SQL Server 6.5 must be made while SQL Server is unclustered, as outlined in the following article in the Microsoft Knowledge Base:

189037 BUG: SQL Setup does not change security and network support options with SVS

For additional information on common connectivity issues when you connect or configure a clustered SQL Server server, see the following articles in the Microsoft Knowledge Base:

273673 Description of SQL Virtual Server client connections

235987 Virtual SQL Server 7.0-based server only supports the use of one TCP/IP address

244980 How to change the network IP addresses of SQL Server failover cluster instances

187708 Cannot connect to SQL virtual server via sockets in cluster

Multiple listen-on TCP/IP ports

SQL Server 7.0 provides support for multiple listen-on ports on a single subnet. This support is not intended for use on multiple subnets or to provide additional availability.

If you require multiple listen-on TCP/IP ports, you need to make the following modifications in the registry before you run the Cluster wizard.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756 How to back up and restore the registry in Windows

1. Start Registry Editor (Regedt32.exe).
2. Locate the ListenOn value in the following registry key:
   HKEY_LOCAL_Machine\Software\Microsoft\MSSQLServer\MSSQLServer
3. On the Edit menu, click Multi String, and enter additional listen-on ports. For example, to add port 1435, enter the following and then click OK:
   SSMSSO70,1435
4. Quit the Registry Editor.

Here are some examples of other ports that you might add:

• SSMSSO70,1436
• SSMSSO70,1437

Test the connectivity to the ports you add, and then continue with the Cluster wizard.

SQL Server (all versions) and WINS configuration

Before you cluster SQL Server, make sure that you have the proper configuration for the Windows Internet Name Service (WINS) for use on a cluster, as explained in the following articles in the Microsoft Knowledge Base:

193890 Recommended WINS configuration for Microsoft Cluster Server

195462 WINS registration and IP address behavior for Microsoft Cluster Server

You should never add static entries in WINS for clustered SQL Server servers or other Microsoft Cluster Server (MSCS) resources; this is explained in the following article in the Microsoft Knowledge Base:

217199 Static WINS entries cause the network name to go offline

Performance counters onSQL Server 7.0 failover cluster instances

SQL Server performance monitor counters (extension counters) for the virtual server are not present when SQL Server 7.0 is set up with a virtual SQL Server configuration and the passive node has control of the resources. The counters will not be available again to the primary node until the entire cluster is shut down and restarted. Even then, availability is sporadic.

The SQL Server extension counters must be found when the system initially starts. With SQL Server 6.5, the counters DLL is located in the \\Mssql\Binn directory by default. Because the cluster drive in which SQL Server is installed is not accessible until all of the MSCS resources are online, the counters are not found when the initial system startup occurs.

SQL Server 7.0 places these counters in the proper directory, %Systemroot%\System32\, so that they are available. To make the Sqlctr65.dll file available, place a copy of the Sqlctr65.dll file in the %Systemroot%\System32 directory. The Sqlctr70.dll file is placed in this directory by default.

For additional information on SQL Server performance counters, see the following articles in the Microsoft Knowledge Base:

127207 Missing objects and counters in Performance Monitor

246328 SQL performance counters may be missing after MDAC installation on a cluster

Warning For SQL Server 6.5, if you decide to rebuild the registry by means of the instructions in the following article in the Microsoft Knowledge Base, see the section "How to Rebuild the SQL Server Registry" later in this article for additional instructions before you take the steps to rebuild the registry:

227662 SQL Performance Monitor counters missing

To summarize, performance counters are not always available on clustered SQL Servers; when they are, they are usually only on the primary node if no failover has occurred.

Rename the resources created by the SQL Server 6.5 or SQL Server 7.0 Cluster Failover Wizard

When you run the SQL Server Cluster Failover Wizard, part of the process includes the creation of the SQL cluster resources. By default, these resources have the following naming structure: For example, if Virtual_SQL_Server_Name is xyz, the SQL Server resources are named as follows by default:
If all or part of these names are modified to be as follows: the SQL Cluster Failover Wizard may fail or stop responding. For additional information on SQL Cluster Failover Wizard failures, see the following article in the Microsoft Knowledge Base:

254593 Troubleshooting SQL Cluster Wizard failures

How to rebuild the SQL Server registry on SQL Server 6.5 and 7.0 failover cluster instance installations

SQL Server 6.5 Enterprise Edition

While SQL Server 6.5 Enterprise Edition is clustered, do not attempt to perform a SQL Server registry rebuild with the following command line: You must uncluster SQL Server before you perform the registry rebuild.

SQL Server 7.0 Enterprise Edition

If you use the Regrebld.exe file from SQL Server 7.0, you can rebuild the registry in a clustered environment with the following restrictions:

• Do not change anything from the previous setup of master.
• Run this utility only from the primary node for SQL Server.

Ignoring these restrictions may cause registry issues.

Service packs

Warning Before you try any service pack installations, make sure that you have the proper permissions and rights. It is highly recommended that you log on to the server and to the SQL Server service account and use Windows Authentication during the process. If for some reason this account was removed from the Local Administrators group on the cluster nodes, please add it back to the group before you start the installation.

SQL Server 2005

Behavior with SQL Server 2005 has not changed from SQL Server 2000.

SQL Server 2000

With SQL Server 2000, there is no un-clustering. You start the service pack installation from the node that is in control of the SQL Server that you want to upgrade.

Note You can install Microsoft Windows NT service packs in the usual manner as described in the following article in the Microsoft Knowledge Base:

174799 How to install service packs in a cluster

SQL Server 6.5 or 7.0

You must uncluster SQL Server to install SQL Server service packs. You must also remove replication before you uncluster SQL Server, which is noted in the "Replication Issues" section of this article.

Replication

SQL Server 2005

Follow the Readme documentation that ships with all SQL Server updates or service packs to determine if you must follow special setup instructions for your particular installation.

SQL Server 2000

Follow the Readme documentation that ships with all SQL Server updates or service packs to determine if you must follow special setup instructions for your particular installation.

SQL Server 6.5 and SQL Server 7.0

You must remove replication before you uncluster SQL Server, as described in the following article in the Microsoft Knowledge Base:

247110 Replication must be removed before applying service pack

When you cluster SQL Server, you may break SQL Server replication; for additional details, see the following article in the Microsoft Knowledge Base:

236407 BUG: Active/passive cluster setup breaks replication and DTS

Full text search

Full text search is not available to clustered SQL Server 7.0 servers, as noted in SQL Server Books Online at the end of the "Configuring SQL Server Failover Support" section. Full text search is fully supported for use in SQL Server 2000 and later versions of SQL Server.

If you have an issue that requires you to rebuild or reinstall Full text search on a SQL Server 2000 failover cluster instance or on a SQL Server 2005 failover cluster instance, a complete uninstall and reinstall of the SQL Server failover cluster instance is the only supported recovery method.

SQL Mail

SQL Mail is not fully supported when used on a SQL Server failover cluster because MAPI is not cluster-aware. Support for SQL Mail when used with clustering is provided on a "reasonable effort" basis only, with no guarantees of stability or availability. Microsoft has confirmed this to be a problem in SQL Server 6.5, SQL Server 7.0, and SQL Server 2000 when used with failover clustering.

Operating system upgrades

Operating system upgrades are supported for clustered SQL Server servers as documented in the following articles in the Microsoft Knowledge Base:

239473 FIX: 70rebind.exe for Windows 2000 and MDAC upgrades on clustered SQL Server 7.0 servers

313037 How to upgrade SQL Server clusters to Windows Server 2003

Licensing

For information about licensing, see the following article in the Microsoft Knowledge Base:

175276 Licensing policy implementation with MSCS

Important cluster service administrative rules

Warning If you ignore any of the following rules, you will need to reinstall Microsoft Cluster Service.

• If you change the partition layout of any physical disk on the shared SCSI bus, restart both cluster nodes.
• Do not change the Windows NT computer name of a cluster node after you install MSCS.
• Do not repartition disks on the SCSI bus without first deleting disk resources.
• Do not change an IP address upon which a network name resource depends.
• Do not run diagnostic tools that make low-level writes to a physical disk. (This is possible only if you start the node under another operating system.)
• Do not reassign drive letters of system disks on any node.
• Do not write data to attached disks on the SCSI chain before you install MSCS.

Sharing of SQL Server cluster resources

Cluster disk resources used by SQL Server should not be used for other cluster services (such as the quorum drive, file or printer shares, or Internet Information servers) unless the cluster has only one cluster disk resource. If you do use the SQL Server cluster disk for any of these resources, it may significantly affect your failover time and may also initiate failovers of SQL Server when no SQL Server problem exists.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

835185 Failover cluster resource dependencies in SQL Server

Microsoft Data Access Components (MDAC)

SQL Server 6.5 and SQL Server 7.0 MDAC component upgrades

SQL Server 6.5 and SQL Server 7.0 clustered installations only support MDAC component upgrades up to MDAC version 2.5. MDAC 2.6 and MDAC 2.7 do not have server-side support for these versions.

However, you can use MDAC 2.6 and later on a client to connect to a clustered SQL Server 6.5 or SQL Server 7.0 installation.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

820754 MDAC 2.6 or later should not be installed on SQL Server 7.0 clusters

239473 FIX: 70rebind.exe for Windows 2000 and MDAC upgrades on clustered SQL Server 7.0 servers

Default MSDTC cluster resource location

By default, where the MSDTC resources are installed depends on the operating system.

Note Unless you have a specific need to change the group in which MSDTC is installed, it is recommended that you leave it in the default location. Additionally, on a cluster node, MSDTC must run as a clustered resource. If you configure MSDTC to run as a non-clustered resource, the distributed transactions may be orphaned and that may cause data corruption when a cluster failover occurs.

Windows NT 4.0
Installs the clustered MSDTC to the first group that contains a valid IP address resource, network name resource, and cluster disk resource. This is usually the SQL group.

Windows 2000
Installs to the cluster group by default and does use the quorum drive. Although it is recommended that the quorum drive only be used by the quorum, MSDTC is an exception to this rule. For issues on installing or rebuilding MSDTC on a SQL cluster, see the following article in the Microsoft Knowledge Base:

294209 How to rebuild or move a MSDTC installation to be used with a SQL failover cluster

Storage Area Networks (SAN) support

Microsoft Cluster Service and SQL Server failover cluster instances are supported in a Storage Area Networks (SAN) environment today. The HCL category cluster/multicluster device lists the set of SAN-capable storage devices whose component has passed cluster component candidate testing. Note however, that this component does not qualify for Microsoft Cluster Service support services. These services are available only for validated configurations shown in the "Cluster" category on the HCL. For more information, see the following articles in the Microsoft Knowledge Base:

280743 Windows clustering and geographically separate sites

834661 SQL Server 2000 Setup requires a drive letter when you use mounted drives

819546 SQL Server 2000 and SQL Server 2005 support for mounted volumes

A list of all validated hardware configurations can be found on the Hardware Compatibility List (HCL) located at the following Microsoft Web site:

Memory allocation (all versions)

When you allocate memory for clustered SQL Server servers, make sure that the summed value of the maximum server memory settings for all the instances of SQL Server plus any other cluster resource and local application requirements is less than the lowest amount of physical RAM available on any of the servers in the failover cluster.

64-bit SQL Server installations

You do not have to configure a 64-bit installation to use additional memory by enabling AWE or by modifying your Boot.ini file to include the PAE startup switch.

Troubleshooting virtual SQL servers

When troubleshooting issues on virtual SQL Server servers, it is important to note that troubleshooting must be done in a certain order unless the problem is a known SQL issue. Problems or failures with the hardware, operating system, networking, security or Microsoft Cluster Service can appear as SQL issues when in fact no SQL issues exist.

As soon as possible after a problem is detected, you should gather the SQL Server MPS reports from all of the failover cluster instance nodes. It is important that you use this tool on all nodes because of the close interaction, and the cause of the problem may be a source other than the node that hosts your SQL Server failover cluster instance.

Perform troubleshooting of virtual SQL Server issues by using the following methods in the given order:

• Hardware - Do the system event logs show any warnings or errors that cannot be explained? If you check the computer's system report, are any problem devices reported? If so, can they be explained?
• Operating System - Do the event logs show any kind of operating system problems, service failures, or driver problem?
• Networking - Do the event logs show loss of connectivity? NIC failures? DNS problems?
• Security - Check for access denied errors, security log failures.
• MSCS - Does the cluster report problems in any of the event logs?
• SQL Server - Does SQL Server report any specific error messages in the error log or event logs?

SQL Server service properties

You must set SQL Server services startup type to Manual. The use of Automatic startup is not supported for use with Virtual SQL Servers.

SQL Server is not supported for Terminal Services in application server mode

SQL Server failover clustering is not supported for use with Terminal Server.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

327270 SQL Server 2000 is not supported on Windows Server 2003 Terminal Server application server

Support for Microsoft Windows 2003

Only SQL Server 2000 Service Pack 3 (SP3) or a later version is supported, as mentioned in the following Microsoft Knowledge Base article:

313037 How to upgrade SQL Server clusters to Windows Server 2003