Different subnet cluster SQL Always On

Salves 501 Reputation points
2020-10-21T18:59:44.243+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,262 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
940 questions
{count} votes

Accepted answer
  1. Ben Miller (DBAduck) 951 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.


1 additional answer

Sort by: Most helpful
  1. 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!

    0 comments No comments