Different subnet cluster SQL Always On

Salves 501

Hi,

I have a cluster that is used for SQL Always On with two IPs on different networks.

Name: Cluster Name
IP Address: 172.16.0.200
IP Address: 172.17.0.200

As far as I understand when a role fails over the IP of the cluster is changed and the DNS too. This is happening successfully.

But I noticed today that all my roles are on the network (172.16.0.0/24) but the IP of the cluster name is on the network (172.17.0.0/24).

The most interesting thing is that ping does not respond to either IP.

Then a question arose regarding these IP changes in the cluster.

As I have several roles, some have two IPs (one from the 172.16.0.0/24 network) and one from the network (172.17.0.0/24) while others only have network IP (172.16.0.0/24).

Can anyone help me understand why the cluster's IP was like this?

Interesting...

I just turned off the server on the network (172.17.0.0/24) and the IP was returned to the network (172.16.0.0/24) and now I can ping the network's IP (172.16.0.0/24).

But when the switch to the network IP (172.17.0.0/24) of the cluster name occurs, I can access the cluster, but it does not drip from anywhere.

I restarted the network server again (172.17.0.0/24) everything went back to normal.

The strangest thing is that I can ping the IP of the network server (172.17.0.0/24), but not just the IP of the cluster name (172.17.0.0/24).

I'm confused.

Thank you.

Ben Miller (DBAduck) 956 Reputation points

2020-10-22T03:27:26.343+00:00

Is this an Availability Group or a Failover Clustered Instance?
Salves 501 Reputation points

2020-10-22T05:27:35.663+00:00

SQL Server 2016 com Availability Group
CathyJi-MSFT 22,331 Reputation points Microsoft Vendor

2020-10-22T09:55:56.947+00:00

Hi @Salves ,

It seems you configured SQL Server Always On Availability Group on a Multi-Subnet Cluster? Did the IP of cluster you mean IP address of AG lister? Did you assign two different virtual IPs (one from each subnet) to AG listener? When AG failover, the listener IP address for the subnet currently hosting the Availability Group primary replica is online and the other two listener IP addresses show as offline.

Best regards,
Cathy
Salves 501 Reputation points

2020-10-22T17:36:59.173+00:00
Hi @ Cathyji-msft,

grateful for your time.

Yes, I know how the SQL Awalys cluster procedure works on different subnet.

I will explain what I know and you will correct me if I say something wrong.

How does the IP update of the FQDN of cluster name.

I say cluster name is the name of the cluster and not the SQL roles.

When we create a cluster an FQDN is created and assigns two IPs to the cluster name name in the failover cluster. However, in the DNS, only the IP that is online for the cluster name and the other offline line will be active.
Salves 501 Reputation points

2020-10-22T17:37:17.85+00:00
continuing ...

How does the IP update of the FQDN of the SQL roles work?

In this case it is different, because when we create an AG two DNS records are created with the same FQDN, but one with each subnet IP.

In the failover cluster the name of the AG will receive two IPs as well, as the FQDN of the cluster name and will also have an IP always offline. The activation of the IP in the role of SQL is performed when a faiolver occurs and the server that is part of the AG that is on the other network becomes primary.
Salves 501 Reputation points

2020-10-22T17:37:56.83+00:00
continuing ...

Abstract:

I understand that when a SQL role fails over and the SQL server that is on the other subnet becomes primary, the switch at the level of the cluster faiolver occurs both for the role of SQL and also for the FQDN of the cluster name.

That in the case of the FQDN of the cluster name the faiolver cluster changes the IP of the DNS to the IP of the same subnet as the subnet server has become primary.
Salves 501 Reputation points

2020-10-22T17:38:42.56+00:00
continuing ...

What happens is the following:

When the server that is in the subnet (172.17.0.0/24) becomes primary, the FQDN of the cluster name is also changed to the subnet (172.17.0.0.24).

However, I have other AGs that remain on the subnet (172.16.0.0/24).

In practice the FQDN with the cluster IP is transparent to the SQL role of my failover cluster, but I was curious about this IP switching mechanism of the FQDN of the cluster name when I have AGs on a different subnet and the faiolver occurs at the level of AG causing the exchange of the IP of the FQDN of the cluster name.
Salves 501 Reputation points

2020-10-22T17:39:40.71+00:00

continuing ...

What does not make sense to me is when the FQDN of the cluster name is changed to the subnet (172.17.0.0/24) I can no longer access the cluster. It just doesn't drip!

If I turn off the server that is in the subnet (172.17.0.0/24), automatically the FQND IP of the cluster name is changed to (172.16.0.0/24) and everything works again.

I am believing that for some reason when faiolver occurs at the AG level and the FQDN of the cluster name follows this change natively by logic, the servers that are on the network (172.16.0.0.24) that remain active cannot answer for the subnet (172.17. 0.0 / 24) because they are not really on that subnet.

The logic of changing the IP of the FQDN of the cluster name is valid when the others were offline and not online as well.

Thank you.

Accepted answer

Ben Miller (DBAduck) 956 Reputation points

2020-10-22T18:09:48.32+00:00
@Salves here is how it works.

The cluster name has 2 IP addresses and will register the IP it needs when failing over. Not a concern because you typically to not address the Cluster directly, you address SQL Server.

Here is how it works in the AG SQL Role. By default when you create a listener for the AG with 2 IP Addresses, they will both be registered in DNS which can cause confusion for clients connecting. You need to set a couple of things to have this work optimally.

