Author: Joseph Sack
Technical Reviewer: Burzin Patel, Juergen Thomas, Lindsey Allen, Justin Erickson
SQL Server 2008 failover clustering introduces several supportability, reliability, and availability improvements. The following list details the more significant and immediate benefits of making the move to SQL Server 2008 Failover Clustering.
The installation process for SQL Server 2008 Failover Clusters has changed significantly. Essentially, you have two options for installation, integrated install or advanced/enterprise install. Integrated install involves the installation of a single-node SQL Server 2008 failover cluster instance. If you want the instance to be able to failover to other nodes, you follow a separate “add node” install for each node.
The advanced/enterprise install differs from the integrated install in that you prepare each node with SQL binaries and services, and then select the active node that owns the SQL Server shared disk, and then bring the SQL Server instance online in a separate step. The advanced/enterprise install is intended for third party enterprise deployment solutions (yet to hit the market), or adding the ability to prepare each node prior to configuring the Windows Cluster. You may decide to use the advance option if you prefer, but overall the integrated install option will provide less steps and will allow you to make the SQL Server instance available sooner.
From a “number of steps” perspective, the integrated install option requires less effort. For example, a two node cluster integrated install would require one “Install” step on the first node, and then an “Add node” step on the second node. An advanced/enterprise install would require a “prepare” operation on each node (two steps), followed with a third step, “completing” the SQL Server instance and bringing it online.
At first blush, this seems like more work for the DBA, so where is the benefit to this new process? Unlike with SQL Server 2005 failover clusters, SQL Server 2008 Failover Cluster installs do not involve remote-node operations. This translates to more discrete install steps on your part, but helps reduce several installation and patching problems that occur due to remote node permission issues, remote offline services, terminal services connections, and other communication issues that can leave you with a partial or failed installation. By moving to a SQL Server 2008 Failover Cluster, the reliability of your install will increase significantly by eliminating the several remote-node variables that once hampered a solid install of a SQL Server failover cluster.
Prior to SQL Server 2008, installing a service pack or cumulative update could require a several-minute outage for the SQL Server instance. This is due to the fact that in order to update a SQL Server instance to the latest Service Pack or Cumulative update, SQL Server services were stopped until the upgrade was completed. With SQL Server 2008 failover clustering, your outage period can be significantly reduced if you follow the proper “rolling update” process. Specifically, you can avoid prolonged outages of a SQL Server instance by applying Service Packs or Cumulative Updates against the passive nodes of a failover cluster. After applying the patches to the passive nodes, you can then failover the SQL Server instance to a newly upgraded node. Upon failover, the SQL Server instance is then upgraded. You can then proceed with updating the formerly active (now passive) nodes.
In my own testing of a two-node cluster hosting a single SQL Server 2008 failover cluster instance, I started off my patching process by installing a Cumulative Update on the passive node of the cluster. While this Cumulative Update was installed, the SQL Server instance remained up. After applying the cumulative update, I failed over the instance of SQL Server 2008 to the newly upgraded node, and then applied the cumulative update to the new passive node. The total down time for the upgrade was 15 seconds, which was the amount of time it took to fail over the SQL Server instance to the newly upgraded node.
As with SQL Server 2005, adding a new node for a SQL Server failover cluster instance or removing a node does not require an outage of the SQL Server instance. Like all cluster setup actions, AddNode needs to be run on the node to be added, as opposed to on the active node for 2005. This results in increased reliability since 2008 AddNode does not rely on remote task scheduling and execution. The only user inputs to the 2008 AddNode are: instance selection, service account passwords on the UI (service account names and passwords on the command line), Error and Usage Reporting options. All feature selection is retrieved from the existing instance where the current node is being added.
Also, in my own testing when adding a new node to a SQL Server failover cluster, I received the following notification during install:
“The current node TX147913-3 is at patch level [10.0.1600.22], which is lower than that of active node TX147913-2: patch level [10.0.1763.0]. After completing setup, you must download and apply the latest SQL Server 2008 service pack and/or patch and bring all nodes to the same version and patch level.”
This helpful warning let me know that I needed to update the newly added SQL Server failover cluster to match the existing and already-upgraded SQL Server failover cluster node. Patching the newly added passive node did not require a restart of the SQL Server 2008 failover cluster.
A pain point for many DBAs was the introduced requirement in SQL Server 2005 Failover Clustering for using domain groups for SQL Server services. These domain groups were used to manage the permissions of the SQL Server service accounts; however they required that each domain group already contained the service accounts as members prior to install. Changing the domain group for a clustered service, although possible, was not a trivial procedure (see KB 915846, “Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster”).
If you are creating a new SQL Server 2008 failover cluster on Windows Server 2008, you can now bypass the use of domain groups by designating Service SIDs during the install. Service SID functionality was introduced in Windows Vista and Windows Server 2008, and allows the provisioning of ACLs to server resources and permissions directly to a Windows service. On the "Cluster Security Policy" dialog during install of a SQL Server failover cluster, you still have the option to use domain groups, however selecting "Use service SIDS" is the recommended choice for SQL Server 2008 on Windows Server 2008 and allows you to bypass provisioning of domain groups and associated service account membership additions prior to installation.
In addition to Service SIDs, running SQL Server 2008 on Windows Server 2008 provides other significant benefits. For example, Windows Server 2008 clustering removes the requirement for having all hardware in a cluster solution be listed in the Hardware Compatibility List (HCL). Finding and validating your exact cluster solution in the HCL was often a difficult task. For Windows Server 2008, you no longer need to validate your exact solution in the HCL. Instead, your Windows Server 2008 logo cluster solution must pass validation using the Windows Server 2008 Cluster Validation Tool. Prior to configuration of your cluster, you can use this tool to scan the server nodes and storage you plan on using for your cluster solution. The tool checks for any issues that may impact support of a Failover Cluster. Any blocking issues across the hardware, network components and configurations, storage resources, and Operating System configurations will be identified in a final report and will allow you to address issues prior to deployment.
Windows Server 2008 Failover Clustering also added new quorum options, moving from a single-point-of-failure to a consensus-based quorum model. Windows Server 2008 Failover Clustering also offers iSCSI disk support, up to 16-node clusters, and ipv6 internet layer protocol support.
SQL Server 2008 Failover cluster allows for the use of a configuration file used in conjunction with a command line setup. For example – the following command line execution initiates an integrated install of a single-node failover cluster, referencing a configuration file with the required command line options:
Setup.exe /q /ACTION=InstallFailoverCluster /Configurationfile="C:\temp\ConfigurationFile.ini"
What’s more, performing a non-command line install of SQL Server 2008 automatically generates a ConfigurationFile.ini which is saved to the following directory:
<drive letter>:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMMSS\ConfigurationFile.ini.
Please note that as of this writing, ConfigurationFile.ini does not automatically include the FAILOVERCLUSTERIPADDRESSES setup option – however this is easy to add manually. For example:
FAILOVERCLUSTERIPADDRESSES="IPv4;172.29.10.160;Cluster Network 1;255.255.248.0"
Using command-line setup in conjunction with a configuration file can help streamline your SQL Server 2008 failover cluster installation process, particularly for large enterprise environments.
Pingback from Six Failover Clustering Benefits Realized from Migrating to SQL Server 2008
Pingback from Secure Citrix Systems » Blog Archive » Six Failover Clustering Benefits Realized from Migrating to SQL …
Pingback from Log Buffer #124: a Carnival of the Vanities for DBAs
Pingback from Six Failover Clustering Benefits Realized from Migrating to SQL … | bestwebhostingservices.com
Good snapshot of SQL Server 2008 cluster benefits. If I may just ask, is there any technical reason why SQL Server 2008 cluster is not supported on Windows Server 2008 cluster configured with different subnet? I am very sorry to throw this wet-blanket out but am just curious.
Just saw your comment, and there is a connect request for this:
The product team response was as follows:
"We will add this support at the earliest possible opportunity. We realize this is a big ask for the geo-dispersed availability scenario. Thanks for the feedback. "
The Microsoft SQL Customer Advisory Team just published my 'Top X List' article - " Six Failover Clustering
I'd like to ask a question, will rolling updates be compatable with other clustering solutions? Or is this just available with windows clustering solutions?
I’m not aware of rolling upgrade support with other clustering solutions – yet. I don’t see why it wouldn’t be possible, but I’m not aware of any solutions at this point.
In the future if I run across this information, I'll post a follow-up here.
How do you do the rolling SQL SP update with Active-Active cluster configurations?
Microsoft is actually releasing a white paper in a couple of months that will describe this process. However in the meantime there is a KB that details the process in general support.microsoft.com/.../958734. There is some flexibility on how you perform this on multi-instance clusters (depending on the number of nodes and instances), and so long as you are not updating a SQL Server instance on the active node currently hosting it, you can minimize downtime.
There is plenty of material available to help you upgrade to SQL Server 2008. This blog is intended as
Pingback from Useful links for upgrading to SQL Server 2008 | ButtonForums
Good articles on clustering. We may setup a clustering for a test server soon. I will follow your recommendations. Thank you.