In yesterday’s post, 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:
|❌ 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?
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?!?
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:
- The Availability Group:
- The AG Listener:
- 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.
Creating a listener in T-SQL looks like this:
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 = 1433); GO
And in PowerShell it looks like this:
New-SqlAvailabilityGroupListener -Name AGListener01HA ` -Path SQLSERVER:\Sql\MyComputerName\MyInstanceName\AvailabilityGroups\AG01 ` -StaticIp '10.10.10.139/255.255.255.0' ` -Port 1433 Add-SqlAvailabilityGroupListenerStaticIp -Path SQLSERVER:\Sql\MyComputerName\MyInstanceName\AvailabilityGroups\AG01\AGListener01HA ` -StaticIp '10.10.20.139/255.255.255.0'
But to add a second listener, you’ll need to do that directly at the Windows Server Failover Cluster, not via SQL. Creating the second listener is fully supported, but something that the SQL Server tooling fails to allow. Instead, you’ll have to click through the docs & follow the directions to create it manually.
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.
In this example, I chose to build these as having the “HA” Listener be specially named. However, in most real-world scenarios my preference would be to have the “default” listener use
RegisterAllProvidersIP=1(with multiple simultaneous IPs), and the specially-named exception listener use
RegisterAllProvidersIP=0(with a single registered IPs).
We can verify the settings for these two listeners using the following PowerShell:
Get-ClusterResource -Cluster WSFC01 -Name AGListener01,AGListener01HA | Get-ClusterParameter -Name RegisterAllProvidersIP,HostRecordTTL
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.
would this help with SSRS connections in a multi-subnet cluster configuration? I have an issue since SSRS is not cluster aware, occasionally a connection will be sent to the inactive listener IP, timeout, then go to the active listener IP. leads to random reports running long occasionally. it seems that only having 1 IP listed in DNS would solve that problem.
You don’t need to change the listener for SSRS data sources. You just need to make sure that your data source definition includes the MultiSubnetFailover=True attribute on your connection string. Ex:
Data Source=ProdDb.am2.co; Initial Catalog=AdventureWorks; MultiSubnetFailover=True. You just have to edit it in the Connection String text box on this screen as there’s no UI checkbox for that attribute when building the connection string via the UI.
Can I set RegisterAllProvidersIP=0 and still use the MultiSubnetFailover=True property and reap its benefits? Or would it be pointless as only active IP address is registered in the Client Access Point in the WSFC cluster when RegisterAllProvidersIP is set to 0.
RegisterAllProvidersIP=0will only register 1 IP. Using
MultiSubnetFailover=Trueis only needed if you have more than 1 IP, but you can use it if there is only one IP. If you are trying to transition to full multi-subnet support, I suggest the two-listener approach, so that you add
MultiSubnetFailover=Trueto your connection string, and swap to the listener name that requires it. This ensures that you get it right, and makes it easier to deprecate the listener that uses