February 1, 2024 at 2:53 pm
We have a SQL Server Always On Availability Group (AG) configuration with the following components:
1. Nodes:
Node1: ZZZDB1.abc.def.gh (IP: 11.89.12.83)
Node2: ZZZDB2.abc.def.gh (IP: 11.89.12.46)
2. Failover Cluster:
Failover Cluster Name: ZZZDBCLST
IP Addresses:
11.89.12.53
11.89.12.87
3. Always On Database Listener:
Listener Name: ZZZDBAG
IP Addresses:
11.89.12.54
11.89.12.98
In multi-subnet configuration, where each node and the listener have IP addresses from the each subnet .
is This configuration is suitable for providing high availability and disaster recovery across different subnets ?
The fact that our DNS resolution for "ZZZDBAG" is returning two IP addresses (11.89.12.54 and 11.89.12.98) instead one IP address
February 1, 2024 at 3:49 pm
This in Azure?
You setup the cluster / AG as a DNN or a VNN?
If DNN then the setup is right, thats the limitation of DNNs you need to use a provider like MSOLEDBSQL that supports the multisubnetfailover option.
If its a VNN then the setup is all wrong as for a VNN you would only need 4 IP's not 6
Node1, Node2, WSFC, Listener
February 1, 2024 at 4:43 pm
This is not Azure.
Its on prem .
February 1, 2024 at 4:55 pm
Why our DNS resolution for "ZZZDBAG" is returning two IP addresses (11.89.12.54 and 11.89.12.98) instead one IP address ?
February 1, 2024 at 5:48 pm
Because you have incorrectly setup your infrastructure.
It is all single subnet so you only need 4 IP addresses.
Remove an IP from the WSFC and remove an IP from the listener configuration
February 1, 2024 at 5:58 pm
It's not a single subnet.
As part of network-level high availability, if one subnet fails, so do the other subnet's services, which is why we assigned WSFC and Listner two IP addresses from each subnet.
February 1, 2024 at 6:09 pm
It's not a single subnet.
As part of network-level high availability, if one subnet fails, so do the other subnet's services, which is why we assigned WSFC and Listner two IP addresses from each subnet.
1.Nodes:
Node1: ZZZDB1.abc.def.gh (IP: 11.89.12.83)---This IP from subnet2
Node2: ZZZDB2.abc.def.gh (IP: 11.89.12.46)------This IP from subnet1
2. Failover Cluster:
Failover Cluster Name: ZZZDBCLST
IP Addresses:
11.89.12.53-------This IP from subnet1
11.89.12.87-----This IP from subnet2
3. Always On Database Listener:
Listener Name: ZZZDBAG
IP Addresses:
11.89.12.54-------This IP from subnet1
11.89.12.98---This IP from subnet2
February 1, 2024 at 6:10 pm
What are your subnet masks then? /24s /25s /26s etc?
Just used to /24s and then 11.89.12.1-11.89.12.255 would be the full single subnet, not having it split in 25/26/27s etc.
February 1, 2024 at 6:15 pm
In multi subnet having all the IPs registered is correct and by design.
Again you need to use a provider which understands multisubnetfailover option like MSOLEDBSQL and set the MultiSubnetFailover=True connection string object.
This is due to RegisterAllProvidersIp being set to true by default.
You can disable it, but then you need to reduce TTL. Otherwise in fail over it’s going to take 15/20 minutes to update DNS and replicate out to all clients.
suggest a read of the following article
February 1, 2024 at 9:03 pm
it's ...../26
February 1, 2024 at 10:15 pm
Then yeah your setup is right. Usually see /24s not /26s so my bad there.
So yeah use a driver which supports MultiSubnetFailover connection string attribute and add that to the connection string.
Only go change RegisterAllProvidersIP if you absolutely must as it impacts time to recovery on failover due to DNS updates replication and resolution.
February 2, 2024 at 4:37 pm
are those public IP addresses?
February 4, 2024 at 8:39 pm
These are example IPs.
February 6, 2024 at 3:08 pm
Our issue is that NSLOOKUP for AG Listener Name (ZZZDBAG) returns one IP address, sometimes two, and sometimes nothing, which is unusual.
Is it supposed to return only one or two IP addresses?
We are attempting to determine whether it is a design issue with the Ag setup or an AD/DNS configuration issue.
February 6, 2024 at 3:40 pm
It depends what setting you have for RegisterAllProvidersIP
If setting is True then you should have all the IPs the listener can use
If setting is False then you should only have the active listener IP returned
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply