Article ID: 254321
Article Last Modified on 9/14/2007
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 7.0 Enterprise Edition
- Microsoft SQL Server 6.5 Enterprise Edition
This article was previously published under Q254321
Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows registry
SUMMARY
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:
- Microsoft Search service
- Multiple instances
- SQL Server Enterprise Manager
- Service Control Manager
- Replication
- SQL Profiler
- SQL Query Analyzer
- SQL Server 2005 Analysis Services
Note Microsoft Product Support Services (PSS) supports SQL Server Failover Clustering as described in the following Microsoft Knowledge Base article:
327518 The Microsoft SQL Server support policy for Microsoft Clustering
SQL Server Enterprise Edition must be installed for support if SQL Server is being used together with Microsoft Cluster Server (MSCS).
MORE INFORMATION
Security
SQL Server 2005
Security has changed for SQL Server 2005 failover clusters from security for SQL Server 2000 failover clusters. By default, SQL Server 2005 is more secure, and changes have been made to some of the basic requirements. SQL Server 2000 installations required the logon service accounts to be members of the local Administrators group. With SQL Server 2005, all service accounts that will administer SQL Server, SQL Server Agent, Full-Text Search, or SQL Server 2005 Analysis Services must be added to a domain group that has been added to the local Administrators group.
SQL Server 2000
The service account requirements include the following:
All domain user accounts must have permission to do the following:
- Access and change the SQL Server folder. By default, this folder is \Program Files\Microsoft SQL Server\Mssql.
- Access and change the .mdf, .ndf, and .ldf database files.
- Log on as a service.
- Read and write registry keys at and under the following registry keys:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
Or the following registry key for any named instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer
Or the following registry key for any named instance:
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
Additionally, a domain user account must be able to read and write corresponding registry keys for these services: SQLAgent$InstanceName, MSSearch, and MSDTC.
| Service | Permission | Functionality |
| SQL Server | Network write privileges | Write to a mail slot by using xp_sendmail. |
| SQL Server | Act as part of operating system, and replace a process level token | Run xp_cmdshell for a user other than a SQL Server administrator. |
| SQL Server Agent | Member of the Administrators local group | Create CmdExec and ActiveScript jobs that belong to someone other than a SQL Server administrator.
|
| SQL Server | Member of local Power Users or local Administrators group | Administer the Cluster.
|
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 available means. No restrictions exist on which method is used.
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.
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
- Start Registry Editor (Regedt32.exe).
- Locate the ListenOn value in the following registry key:
HKEY_LOCAL_Machine\Software\Microsoft\MSSQLServer\MSSQLServer
- 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
- 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:
<Virtual_SQL_Server_Name> IP Address
<Virtual_SQL_Server_Name> Network Name
<Virtual_SQL_Server_Name> SQL Server 7.0
<Virtual_SQL_Server_Name> VServer
<Virtual_SQL_Server_Name> SQL Server Agent 7.0
For example, if Virtual_SQL_Server_Name is xyz, the SQL Server resources are named as follows by default:
xyz IP Address
xyz Network Name
xyz SQL Server 7.0
xyz VServer
xyz SQL Server Agent 7.0
If all or part of these names are modified to be as follows:
IP Address
Network Name
SQL Server
Virtual Server
SQL Agent
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:
setup /t RegistryRebuild = On
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.
For more information about the Mps_sql.exe tool, click the following article number to view the article in the Microsoft Knowledge Base:
883724 Information about the SQL Server edition of the MPS Reporting tool
Please perform troubleshooting of virtual SQL Server issues in the following 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 noted in the following Microsoft Knowledge Base article:
313037 How to upgrade SQL Server clusters to Windows Server 2003
Additional query words: MSCS
Keywords: kbhowto kbfaq kbinfo KB254321
