SQL Server 2012 Multi-Subnet Cluster Part 2

By:   |   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.

Failover Cluster Validation Wizard

In my case, I had to further configure the MPIO settings on my iSCSI storage before I got a successful test result.

Failover Cluster Validation Report

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.
    Failover Cluster Management
  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.
    Select Servers dialog box of the Create Cluster Wizard
  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.

    the Access Point for Administering the Cluster dialog box

     

  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.

    Confirmation for the Create Cluster Wizard

     

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

    Click Next.

    Summary screen for the Create Cluster Wizard

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.

Cluster Core Resources section of the Windows Failover Cluster

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.

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.

Heartbeat Parameter Default value
SameSubnetDelay 1000 (in milliseconds)
SameSubnetThreshold 5 heartbeats
CrossSubnetDelay 1000 (in milliseconds)
CrossSubnetThreshold 5 heartbeats

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 *

 

PowerShell code and output for the delay and threshold

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

 

PowerShell code for CrossSubnetDelay and CrossSubnetThreshold for the cluster

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.

Configuring the Cluster Quorum Settings

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
    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.
    Select Quorum Configuration dialog box
  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
    Configure File Share Witness dialog box
  5. In the Confirmation dialog box, verify that the file share configuration for the quorum/witness is correct. Click Next.
    verify that the file share configuration for the quorum/witness is correct
  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.

Cluster Core Resources section for the File Share Witness

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento 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


Comments For This Article




Wednesday, May 28, 2014 - 10:21:46 PM - bass_player Back To Top (31979)

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. 

 

Thursday, May 22, 2014 - 1:33:59 PM - SQL DBA Back To Top (30894)

Hello Edwin,

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, September 19, 2012 - 9:22:57 AM - bass_player Back To Top (19569)

Hi Dave,

Please see part 1 of this series of articles for the network diagram of the multi-subnet cluster

http://www.mssqltips.com/sqlservertip/2728/sql-server-2012-multisubnet-cluster/

Tuesday, September 18, 2012 - 6:00:36 PM - Dave Back To Top (19556)

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

Saturday, July 28, 2012 - 12:47:58 PM - bass_player Back To Top (18835)

Jeremy,

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 

Saturday, July 28, 2012 - 12:38:20 PM - bass_player Back To Top (18834)

João Gabriel

Your question is not related to SQL Server 2012 Multi-subnet clustering. I recommend asking your question on the

TechNet and MSDN forums to get relevant answers.

Although, I can respond by saying go to the SQL Server Reporting Services Report Manager site and assign permissions to users from there

http://msdn.microsoft.com/en-us/library/ms157147.aspx

Friday, July 27, 2012 - 9:42:12 AM - sreekanth bandarla Back To Top (18826)

Jeremy - Go for Starwindsoftware.com for setting up your Storage in your Lab. Personally, I feel that's the best software based SAN I've ever seen.

You can explore openfiler as well...(very complex to setup when compared to Starwind)

Thursday, July 26, 2012 - 3:33:24 PM - vamsi Back To Top (18817)

Can you please help us for the following.

 

The SQL instance in one of our clients server was installed in Deutsch language.

The Windows OS is in English, but the SQL Server is installed in “Deutsch” language which we are not aware of.

Activities or tasks interrupted, are as follows:

Login screen for SQL Server is different (language wise) so not able to understand.

Basic activities such as Backup, Restoration, Creating login etc., we are not able to understand the options.

Tool bar present in the SQL Server Management Studio is  also in Deutsch language.

Error message is also in Deutsch language, which will  make us unable to understand the issue. 

We would like to know if there is any way/packs/work around available for SQL to convert the SQL GUI options language from Deutsch to English.

 

Thursday, July 26, 2012 - 10:27:23 AM - Jeremy Back To Top (18815)

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?

Thursday, July 26, 2012 - 10:16:34 AM - Joćo Gabriel Mazuze Back To Top (18814)

Hi all,

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?

 

Kind regards 














get free sql tips
agree to terms