Thursday, June 4, 2009

Failover Clustering

Failover Clustering

SQL Server 2005

  • Multi-instance clusters – each instance requires its own resources
  • Multi-node – more nodes per configuration, active/active, active/passive, N+1 – plann carefully
  • Analysis Services clusterable
  • See
  • Integration Services – Can be clustered. Discussion - Where to store the packages?
  • Reporting services not clusterable  - Consider using network load balancing instead
  • Can rename a clustered instance
  • Majority Node Set
  • See

SQL Server 2008

  • Cluster Validation tool
  • Support for up to 16 nodes
  • New quorum model: File Server Witness, Node Majority
  • Edit subnet mask, DHCP, IPv6
  • GPT disks, support for >2TB partitions
  • Improved cluster setup, error reporting
  • Rolling upgrades, one node at a time
  • See

Failure Detection

  • Node failure
  • Resource failure detected
  • LooksAlive – SQL Server status – default every 5 seconds
  • IsAlive – SELECT @@SERVERNAME – default every 60 seconds
  • See 
  • Upon failover – new instance comes up on other node
  • SQL Server 2005+ – Enterprise Edition – service available after Redo phase completes
  • Failback – Available using use preferred owner


  • Verify failover
  • Careful – Post-install tasks like manual IP ports, additional disks – Check resources
  • Logs: Windows Event Logs, Cluster log, SQL Server Setup log
  • Books Online: Failover Cluster Troubleshooting
  • See

Clustering Details

  • Client design: To the app, failover process looks like server taking a long while to respond. Consider adding retry logic.
  • Planning: Verify hardware solution, run validation, plan security for service accounts, plan SQL Tools location
  • Migration: Verify OS settings (CSP, Kerberos), MSDTC, pre-requisites
  • Heartbeat: TCP/IP configuration, binding order, remove NETBIOS
  • Virtualization: Check support policy (KB below), guest failover not supported, SVVP
  • See
  • See
  • Adding a node: Set SQL setup, select add cluster node
  • Removing a node: Set SQL setup, select remove cluster node
  • SQL Server setup can be fully scripted, including cluster install, add cluster node, etc.
  • Example: SETUP.EXE /q /ACTION=InstallFailoverCluster /INSTANCENAME=...
  • Example: SETUP.EXE /q /ACTION=AddNode /INSTANCENAME=...
  • See 
  • Careful – SQL Server 2008 cluster setup is now run one node at a time
  • Cluster log: Use CLUSTER.EXE command line to obtain text log
  • See

Combining with other features

Clustering vs. Mirroring



Post a Comment