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 http://sqlblog.com/blogs/greg_low/ and http://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

• High availability option, standby instance on a different node
• In case of failover: active transactions are rolled back, connections retry, reconnect to other node
• Single copy of databases in shared storage
• Built on top of Windows Server Failover Clustering – up to 8 nodes in SQL 2005, 16 in SQL 2008
• SQL Server 2005 – Requires certified hardware
• See http://support.microsoft.com/kb/309395
• SQL Server 2008 – Run Failover Clustering Validation tool
• http://support.microsoft.com/kb/943984 
• Discussion – Where to keep tempdb? In a cluster disk!
• Cluster Group (a.k.a. Cluster Service or Application) – Collection of required resources
• See http://msdn.microsoft.com/en-us/library/ms189134.aspx
• See http://support.microsoft.com/kb/327518/

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

Combining with other features

• Clustering and File Stream - Supported
• See http://msdn.microsoft.com/en-us/library/bb895334.aspx
• Clustering and Full Text – Supported
• Clustering and Replication - Supported
• See http://msdn.microsoft.com/en-us/library/ms143786.aspx

Clustering vs. Mirroring

• Clustering – entire instance, Mirroring – one database at a time
• For 2005, avoid mirroring large number of databases
• See http://msdn.microsoft.com/en-us/library/cc917681.aspx
• For 2008, with log stream compression, can probably handle more…
• See http://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx
• Mirroring with large databases: Initial backup/restore can be problematic

Class discussion - other topics

• Upgrade vs. Migrate
• See http://blogs.msdn.com/clustering/archive/2008/08/28/8904281.aspx
• DTC
• See http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx
• See http://technet.microsoft.com/en-us/library/cc730992.aspx
• NIC Teaming
• Mount points
• iSCSI
• Multiple Instances
• Geo Clustering

Related blog posts:

• http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-database-mirroring.aspx