SQL Server 2005 Clustering Best Practices
In this section, I summarize all of the best practices you should follow if you want to install, configure, and maintain a solid SQL Server 2005 cluster. Think of this as a cluster checklist to success.
Before You Begin
- Planning in detail is essential!
- You should spend more time planning than you do in the actual implementation of SQL Server Clustering.
- Don’t wing it!
- Identify all the potential risks you could encounter as the result of using a cluster.
- Remember, a cluster is only a small part of a larger business continuity plan or disaster recovery plan.
- Plan how to deal with all identified risks.
General Best Practices
- A two-node Active/Passive configuration is preferred over an Active/Active configuration.
- If you build a 3-node or larger cluster, one node should be dedicated to failover, while the rest of the nodes run a single instance of SQL Server.
- Running multiple instances of SQL Server on the same node is not recommended.
- SQL Server clusters should be dedicated to SQL Server. Don’t run other software on these nodes.
- Cluster nodes should be member servers, not domain controllers.
- Cluster nodes must be all in the same domain and have access to multiple domain controllers.
- Before you begin to install Cluster Services or SQL Server Clustering, determine your virtual names and IP addresses.
- After each major step, check all logs to look for potential problems, before moving onto the next step.
- Before installing SQL Server Clustering, ensure that the Windows Cryptographic Service Provider and the Task Scheduler are on.
- Once Clustering Services and SQL Server Clustering have been installed, give the domain controllers some time to replicate the virtual server names before clients try to access them.
Hardware Best Practices
- All cluster hardware must be on the Microsoft Windows 2003 Clustering HCL.
- Server and array must be approved to work together.
- Each cluster node should have identical hardware, firmware, drivers, software, etc.
- Ensure that all firmware and drivers are the most current and are approved for clustering.
- Ensure that each node’s local drive is named the same, e.g. “c,” and is mirrored.
- Be sure you consider future disk space needs when selecting the amount of disk space to be in the shared array. It is much easier to add it now, than later.
- SQL Server clustering is not designed to protect data. Shared arrays must incorporate their own fault tolerant technology. RAID 10 is preferred for best protection and performance.
- Consider options for storing data off site using mirroring or log shipping.
- If you are using a Smart UPS for any node of the cluster, remove it before installing Cluster Services, and then re-add it.
- If your server nodes have AMP/ACPI power saving features, turn them off. This includes network cards, drives, etc. Their activation can cause a failover.
Software Best Practices
- Use Windows 2003 Enterprise or Data Center.
- Use latest OS service pack.
- Test service pack and patches before adding them to a cluster.
Configuration Best Practices
- Each node of the OS cluster should have identical OS and software configuration settings.
- Don’t locate the paging file on the shared array.
Clustering Hardware Best Practices
- If you are using extended memory in your cluster nodes, each node must have the exact same amount of RAM, and it must be configured exactly the same on all nodes.
- Even if you are not using extended memory, it is a good idea to have the same amount of RAM in each node.
- Use two separate (virtual or physical) networks, one each for the public and private links. The public and private networks should be on separate subnets.
- The nodes of a cluster may be on different physical networks, and if they are, both the private and public network connections must appear as a single, non-routed LAN using a VLAN.
- Label the network connections, from within the OS, as Public and Private for each node in the cluster. Use the same names for each node.
- Use a separate network card for the Public and Private network connections. Don’t use multi-port NICs.
- NICs must use identical drivers and configurations.
- Each NIC’s speed and duplex should be manually set.
- To keep the Private network as fault tolerant as possible, use a crossover cable in a 2-node cluster; or a VLAN or hub for a larger cluster.
- Use static IP addresses for both the Public and Private networks.
- For the Private network, use a private class A, B, or C address, not a public address.
- For the Public network cards, configure at least two DNS servers (for fault tolerance).
- These DNS servers must support dynamic updates; otherwise, failover may not work.
- Do not use IPSec for clustered applications; it is not cluster-aware.