Multi-subnet availability group, without MultiSubnetFailover support

As a DBA, we have the opportunity & responsibility to build highly available database platforms for use by applications & users. Even if you are a DBA at a small company supporting databases for only internal applications, we are usually still responsible for providing databases as a service for application teams. Sometimes, in order for us to provide the best service, we need application teams to do some work to make use of the super awesome thing we built. And sometimes, getting application teams to make that change isn’t easy, or isn’t fast, or isn’t even possible.

Such is often the case with multi-subnet availability groups.

Multi-subnet availability groups

When building a highly-available availability group (AG), we often need to straddle multiple subnets. The most common case where an AG spans multiple subnets is when an AG spans data centers or geographies–such as a primary data center, and a disaster recover (DR) data center. Another common case is when an AG spans more than one availability zone (AZ) within a data center. All the major cloud providers support AZs, and it is becoming more common for companies to build on prem data centers with AZ support. AZs create “shared nothing” zones where Zone A & Zone B have fully separate storage, network, and compute–which allows for teams to build platforms that are zone-redundant, so that even in dramatic failure situations (ie a SAN failure), the failure is limited to servers in that zone, and the redundant systems stay online in the other zone.

This type of zone-redundant high availability is great for uptime, but does introduce one wrinkle for connectivity.

MultiSubnetFailover=true

With a default configuration, multi-subnet AGs require that the clients connecting to them include MultiSubnetFailover=true as a connection string attribute. This attribute tells the driver to expect DNS to provide multiple IP addresses for the Listener name, and to try all of them to find the correct IP to connect to for that network name. Clients that do not specify this attribute will get multiple IPs and not know how to handle them properly–most drivers will pick up one of the returned IPs at random (or maybe just seemingly random), and try to connect to that. This can result in random (or seemingly random) connection failures when it picks the wrong IP.

However, not every client or application will support this connection string attributes. In my experience there are two extremely common reasons that you can’t use MultiSubnetFailover=true:

• Third party apps that don’t support it. Software vendors take all sorts of different approaches to connection strings, and sometimes it’s just not possible to edit anything but the server name. If your software vendor doesn’t support it, then all you can do is ask them to fix it in the future (and keep reading for the workaround).
• Legacy apps with a huge codebase. Even if all your software is home-grown, and you have full control of it, getting changes in can take time. In an environment of legacy apps, you might have dozens or hundreds of client-side apps, services, and containers that need to be updated to the proper drivers and then have connection strings modified. It’s all within your power, but it’s going to take time to accomplish.

RegisterAllProvidersIP=0

There is a configuration on the AG listener itself–the network name on the Windows cluster–that you can flip in order to change the way the listener registers IPs, so that only one IP is registered at a time. When the listener fails over to a different subnet, then the Windows cluster will re-register the listener with a different IP.

There is a trade off to be made here:

That third item is really the problem. In addition to TTL, most multi-subnet network configurations will also have some level of DNS propagation where each subnet has it’s own DNS server, so the changes also have to propagate across to that other subnet. If you have to wait for TTL to expire and for DNS propagation, you’re going to bump failover downtime from “a few seconds” to “a minute or more,” even if you set TTL relatively low.

Setting RegisterAllProvidersIP

This setting has to be set on the listener object on the Windows Server Failover Cluster(WSFC). This setting needs to be set via PowerShell, as it’s not exposed from the Failover Cluster Manager snap-in.

First, let’s just find & look at the setting to see it’s current value. In my example, I’ve not yet changed it, so it will have the default value of 1 (true) to register all IPs.

First off, let’s talk about example configuration. I have a WSFC with a single AG, and a single listener. The listener has two IPs, each in a different subnet:

• The Windows Cluster: WSFC01
• The Availability Group: AG01

• The AG Listener: AGListener01

  • 10.10.10.xxx subnet
  • 10.10.20.xxx subnet

I’ll step through the PowerShell slowly, assuming that most of us DBAs don’t play around with the FailoverClusters PowerShell module very often.

First, let’s take a look at the listener in DNS:

Resolve-DnsName AGListener01 

Name              Type   TTL   Section    IPAddress
----              ----   ---   -------    ---------
AGListener01      A      1200  Answer     10.10.10.138
AGListener01      A      1200  Answer     10.10.20.138

Notice both IPs are listed in DNS.

Next, we can see the list of all cluster resources using the Get-ClusterResource cmdlet:

Get-ClusterResouce -Cluster WSFC01

Name                         State   OwnerGroup     ResourceType
----                         -----   ----------     ------------
10.10.10.199                 Online  Cluster Group  IP Address
Cluster IP Address           Online  Cluster Group  IP Address
Cluster Name                 Online  Cluster Group  Network Name
AG01                         Online  AG01           SQL Server Availability Group
AG01_10.10.20.138            Offline AG01           IP Address
AGListener01                 Online  AG01           Network Name
File Share Witness           Online  Cluster Group  File Share Witness
IP Address 10.10.10.138      Online  AG01           IP Address

Notice that both IPs are listed–but the 10.10.20 IP is offline. It is offline, but still registered in DNS. The DNS registration allows for clients to know both IPs, and connect to whichever it finds online.

Out of all of the things returned by Get-ClusterResource, we only care about the objects that belong to the OwnerGroup for the AG. We can filter a bit further by using our AG name as a filter:

Get-ClusterResource -Cluster WSFC01 | Where OwnerGroup -eq AG01