If the AG Listener Resource is named AG1 then this is what you would do in PowerShell using the FailoverClusters module. (you can find out what it is by using Get-ClusterResource -Cluster clustername)

Get-ClusterResource -Name AG1 | Set-ClusterParameter -Name HostRecordTTL -Value 300 Get-ClusterResource -Name AG1 | Set-ClusterParameter -Name RegisterAllProvidersIP -Value $false

With the RegisterAllProvidersIP being $false, it will modify it so that only the Active IP will be registered in DNS.
Then on the client side to get the connection faster if the AG ends up on the other IP, you would use
MultiSubnetFailover=true
in your connection string, this will tell the connection that if the current one cannot be contacted, to refresh (not exactly what happens under the covers, but close) and then will reattempt the connection. The HostRecordTTL will be used to tell the DNS record how long it will live before the router has to request a new copy, which would be the new IP if it had failed over to the other node.
Please sign in to rate this answer.
Salves 501 Reputation points

2020-10-22T19:12:53.95+00:00

anonymous user ,

yes, I understand that the cluster name IP is not a problem and that's what I mentioned above too.

But do you know how I observed and had a problem with that? When I tried to use Cluster-Aware Update.

Now studying more I understand that Cluster-Aware Update is not a good practice for an environment with SQL Awalys On.

I was connected to the cluster normally on one of the nodes and when trying to connect on the FQDN of the cluster I couldn't. Then I noticed that the FQDN's IP was using the subnet (172.17.0.0./24) which is not my main environment subnet. My production network is (172.16.0.0/24) while the subnet (172.17.0.0/24) is from the DR.

That's why I got into this questioning.

ow regarding AGs, all right, this is working very well.

These settings you mentioned one of them (HostRecordTTL) is mentioned in the cluster validation report, perfect!

Now this (RegisterAllProvidersIP) made me think of something interesting.

Salves 501 Reputation points

2020-10-22T19:13:20.267+00:00

Continuing ...

I really read that the way I configured it, that is, RegisterAllProvidersIP $ true I will have to configure the connection strings to use MultiSubnetFailover = true and some may not support it, but I believe they all do.

This means that if I use (RegisterAllProvidersIP $ false) I don't need to worry about the string (MultiSubnetFailover = true), because the IPs of the roles SQL will use a single IP record in the DNS, which will be updated automatically when the faiolver occurs for the server that is on the other subnet.

Is that what I understood?

If your answer is yes, I ask you: Can I apply this configuration with my AGs already created? Did they work?

Thank you.

Ben Miller (DBAduck) 956 Reputation points

2020-10-22T19:25:11.85+00:00

You almost have it.

When using an AG in a mutli-subnet scenario you need to have RegisterAllProvidersIP to be $false or you will get some interesting results in the AG connectivity. The HostRecordTTL just governs how long the DNS entry will live in routers after it is changed until it times out and routers request the new one.

The MultiSubnetFailover = true is just a way to have the driver retry when the connectivity fails, it is supposed to help your connection succeed faster because it is aware. You will still need to have the RegisterAllProvidersIP = $false regardless.

If your drivers do not allow MultiSubnetFailover attribute then it will just not be as fast to connect to it when it has failed over.

Yes you can apply these to existing AGs.

Salves 501 Reputation points

2020-10-22T20:16:03.443+00:00

anonymous user,

Sorry to ask again, but that hasn't made sense to me yet. I understood the explanation and thank you very much for your time, but it is still unclear why you need to change (RegisterAllProvidersIP $ false), even using (MultiSubnetFailover = true).

I read a very explanatory article about how the client establishes communication with the Listeners IPs on Always On and that is why I may be having difficulty understanding, as I only knew (MultiSubnetFailover = true) until now.

I had already read about (RegisterAllProvidersIP $ false) for specific cases where if connection "could not be used" (MultiSubnetFailover = true) you can resort to change (RegisterAllProvidersIP $ false).

Salves 501 Reputation points

2020-10-22T20:16:20.223+00:00

Continuing ....

I currently have two DNS records for my AG Listers.

When I set (RegisterAllProvidersIP $ false) I will see in my DNS only an IP with the name of the AG Listiner. Different from what it is today, that is, today it creates two FQDNs with the same name as the Listiner one with the IP of one subnet and the other with IP of the other subnet.

If this is true, I'm sincerely sorry I don't see the need for (MultiSubnetFailover = true). It does not make sense to me, as access to the Listiner will be done via FQDN and not IP.

If you can correct me I will be happy to learn one more time.

Thank you.

Ben Miller (DBAduck) 956 Reputation points

2020-10-22T20:52:38.63+00:00

Let me see if I can help.

DNS should ever only have 1 entry per A record. If you have 2 then it is a roll of the dice of which one gets served on a request. So having 2 DNS entries (RegisterAllProvidersIP = true) is going to cause issues with connectivity. So you need to turn that setting to false so that only 1 entry ever gets registered and that is the one that is active so connectivity is solid.

Multisubnetfailover is an attribute in the connection string that tells the driver that there is a two IP possibility that could happen that the active one is down and will be replaced when the other one is registered on failover. It is NOT a requirement, but it does help to reestablish connectivity faster (everything is relative).

Does that help?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

John JY 221 Reputation points

2021-09-10T17:36:57.787+00:00

Hi Salves,
We have exactly same set up as you.
Can I ask if widnows core cluster fails, will SQL AG role fail at your configuration?

Thank you!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Different subnet cluster SQL Always On

1 additional answer

Your answer