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
- SQL Profiler
- SQL Query Analyzer
- SQL Server 2005 Analysis Services
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:
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.
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:
- 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 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:
- 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.
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:
- Cluster Administrator
- SQL Server Configuration Manager
- SQL Server surface area configuration
- SQL Server Management Studio
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:
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:
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:
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:
- 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
- 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 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:
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:
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:
- 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.
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:
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:
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> Network Name
<Virtual_SQL_Server_Name> SQL Server 7.0
<Virtual_SQL_Server_Name> SQL Server Agent 7.0
xyz Network Name
xyz SQL Server 7.0
xyz SQL Server Agent 7.0
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:
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:
- Do not change anything from the previous setup of master.
- Run this utility only from the primary node for SQL Server.
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:
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:
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:
LicensingFor information about licensing, see the following article in the Microsoft Knowledge Base:
Important cluster service administrative rulesWarning 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 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:
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:
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:
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:
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.
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 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:
Support for Microsoft Windows 2003Only SQL Server 2000 Service Pack 3 (SP3) or a later version is supported, as mentioned in the following Microsoft Knowledge Base article:
Article ID: 254321 - Last Review: Oct 14, 2013 - Revision: 1