question

salves avatar image
0 Votes"
salves asked JohnJY-4666 answered

Different subnet cluster SQL Always On

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.

sql-server-generalwindows-server-clustering
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is this an Availability Group or a Failover Clustered Instance?

0 Votes 0 ·
salves avatar image salves Ben-Miller-DBAduck ·

SQL Server 2016 com Availability Group

0 Votes 0 ·

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

0 Votes 0 ·
Show more comments
Ben-Miller-DBAduck avatar image
0 Votes"
Ben-Miller-DBAduck answered Ben-Miller-DBAduck commented

@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.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Ben-Miller-DBAduck ,

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·
salves avatar image salves Ben-Miller-DBAduck ·

@Ben-Miller-DBAduck,

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).

0 Votes 0 ·
salves avatar image salves Ben-Miller-DBAduck ·

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.

0 Votes 0 ·
Show more comments
JohnJY-4666 avatar image
0 Votes"
JohnJY-4666 answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.