What you need for a Multi Subnet Configuration for AlwaysOn FCI in SQL Server 2012


By:   |   Updated: 2014-06-13   |   Comments (4)   |   Related: More > Clustering

Problem

You have a new project that requires a primary database server in one building and secondary database server in another building to be configured as a Failover Cluster. With this setup the participating nodes will have different subnets. You propose to use the SQL Server 2012 AlwaysOn feature and you are well aware that multiple subnets are supported by the AlwaysOn feature.  You may have already setup AlwaysOn in SQL Server 2012, but what needs to be done across multiple subnets? What are the important points to consider for this scenario?

Solution

One of the enhancements to AlwaysOn FCI is the support for multi subnet cluster setup. The ability to implement a multi-subnet FCI has always been included in older versions of SQL Server, but it required a virtual local area network (VLAN). SQL Server 2012 removes that requirement, enabling multi-subnet FCI to be more commonly adopted as a high availability and disaster recovery solution.

In this tip, I share several points to consider when implementing this solution.

SQL Edition

You will need SQL Server 2012 Enterprise Edition for a multi subnet cluster setup.

Storage

Multi subnet FCI will require storage level replication to maintain a copy of the database at the disaster recovery site. Though you have separate copies of the database file at each site, to SQL Server this looks like a shared storage solution.

When choosing storage replication technology, you must ensure that it meets the requirements for SQL Server storage and that the database and logs are synchronously and consistently replicated. The key concern here is that it must adhere to the write ordering requirements of SQL Server and to make sure that the data is consistent even in the event of network failure.

File Share Witness

The purpose of the FSW is to have something else that can count as a vote in situations where the number of configured nodes isn't quite enough for determining a quorum. A FSW is more likely to be used in multi-site clusters or where there is no common storage. A FSW does not store cluster configuration data like a disk. It does, however, contain information about which version of the cluster configuration database is most recent. Other than that, the FSW is just a share.

When choosing that FSW location, it should be outside of the cluster, not on either node participating in the cluster. The location must also not be found in the SAN. This actually happened with me during my setup. I realized that the path created by our SAN team could not resolve when I continued with my AlwaysOn configuration, so I asked for a shared path folder instead.

IP Addresses

SQL Server 2012 now supports failover cluster nodes residing on different subnets. The introduction of "OR" logic allows the use of 2 IP addresses. This means that IP addresses can reside in different subnets, practically eliminating the need for a VLAN.

From the diagram below, on each side of your subnet, you will need to request the following from your Domain or Network Administrator:

  • Cluster IP for each node
  • Cluster name for each node
  • Availability Group IP for each node
  • Availability Group name
SQL Server 2012 now supports failover cluster nodes residing on different subnets.

Now, in order for SQL Server 2012 to deliver very high availability, these IP addresses will be registered with DNS by default.

Although you are using multiple subnets, the nodes participating in your cluster must still reside in the same domain.

Network

If you are doing the setup of AlwaysOn in a multi site cluster, make sure to tune the heartbeat network traffic.

The heartbeat frequency, also called subnet delay, is once every second regardless of the subnet configuration. By default, if a node misses a series of 5 heartbeats, another node initiates a failover. The range for this value, called subnet threshold, is 3 to 10.

Determine the value for the Time-to-Live (TTL) setting in the DNS. By default this value is set to 20 minutes (1200 seconds). For SQL Server 2012, we recommend that this value be changed to 60 seconds. You may run the cluster command below to do this:

cluster /cluster: ClusterName res "NetworkNameResource" /priv HostRecordTTL=60

If you are not familiar with your Network Name Resource, like me, you can use this command:

cluster res

It should display all the network resource available to you. Look for the SQL Network Name value and that should be it.

Use this command to check the new value of your new TTL setting:

cluster /cluster:<ClusterName> res “NetworkNameResource” /priv

Another setting to update is the DNS replication delay. This value is set to 180 minutes by default. You will also need to set the replication frequency to at least 15 minutes.

In the event of fail over to another subnet, the online IP address changes accordingly. The Windows Server failover cluster issues a DNS update as soon as the network name resource comes online. But these changes do not take effect immediately. This is because of the local DNS cache on the client machines and also because of the DNS replication latency if the cluster node and the client machines are not using the same DNS server. Therefore, the client machines cannot resolve the network name, and until the DNS replication is complete and the local DNS cache times out causing connection failures.

Next Steps


Last Updated: 2014-06-13


get scripts

next tip button



About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, March 01, 2018 - 2:33:26 AM - Alaa Barqawi Back To Top

hi Carla

can i have 2 nodes normal SQL failover cluster on the 1st site then availability group between site A and Site B?


Thursday, June 26, 2014 - 8:02:04 AM - Carla Abanes Back To Top

Hi Steve,

Thanks for reading!

On the windows level, you will need the cluster IP and name for each node. During the failover, although the cluster service keeps the same network name, that name will be routed to the other IP address. And before the applications realizes that a failover happemed, the DNS servers must update each other with this new IP address. During your setup this DNS record is entered into manually. 


Wednesday, June 25, 2014 - 10:47:46 AM - steve Back To Top

Great post. I am configuring a 2 node cluster for always on. each server in different subnet.

since you are saying get a clustername for each node, is there a field in cluster creation dialogue that accepts the 2 different cluster names? And does it create the DNS records automatically?  I thought I would have one cluster name and it would point to 2 IPs (each in different subnet)... or is that only possible for AG listener?


Friday, June 13, 2014 - 10:42:24 AM - bass_player Back To Top

Great tip, Carla. It's also great to see you here at MSSQLTips.

A couple of things to add here: PowerShell is the de facto way of managing servers moving forward. Cluster.exe commands have corresponding PowerShell cmdlets starting with Windows Server 2008 R2. As for DNS, assuming that it is Active Directory (AD)-integrated, the replication is dictated by AD replication be default unless manually configured otherwise. AD replication is dictated by how you segment your AD sites logically thru Active Directory Sites and Services. There are cases when the logical topology of the network in AD does not reflect the physical topology. Unfortunately, these are things that are outside the scope of the typical SQL Server DBA but it is worth knowing how this affects SQL Server high availability and disaster recovery



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools