SQL Server 2012 Multi-Subnet Cluster Part 2

By: Edwin Sarmiento   |   Updated: 2012-07-26   |   Comments (10)   |   Related: 1 | 2 | 3 | 4 | > Clustering

Problem

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?

Solution

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:

1. Open the Failover Cluster Management console.
2. Under the Management section, click the Create a Cluster link. This will run the Create Cluster Wizard.
   
3. 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.
   
4. 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	Networks	IP Address
   WINMULTISUBCLUS	172.16.0.0/24	172.16.0.112
   	192.168.0.0/24	192.168.0.112

   Click Next.

   

    

5. 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.

   Click Next.

   

    

6. In the Summarydialog box, verify that the report returns successful results.

   Click Next.

   

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.

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.

PS C:\> $clust = Get-Cluster; $clust.CrossSubnetDelay = 3000; $clust.CrossSubnetThreshold = 7

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:

1. Open the Failover Cluster Management console
2. 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
   
3. In the Select Quorum Configuration dialog box, select the Node and File Share Majority (for clusters with special configuration) option. Click Next.
   
4. 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
   
5. In the Confirmation dialog box, verify that the file share configuration for the quorum/witness is correct. Click Next.
   
6. 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.

Next Steps

• Review the previous tips on Install SQL Server 2008 on a Windows Server 2008 Cluster Part 1, Part 2, Part 3 and Part 4.
• Download and install an Evaluation copy of Windows Server 2008 R2 for this tip and SQL Server 2012in preparation for the next one.
• 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.

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips