In a previous tip on Install SQL Server 2008 on a Windows Server 2008 Cluster Part 1, we have seen how to install and configure a SQL Server 2008 on a Windows Server 2008 Failover Cluster. We now have a new requirement to deploy a SQL Server 2012 instance on a multi-subnet cluster that spans different geographical locations. How do I install and configure a SQL Server 2012 on a multi-subnet cluster?
To continue this series on Installing SQL Server 2012 on a Multi-Subnet Cluster, we will look at building our Windows Server 2008 R2 cluster in preparation for SQL Server 2012. In Part 1, we have configured the storage in both of the servers that we will be using as part of our cluster. Now, that doesn't mean we're done with the storage part. Remember that this is a key component in setting up your multi-subnet cluster so we will make modifications as necessary prior to installing SQL Server 2012. Your storage engineers and vendors will be able to assist you with making the necessary configuration to make the storage suitable for a multi-subnet cluster.
This tip will walk you through the creation of the Windows Server 2008 R2 Multi-Subnet Cluster. It is assumed at this point that you have installed the Failover Clustering feature together with the .NET Framework 3.5.1 using Server Manager. If you haven't done so, check out this tipfor reference.
Running the Failover Cluster Validation Wizard
When you run the Failover Cluster Validation Wizard, make sure you select all of the tests and don't skip any items, specifically the storage tests. This is highly recommended because the Failover Cluster Validation Wizard will tell you whether or not you still need to make modifications on your storage subsystem.
In my case, I had to further configure the MPIO settings on my iSCSI storage before I got a successful test result.
Creating the Windows Server 2008 R2 Multi-Subnet Cluster
Once you get a successful test result on the Failover Cluster Wizard, you are now ready to create your Windows Server 2008 R2 Multi-Subnet Cluster. Make sure that you already have the virtual server name and virtual IP addresses that you will assign for the Windows Failover Cluster. Now, you might be wondering why I said IP addresses (plural, not singular). That wasn't a typographical error. Since you are dealing with multiple subnets for this cluster, you need to have a corresponding virtual IP address per subnet for the virtual server name. The number of virtual IP address will depend on the number of subnets you will be using for your failover cluster. In this example, since I am only dealing with two subnets, I only need two virtual IP addresses for every virtual server name - including the SQL Server cluster resource which we will see later on when we install SQL Server 2012 on this cluster.
To run the Create a Cluster Wizard:
Open the Failover Cluster Management console.
Under the Management section, click the Create a Cluster link. This will run the Create Cluster Wizard.
In the Select Servers dialog box, enter the hostnames of the nodes that you want to add as members of your cluster and click Next.
In the Access Point for Administering the Clusterdialog box, enter the virtual hostname and IP addresses that you will use to administer the cluster. As I've previously mentioned, notice that you now have two sections for the virtual IP address - one for each subnet. The wizard is smart enough to detect that you are trying to create a multi-subnet cluster. This is possible because of the implementation of the OR logic in the cluster resource dependency (more on this in a later section). For this example, I will use the following information:
Virtual Server Name
In the Confirmation dialog box, click Next. This will configure Failover Clustering on both nodes of the cluster, add DNS and Active Directory entries for the cluster virtual server name.
In the Summarydialog box, verify that the report returns successful results.
That was it. You now have a working Windows Server 2008 R2 multi-subnet cluster. One thing that you'll notice in the report is that it automatically configured the quorum to use node and disk majority. This is the default configuration for a cluster with an even number of nodes and accessible shared storage. While you can use node and disk majority quorum configuration for a multi-subnet cluster, it doesn't make sense to replicate the storage between subnets just for this purpose. A recommended quorum configuration for this setup is to use the node and file share majority. A detailed explanation of quorum configuration can be found in this Microsoft TechNet article. We will configure the cluster quorum settings in a while but before we do, let's take a look at what other cluster and network configuration we need to set prior to installing SQL Server 2012.
Understanding the OR Logic in Dependencies
As I mentioned in the previous tip, multi-subnet clustering has been available ever since Windows Server 2008. This is because it implements the OR logic in dependencies for cluster resources. In the past, cluster resource dependencies implement an AND logic. This means that if a cluster resource X is dependent on resources A and B, both of the cluster resources need to be online in order for cluster resource X to be online. If either A or B is offline, cluster resource X will not go online. With the implementation of the OR logic dependency, the cluster resource X can now be brought online even when one of the dependency resources is not.
To better understand this concept, let's look at the Cluster Core Resources section of the Windows Failover Cluster. Expand the virtual server name to see the two virtual IP addresses that we have assigned.
Notice how the virtual server name WINMULTISUBCLUS is online even when the virtual IP address 192.168.0.112is offline. If you check the properties of the virtual server name, you will see that the cluster has automatically defined this OR logic dependency for us when we were creating the cluster.
This OR logic dependency on the virtual IP address is what makes it possible for the cluster to go online on any of the subnets when failover happens. We will see this again in action when we install SQL Server 2012 Failover Cluster.
Tuning Cluster Heartbeat Settings
In a multi-subnet cluster, we need to test network latency to make sure that the nodes do communicate with each other. The communication between cluster nodes, more commonly known as the "heartbeat", needs to be properly configured for the cluster to work efficiently. Inefficient communication between cluster nodes may trigger a false failover, thus, it is necessary to properly tune the heartbeat settings.
There are two major settings that affect heartbeat. First, the frequency at which the nodes send signals to the other nodes in the cluster (subnet delays) and second, the number of heartbeats that a node can miss before the cluster initiates a failover (subnet threshold). In a single-subnet cluster, we barely made modifications to these settings because the delay (about 250 to 2000 milliseconds) and threshold values are tolerable enough for the cluster to handle without initiating a false failover. However, in a multi-subnet cluster, when the cluster nodes are too far away from each other, the communication may take longer and could possibly miss heartbeats. The table below outlines the default values for cluster subnet delays and thresholds.
1000 (in milliseconds)
1000 (in milliseconds)
We can view the cluster parameters to see their default values. Note, that while we can still use the cluster.exe command, I will use the FailoverClusters module in Windows PowerShell.
PS C:\> Get-Cluster | Format-List *
This simply means that, by default, a heartbeat is sent every 1 second to all of the nodes in the cluster - both single subnet and multi-subnet. If 5 heartbeats are missed, the node is considered down and failover is initiated. We can change these values based on the performance of our network infrastructure. A simple PING test can be done between nodes of a multi-subnet cluster to get an idea of response times between nodes. For this example, let's say we will configure our cross subnet delay value to 3 seconds instead of 1 and a threshold value of 7 instead of 5.
This now changes the behavior of the cluster heartbeat to be more tolerable across multiple subnets.
Configuring the Cluster Quorum Settings
As previously mentioned, the cluster quorum settings that the wizard selected for us used the Node and Disk Majority option. Since it doesn't make sense for us to replicate the cluster storage just for this purpose, we will configure the cluster quorum settings to use Node and File Share Majority. This also gives us the benefit of allocating the cluster storage for other cluster resources. This is Microsoft's recommendation for multi-subnet clusters with even number of nodes. Before we change the configuration, we need to create a file share and grant the Windows Failover Cluster virtual server name Read/Writepermissions to it.
The placement of the file share witness is a bit of a debate as well. While the ideal case is to place it in a different geographic location than the cluster nodes, sometimes cost dictates otherwise. Microsoft Clustering MVP David Bermingham wroteabout the different options on where to place the file share witness. I'm all for this recommendation, but if cost is preventing us from doing so, you can place it on the production site provided that you constantly monitor the file share witness and host it in a highly available file server.
To configure the quorum in a failover cluster:
Open the Failover Cluster Management console
Select the name of the cluster you have just created. Right-click on the cluster, select More Actions, and click Configure Cluster Quorum Settings... This will open up the Configure Cluster Quorum Wizard
In the Select Quorum Configuration dialog box, select the Node and File Share Majority (for clusters with special configuration) option. Click Next.
In the Configure File Share Witness dialog box, provide the file share location that you want your cluster to use as the quorum/witness. In my example, AD2 is a domain controller in another location that is different from my cluster nodes. Click Next
In the Confirmation dialog box, verify that the file share configuration for the quorum/witness is correct. Click Next.
In the Summary dialog box, verify that the entire configuration is successful.
You can verify that the cluster quorum setting is now configured to use the file share witness by looking at the Cluster Core Resourcessection.
In this tip, we have created a Windows Server 2008 multi-subnet cluster and configured the heartbeat and quorum settings. Make sure you perform validation testing to make sure that the cluster is working as expected. In the next tip, we will proceed to install SQL Server 2012 in our fully working multi-subnet cluster.
Start working on building your test environment in preparation for setting up a SQL Server 2012 multi-subnet cluster on Windows Server 2008 R2. This is a great opportunity to learn more about networking concepts and fundamentals as a SQL Server DBA.
Last Update: 7/26/2012
About the author
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.
I need a help, i have developed reports in BIDS 2008 and uploaded them into a report server, everything is working fine on the local machine, i need to allow another users in our local network to browse thos reports. How do i proceed?
I'd like to create a VM lab to simulate a multi-subnet environment so I can do a clustered install like this, but I'm really unfamiliar with the storage setup. Are you aware of any software emulations we could use to fake out the cluster so that it thinks we have a real storage subsystem?
I used StarWind Software to write these series of articles. There are other products out there that can help you simulate replication on the storage level but some of them are not software-based and are not cheap. I'm still looking for other options so I can try them out but, as of now, I'm very happy with StarWind for testing purposes since I do a lot of presentations
Hi Edwin, Could you please explain this statement in more details---SQL Server 2012 instance on a multi-subnet cluster that spans different geographical locations? What i understand in different geographical locations or datacenters most of times they have their OWN subnet or multi-subnets. I don't know whether the way you configure cluster will work or not. Please expand the networks and show us how each node network subnet was setup. thanks Dave
Wednesday, September 19, 2012 - 9:22:57 AM - bass_player
The company I work for uses Log shipping for DR and SQL clustering/Mirroring for high availability. Our DR site is located in Central zone whereas out production data center is in pacific zone. We have some databases that need HA and DR so we had to set up “Log shipping & Mirroring” or “Log shipping & Clustering”. This sometimes increases the maintenance overhead especially with mirroring and the databases getting failover. Now with the Multi subnet cluster support that SQL 2012 is offering and our databases getting migrated to 2012 & 2014, I was thinking this will be a very good option for us as we get both HA & DR as one technology. But before implementing this I wanted to know what type of challenges once can face with Multi subnet clusters.
1.I checked with my storage team and they confirmed we can set up storage replication but at time when the maintenance is being done we might build up latency. This concerns me with multi subnet cluster. If anything such happens do you think it will be an easy recovery?
2.Also if there will be an network outage due to maintenance or unplanned down time, how would this go with Multi subnet cluster? When the network is bought online does everything catch up itself?
3.I also wanted to know the network sensitivity that multi subnet might have with cluster. I don’t want the cluster to failover for minor millisecond/second outages. I guess we can set this property in the cluster settings?
4.I see this post almost 2 yrs old so I am predicting you might have had good hands on experience on Multi subnet cluster. What would be your opinion in terms of managing the multi subnet clusters post implementation and during the outages? And would you be able to recommend a good book for SQL 2012 clustering?
Wednesday, May 28, 2014 - 10:21:46 PM - bass_player
Before I dive into more detail on multi-subnet clusters, I would recommend defining your recovery objectives and service level agreements. These definitions should be the starting point of any technical decision made before even jumping into the implementation details. Now, if you've decided to pursue multi-subnet clustering, you need to test your network latency and see if a maintenance will cause you to miss your recovery objectives and service level agreements. For example, let's say your RPO/RTO is 1 hour. If you need to perform maintenance on your switches in less than 20 minutes, would your storage replication be able to catch up in less than 40 minutes? If not, you either need to upgrade your network, your storage replication implementation or update your recovery objective. It would be hard to simply implement a multi-subnet cluster without knowing your recovery objectives and service level agreements. I simply provided numbers to put the implementation details in perspective and in relation to your requirements. I would recommend taking a look at this online course that I created. The first 5 modules were made available to anybody because the principles form the foundation of every high availability and disaster recovery project.
Now, would network glitches affect your geographically-dispersed cluster? Absolutely. This is why you need to change your CrossSubnetDelay and CrossSubnetThreshold parameters. You also need to configure the proper quorum settings and decide the appropriate location for your quorum - whether you decide to use shared storage or a file share witness.