Multi-subnet availability group, with mixed MultiSubnetFailover support (Part 2)
Tags: sql, powershell, sqlserver
Published: 2020-10-09 18:32:29

---
title: Multi-subnet availability group, with mixed MultiSubnetFailover support (Part 2)
published: true
date: 2020-10-06 12:00:55 UTC
tags: sql,PowerShell,sqlserver
canonical_url: https://am2.co/2020/10/multi-subnet-registerallprovidersip-2/?utm_source=rss&utm_medium=rss&utm_campaign=multi-subnet-registerallprovidersip-2
---

In [yesterday’s post](https://am2.co/2020/10/multi-subnet-registerallprovidersip-1/), I showed how to configure an availability group (AG) to use the `RegisterAllProvidersIP=0` when you can’t get clients to connect using the `MultiSubnetFailover=true` connection string attribute.

I mentioned that you have to make some trade-offs when you set `RegisterAllProvidersIP=0`, and included this comparison:

| MultiSubnetFailover=true | RegisterAllProvidersIP=0 |
| --- | --- |
| ❌ Microsoft drivers support it, but not jTDS & other drivers. | ✅ IP changes are handled at the server, so all drivers can work with this config. |
|
| |
| ❌ Requires connection string changes from all clients. | ✅ No changes needed from clients |
|
| |
| ✅ During failover clients can reconnect immediately. | ❌ During failover clients will not be able to reconnect until the DNS changes propagate & TTL expires. |
|
| |

But….when if you can eat your cake and have it, too?

[![](http://am2.co/wp-content/uploads/AGListener01-b.jpg)](https://www.youtube.com/watch?v=6U0C3LOwEP0)  


In some cases, you’ll have some applications & clients that are not able to use `MultiSubnetFailover=true`, and other clients that can. Perhaps you’re working on updating a bunch of legacy Java apps to move from old jTDS drivers to the current Microsoft JDBC drivers that properly support `MultiSubnetFailover=true`. Parts of your codebase have been updated, and you want them to make use of the connection string attribute for fast cross-subnet failover. But other parts of your codebase are still being updated and rely on the `RegisterAllProvidersIP` cluster parameter to be false. Wouldn’t it be nice to have both?

## Why don’t we have [both](https://knowyourmeme.com/memes/why-not-both-why-dont-we-have-both)?!?

Sometimes, we want the best of both worlds. In this case, _we actually can_. The only cost we’ll incur is to use some extra IP addresses.

Yesterday’s example uses 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

### Add a second listener

The wizard in SSMS to create a new AG only allows for creating a single listener on your AG. But, you can easily add a second AG using T-SQL.

Yesterday, we converted the `AGListener01` listener to use `RegisterAllProvidersIP=0`, so that only one IP is online at a time, at the cost of a 5-minute DNS update when there is a failover. In my mind, the 5-minute TTL wait means that the AG listener is not _really_ highly available. Today, we can add a second listener to the same AG that provides that full HA:

```sql
ALTER AVAILABILITY GROUP AG01  
      ADD LISTENER 'AGListener01HA' ( 
            WITH IP ( ('10.10.10.139','255.255.255.0'),
                      ('10.10.20.139','255.255.255.0') 
                    ) , PORT = 50001 );   
GO  
```

Now we have a single AG (AG01), with two listeners (AGListener01, AGListener01HA). AGListener01 was modified to use `RegisterAllProvidersIP=0`, and AGListener01HA has the default settings, which require clients to specify `MultiSubnetFailover=true` as a connection string attribute.

We can verify the settings for these two listeners using the following PowerShell:

```powershell
Get-ClusterResource -Cluster WSFC01 -Name AGListener01,AGListener01HA | 
        Get-ClusterParameter -Name RegisterAllProvidersIP,HostRecordTTL
```
```text
Object          Name                   Value  Type
------          ----                   -----  ----
AGListener01    RegisterAllProvidersIP 0      UInt32
AGListener01    HostRecordTTL          300    UInt32
AGListener01HA  RegisterAllProvidersIP 1      UInt32
AGListener01HA  HostRecordTTL          1200   UInt32
```

Both listeners are part of the same AG, so they will fail over together, and both listeners will point to the proper primary replica. However, clients can choose which listener to connect to, based on the client requirements. As client applications update their drivers and connection strings, they can also update to point to the (more) highly available listener (`AGListener01HA`) so that they can take advantage of faster reconnection on failover.

The post [Multi-subnet availability group, with mixed MultiSubnetFailover support (Part 2)](https://am2.co/2020/10/multi-subnet-registerallprovidersip-2/) appeared first on [Andy M Mallon - AM²](https://am2.co).