Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Clustered SQL Server do's, don'ts, and basic warnings
Article ID: 254321 - View products that this article applies to.
This article was previously published under Q254321
This article describes some important do's and don'ts for using SQL Server clustered servers with the different versions of SQL Server.
Important All customers who are still using clustering with SQL Server 6.5 and SQL Server 7.0 should upgrade to SQL Server 2000 or SQL Server 2005 as soon as it is feasible to do so. The following tools, features, and components are supported with failover clustering in SQL Server 2000 Enterprise Edition:
327518SQL Server Enterprise Edition must be installed for support if SQL Server is being used together with Microsoft Cluster Server (MSCS).
(http://support.microsoft.com/kb/327518/ )The Microsoft SQL Server support policy for Microsoft Clustering
Good practice for SQL Server installationWhen 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:
319723Microsoft 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.
(http://support.microsoft.com/kb/319723/ )How to use Kerberos authentication in SQL Server
The instructions that are listed in Microsoft Knowledge Base article 319723 offer the following benefits:
SQL Server 2000The 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:
SQL Server 2005For 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:
910708Note To troubleshoot domain group issues, you must have access to the domain controller.
(http://support.microsoft.com/kb/910708/ )You must specify the domain groups for the clustered SQL Server services when you install a SQL Server 2005 failover cluster
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 instancesSQL Server 2005 supports starting and stopping SQL Server services by using any of the following tools:
SQL Server 2000 failover cluster instancesSQL 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:
[sqsrvres] CheckServiceAlive: Service is dead [
[sqsrvres] OnlineThread: service stopped while waiting for QP
After you receive these error messages, the cluster service restarts SQL Server. This behavior is expected for these types of errors.
[sqsrvres] OnlineThread: Error 1 bringing resource online
SQL Server 6.5 and SQL Server 7.0 failover cluster instancesTo 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 instancesYou 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:
239885Warning 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.
(http://support.microsoft.com/kb/239885/ )How to change service accounts for a clustered computer that is running SQL Server
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 instancesYou 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:
(http://support.microsoft.com/kb/308023/ )How to cluster SQL Server 2000 Analysis Services in Windows 2000 and in Windows Server 2003
SQL Server 2005 network librariesWith the release of SQL Server 2005, the SQL Native Client has been added to the supported list of protocols. Supported protocols include the following:
SQL Server 2000 network librariesClustered 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:
831127Any additional resources that are added to a SQL group must have their own dedicated NetworkName and IPAddress resources.
(http://support.microsoft.com/kb/831127/ )Named Pipes support cannot be removed on a virtual server that is running SQL Server 2000 Service Pack 3
SQL Server 6.5 and SQL Server 7.0 failover cluster instancesIf 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:
189037For 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:
(http://support.microsoft.com/kb/189037/ )BUG: SQL Setup does not change security and network support options with SVS
(http://support.microsoft.com/kb/273673/ )Description of SQL Virtual Server client connections
(http://support.microsoft.com/kb/235987/ )Virtual SQL Server 7.0-based server only supports the use of one TCP/IP address
(http://support.microsoft.com/kb/244980/ )How to change the network IP addresses of SQL Server failover cluster instances
(http://support.microsoft.com/kb/187708/ )Cannot connect to SQL virtual server via sockets in cluster
Multiple listen-on TCP/IP portsSQL 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:
(http://support.microsoft.com/kb/322756/ )How to back up and restore the registry in Windows
SQL Server (all versions) and WINS configurationBefore 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:
(http://support.microsoft.com/kb/193890/ )Recommended WINS configuration for Microsoft Cluster Server
195462You 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:
(http://support.microsoft.com/kb/195462/ )WINS registration and IP address behavior for Microsoft Cluster Server
(http://support.microsoft.com/kb/217199/ )Static WINS entries cause the network name to go offline
Performance counters onSQL Server 7.0 failover cluster instancesSQL 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:
(http://support.microsoft.com/kb/127207/ )Missing objects and counters in Performance Monitor
246328Warning 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:
(http://support.microsoft.com/kb/246328/ )SQL performance counters may be missing after MDAC installation on a cluster
227662To 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.
(http://support.microsoft.com/kb/227662/ )SQL Performance Monitor counters missing
Rename the resources created by the SQL Server 6.5 or SQL Server 7.0 Cluster Failover WizardWhen 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:
<Virtual_SQL_Server_Name> IP AddressFor example, if Virtual_SQL_Server_Name is xyz, the SQL Server resources are named as follows by default:
<Virtual_SQL_Server_Name> Network Name
<Virtual_SQL_Server_Name> SQL Server 7.0
<Virtual_SQL_Server_Name> SQL Server Agent 7.0
xyz IP AddressIf all or part of these names are modified to be as follows:
xyz Network Name
xyz SQL Server 7.0
xyz SQL Server Agent 7.0
IP Addressthe 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:
(http://support.microsoft.com/kb/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 EditionWhile SQL Server 6.5 Enterprise Edition is clustered, do not attempt to perform a SQL Server registry rebuild with the following command line:
setup /t RegistryRebuild = OnYou must uncluster SQL Server before you perform the registry rebuild.
SQL Server 7.0 Enterprise EditionIf you use the Regrebld.exe file from SQL Server 7.0, you can rebuild the registry in a clustered environment with the following restrictions:
Service packsWarning 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 2005Behavior with SQL Server 2005 has not changed from SQL Server 2000.
SQL Server 2000With 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:
(http://support.microsoft.com/kb/174799/ )How to install service packs in a cluster
SQL Server 6.5 or 7.0You 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.
SQL Server 2005Follow 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 2000Follow 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.0You must remove replication before you uncluster SQL Server, as described in the following article in the Microsoft Knowledge Base:
247110When you cluster SQL Server, you may break SQL Server replication; for additional details, see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/247110/ )Replication must be removed before applying service pack
(http://support.microsoft.com/kb/236407/ )BUG: Active/passive cluster setup breaks replication and DTS
Full text searchFull 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 MailSQL 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 upgradesOperating system upgrades are supported for clustered SQL Server servers as documented in the following articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/239473/ )FIX: 70rebind.exe for Windows 2000 and MDAC upgrades on clustered SQL Server 7.0 servers
(http://support.microsoft.com/kb/313037/ )How to upgrade SQL Server clusters to Windows Server 2003
LicensingFor information about licensing, see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/175276/ )Licensing policy implementation with MSCS
Important cluster service administrative rulesWarning If you ignore any of the following rules, you will need to reinstall Microsoft Cluster Service.
Sharing of SQL Server cluster resourcesCluster 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:
(http://support.microsoft.com/kb/835185/ )Failover cluster resource dependencies in SQL Server
Microsoft Data Access Components (MDAC)
SQL Server 6.5 and SQL Server 7.0 MDAC component upgradesSQL 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:
(http://support.microsoft.com/kb/820754/ )MDAC 2.6 or later should not be installed on SQL Server 7.0 clusters
(http://support.microsoft.com/kb/239473/ )FIX: 70rebind.exe for Windows 2000 and MDAC upgrades on clustered SQL Server 7.0 servers
Default MSDTC cluster resource locationBy 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.
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:
(http://support.microsoft.com/kb/294209/ )How to rebuild or move a MSDTC installation to be used with a SQL failover cluster
Storage Area Networks (SAN) supportMicrosoft 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:
(http://support.microsoft.com/kb/280743/ )Windows clustering and geographically separate sites
(http://support.microsoft.com/kb/834661/ )SQL Server 2000 Setup requires a drive letter when you use mounted drives
(http://support.microsoft.com/kb/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 installationsYou 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 serversWhen 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.
For more information about the Mps_sql.exe tool, click the following article number to view the article in the Microsoft Knowledge Base:
883724Please perform troubleshooting of virtual SQL Server issues in the following order:
(http://support.microsoft.com/kb/883724/ )Information about the SQL Server edition of the MPS Reporting tool
SQL Server service propertiesYou 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 modeSQL 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:
(http://support.microsoft.com/kb/327270/ )SQL Server 2000 is not supported on Windows Server 2003 Terminal Server application server
Support for Microsoft Windows 2003Only SQL Server 2000 Service Pack 3 (SP3), or a later version, is supported as noted in the following Microsoft Knowledge Base article:
(http://support.microsoft.com/kb/313037/ )How to upgrade SQL Server clusters to Windows Server 2003
Article ID: 254321 - Last Review: September 29, 2008 - Revision: 16.1