Name                        State   OwnerGroup   ResourceType
----                        -----   ----------   ------------
AG01                        Online  AG01         SQL Server Availability Group
AG01_10.10.20.138           Offline AG01         IP Address
AGListener01                Online  AG01         Network Name
IP Address 10.10.10.138     Online  AG01         IP Address

These 4 items represent the resources that make up my AG: The AG itself, the listener (listed as “Network Name”), and the two IP addresses, each in different subnets.

The item that I want to check for the RegisterAllProvidersIP parameter is the listener (Network name) resource. If I pass the Cluster name & resource name to Get-ClusterResource, I can then pass that resource along the pipeline to Get-ClusterParameter which will list out all parameters for that one resource:

Get-ClusterResource -Cluster WSFC01 -Name AGListener01 | 
        Get-ClusterParameter 

Object        Name                   Value                            Type
------        ----                   -----                            ----
AGListener01  Name                   AGListener01                     String
AGListener01  DnsName                AGListener01                     String
AGListener01  Aliases                                                 String
AGListener01  RemapPipeNames         1                                UInt32
AGListener01  HostRecordTTL          1200                             UInt32
AGListener01  RegisterAllProvidersIP 1                                UInt32
AGListener01  PublishPTRRecords      0                                UInt32
AGListener01  ResourceData           {1, 0, 0, 0...}                  ByteArray
AGListener01  StatusNetBIOS          0                                UInt32
AGListener01  StatusDNS              0                                UInt32
AGListener01  StatusKerberos         0                                UInt32
AGListener01  CreatingDC             \\dc01.am2.co                    String
AGListener01  LastDNSUpdateTime      9/29/2020 3:57:12 PM             DateTime
AGListener01  ObjectGUID             abc1234def567890ab12cd3456ef7890 String
AGListener01  DnsSuffix              am2.co                           String
AGListener01  ADAware                1                                UInt32

And I can get the specific RegisterAllProvidersIP parameter by explicitly asking for it. I’m also curious about TTL, so lets get just these two parameters:

Get-ClusterResource -Cluster WSFC01 -Name AGListener01 | 
        Get-ClusterParameter -Name RegisterAllProvidersIP,HostRecordTTL

Object        Name                   Value  Type
------        ----                   -----  ----
AGListener01  RegisterAllProvidersIP 1      UInt32
AGListener01  HostRecordTTL          1200   UInt32

To change this sample script from the docs specify five commands to run:

Import-Module FailoverClusters  
Get-ClusterResource yourListenerName | 
        Set-ClusterParameter RegisterAllProvidersIP 0   
Get-ClusterResource yourListenerName | 
        Set-ClusterParameter HostRecordTTL 300  
Stop-ClusterResource yourListenerName  
Start-ClusterResource yourListenerName  
Start-Clustergroup yourListenerGroupName

Note that these commands are written in a way that they do not pass in the Cluster parameter, so as written, they would need to be run on a cluster node for the cluster.

PowerShell is more powerful when I can run it from my desktop without fussing with remoting into every server to run something locally on the cluster node, so let’s update this example to allow that (and to substitute our own cluster object names for our example):

Import-Module FailoverClusters  
Get-ClusterResource -Cluster WSFC01 -Name AGListener01 | 
        Set-ClusterParameter -Name RegisterAllProvidersIP 0   
Get-ClusterResource -Cluster WSFC01 -Name AGListener01 | 
        Set-ClusterParameter -Name HostRecordTTL 300  
Stop-ClusterResource -Cluster WSFC01 -Name AGListener01 
Start-ClusterResource -Cluster WSFC01 -Name AGListener01 
Start-Clustergroup -Cluster WSFC01 -Name AG01 

There are five steps outlined here:

1. Change the RegisterAllProvidersIP parameter.
2. Reduce the HostRecordTTL. The docs recommend 300 seconds (5 minutes), but you’ll need to determine the right value for your environment.
3. Take the listener offline.
4. Bring the listener back online.
5. Ensure the entire AG is online.

Be careful about running these commands in production, as those stop/start steps will be disruptive to users. It’s important to note that if you change these cluster parameters, they will not take effect until you offline & online the resource. The Windows Cluster service will continue to renew the DNS entry using the configuration from the last time the resource was brought online. Therefore you’ll need to briefly take the listener (but not the entire AG) offline, and back online for it to realize the changes & update DNS.

After we’ve run the Set commands, we can use the Get commands to confirm that it is now set properly:

Get-ClusterResource -Cluster WSFC01 -Name AGListener01 | 
        Get-ClusterParameter -Name RegisterAllProvidersIP,HostRecordTTL

Object        Name                   Value  Type
------        ----                   -----  ----
AGListener01  RegisterAllProvidersIP 0      UInt32
AGListener01  HostRecordTTL          300    UInt32

After it is changed and the old TTL is expired, you’ll be able to see the Lister has only a single IP address configured, and now shows the shorter TTL:

Resolve-DnsName AGListener01 

Name              Type   TTL   Section    IPAddress
----              ----   ---   -------    ---------
AGListener01      A      300   Answer     10.10.10.138

Now your clients that are not multi-subnet aware will be able to connect, at the cost of longer downtime during an AG failover. Tune in tomorrow for another post that will let you support legacy clients, and modern clients that can use MultiSubnetFailover=true, all on the same AG.

The post Multi-subnet availability group, without MultiSubnetFailover support appeared first on Andy M Mallon - AM².