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

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. Use the autorestart feature. Use run-when-idle jobs.
SQL Server	Member of local Power Users or local Administrators group	Administer the Cluster. Add and delete SQL Server objects in the Windows 2000 Active Directory.

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: 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:

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: 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: 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:

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 your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

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: 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:

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: 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: 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:

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:

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: When you cluster SQL Server, you may break SQL Server replication; for additional details, see the following article in the Microsoft Knowledge Base:

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, 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:

Licensing

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

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 or add new software or hardware components to your system after you install SQL Server Service Pack 3, you must reinstall Service Pack 3 or your current Windows NT service pack again.
• 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:

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:

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:

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:
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: Please perform troubleshooting of virtual SQL Server issues in the following order:

1. 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?
2. Operating System - Do the event logs show any kind of operating system problems, service failures, or driver problem?
3. Networking - Do the event logs show loss of connectivity? NIC failures? DNS problems?
4. Security - Check for access denied errors, security log failures.
5. MSCS - Does the cluster report problems in any of the event logs?
6. 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:

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: