SQL Server 2005 Clustering Best Practices

Disk Array

• Use fiber channel arrays over SCSI.
• When using fiber-attached storage, use two fiber cards (HBAs) in each node, with each one connecting to a different switch.
• If using SAN zoning, hardware zoning is preferred over software zoning.
• Be sure the shared array is successfully running before installing clustering.
• Be sure the shared array and HBAs have the latest firmware updates and drivers.
• Arrays must be set up as Basic disks, not Dynamic.
• Before installing clustering services, be sure that only one node of the cluster is on at a time.
• One at a time, each node of the cluster must be made to see the shared array.
• Use Disk Administrator to recognize and format an array. An array only has to be formatted once.
• Drives must be formatted NTFS, not compressed.
• All drives on all nodes of the cluster must access the shared array using the same drive letters.
• Name the Quorum drive "Q" for ease of identification.
• Generally speaking, set the size of the logical drives to match the physical size of the drives. Fewer logical volumes contribute to higher performance.
• The Quorum drive would be an exception to the above.

Quorum Drive

• Should be on its own logical drive, dedicated for this purpose.
• Quorum drive should be fault tolerant.
• Do not use a single node cluster quorum.
• Recommended minimum size is 500MB, which is the minimum recommended size for an efficient NTFS partition.

Infrastructure Protection

• An expensive cluster is not of much value if there are weak links in the infrastructure that supports the cluster, such as backup power, routers/switches, domain controllers, DSN servers, and so on.
• If the infrastructure is not fault tolerant, then using a cluster is not buying you much.

Installing Cluster Service

• After the Clustering Wizard analyzes your configuration, review all alerts and take any necessary action.
• Virtual server names must be unique and 15 characters or less.
• Choose a "Typical" installation. Generally, only choose "Advanced" if you have fiber channel switched fabric with multiple switches, or similar complex configurations.

Testing the Cluster Service

• Once the cluster is built, test it extensively before it goes into production. Test for the following:
  
  • Group moves.
  • Initiate manual failover.
  • Turn each node off.
  • Disconnect network connections from each node.
  • Disconnect shared array (this sometimes can be interesting) connection from each node.
• Consider stress testing your disk array for several days using SQLIOTest to help identify potential array issues.
• Ideally, before installing new Service Packs or patches, test on a test cluster before moving into production.

Before Installing SQL Server 2005 Clustering

• Ensure that each node works as expected and that the OS clustering works as expected. Check all logs and fix any problems before you install SQL Server Clustering.
• Select a node of the cluster to be your active node. This can be any node, but it is good to do this for your frame of reference, and it prevents potential confusion.
• Do not install applications into the default cluster group.
• Be sure that you have created a cluster resource group using Cluster Administrator before installing SQL Server Clustering.
• In addition, be sure that all of the shared array drives that will be used by SQL Server are in this group.

Installing SQL Server Clustering

• Check logs after each major step of the installation, looking for potential errors.
• When the Clustering Wizard analyzes your configuration, be sure to carefully read all alerts and be sure that they do not apply to you. If they do, then correct them before continuing the installation.

After Installing SQL Server Clustering

Once the SQL Server cluster is built, test it extensively before it goes into production. Test for the following:

• Group moves.
• Initiate manual failover.
• Turn each node off.
• Disconnect network connections from each node.
• Disconnect shared array (this sometimes can be interesting) connection from each node.
• Use your SQL Server application (or Enterprise Manger) to act as a client to see if you can still access SQL Server after each failover.
• Ideally, before installing new Service Packs or patches, test on a test cluster before moving into production.

Upgrading Best Practices

• Ideally, don't upgrade in place. Instead, create a brand new cluster on new hardware and move the SQL Server databases from the old cluster to the new.
• If you have to upgrade in place, perform a rolling upgrade and use the SQL Server 2005 Upgrade Advisor.
• Have a carefully thought-out back out plan should the upgrade fail.