SQL Server 2008 Failover Clustering

Here are some notes on “SQL Server 2008 Failover Clustering” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

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 https://msdn.microsoft.com/en-us/library/ms143511.aspx
  • 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 https://suppot.microsoft.com/kb/838612

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 https://msdn.microsoft.com/en-us/library/ms189910.aspx

Failure Detection

  • Node failure
  • Resource failure detected
  • LooksAlive – SQL Server status – default every 5 seconds
  • IsAlive – SELECT @@SERVERNAME – default every 60 seconds
  • See https://blogs.technet.com/rob/archive/2008/05/07/failover-clustering.aspx 
  • 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

Troubleshooting

  • 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 https://msdn.microsoft.com/en-us/library/ms189117.aspx

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 https://support.microsoft.com/KB/956893
  • See https://support.microsoft.com/KB/956893
  • 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 https://msdn.microsoft.com/en-us/library/ms144259.aspx 
  • 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 https://blogs.msdn.com/clustering/archive/2008/09/24/8962934.aspx

Combining with other features

Clustering vs. Mirroring

Class discussion - other topics

Related blog posts:

Comments

  • Last updated on