17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
Register Login Menu
Home Forums SQL Server 2022 SQL Server 2022 - Administration DNS
resolution for DB listener
Post reply Like (0) 1 2 Next
DNS resolution for DB listener
adisql February 1, 2024 at 2:53 pm #4371446
We have a SQL Server Always On Availability Group (AG)
configuration with the following components:
1. Nodes:
SSCrazy
Points: 2893 Node1: ZZZDB1.abc.def.gh (IP: 11.89.12.83)
More
Node2: ZZZDB2.abc.def.gh (IP: 11.89.12.46)
actions
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:
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 1/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
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
This topic was modified 2 weeks, 2 days ago by adisql.
Ant- February 1, 2024 at 3:49 pm Like (0) #4371484
Green 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
SSC Guru need to use a provider like MSOLEDBSQL that supports the
Points: 113405 multisubnetfailover option.
More If its a VNN then the setup is all wrong as for a VNN you would
actions only need 4 IP's not 6
Node1, Node2, WSFC, Listener
adisql February 1, 2024 at 4:43 pm Like (0) #4371495
This is not Azure.
Its on prem .
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 2/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
SSCrazy
Points: 2893
More
actions
adisql February 1, 2024 at 4:55 pm Like (0) #4371498
Why our DNS resolution for "ZZZDBAG" is returning two IP
addresses (11.89.12.54 and 11.89.12.98) instead one IP address
?
SSCrazy
Points: 2893
More
actions
Ant- February 1, 2024 at 5:48 pm Like (0) #4371523
Green Because you have incorrectly setup your infrastructure.
It is all single subnet so you only need 4 IP addresses.
SSC Guru
Points: 113405
Remove an IP from the WSFC and remove an IP from the listener
configuration
More
actions
adisql February 1, 2024 at 5:58 pm Like (0) #4371525
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
SSCrazy
Listner two IP addresses from each subnet.
Points: 2893
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 3/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
More
actions
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 4/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
adisql February 1, 2024 at 6:09 pm Like (0) #4371539
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
SSCrazy
Listner two IP addresses from each subnet.
Points: 2893
More
actions
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
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 5/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
Ant- February 1, 2024 at 6:10 pm Like (0) #4371540
Green 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.
SSC Guru
Points: 113405
More
actions
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 6/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
Ant- February 1, 2024 at 6:15 pm Like (0) #4371543
Green 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
SSC Guru MultiSubnetFailover=True connection string object.
Points: 113405
More
actions 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
https://learn.microsoft.com/en-us/sql/database-
engine/availability-groups/windows/create-or-configure-an-
availability-group-listener-sql-server?view=sql-server-ver16
adisql February 1, 2024 at 9:03 pm Like (0) #4371612
it's ...../26
SSCrazy
Points: 2893
More
actions
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 7/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
Ant- February 1, 2024 at 10:15 pm Like (0) #4371641
Green 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.
SSC Guru
Points: 113405 Only go change RegisterAllProvidersIP if you absolutely must as it
impacts time to recovery on failover due to DNS updates
More
replication and resolution.
actions
CreateIn February 2, 2024 at 4:37 pm Like (0) #4372166
dexNoncl are those public IP addresses?
ustered
SSChasing
Mays
Points: 640
More
actions
adisql February 4, 2024 at 8:39 pm Like (0) #4373171
These are example IPs.
SSCrazy
Points: 2893
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 8/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
More
actions
adisql February 6, 2024 at 3:08 pm Like (0) #4374239
Our issue is that NSLOOKUP for AG Listener Name (ZZZDBAG)
returns one IP address, sometimes two, and sometimes nothing,
which is unusual.
SSCrazy
Is it supposed to return only one or two IP addresses?
Points: 2893
More
actions
We are attempting to determine whether it is a design issue with
the Ag setup or an AD/DNS configuration issue.
This reply was modified 1 week, 4 days ago by adisql.
Ant- February 6, 2024 at 3:40 pm Like (0) #4374269
Green It depends what setting you have for RegisterAllProvidersIP
If setting is True then you should have all the IPs the listener can
use
SSC Guru If setting is False then you should only have the active listener IP
Points: 113405 returned
More
actions
Viewing 15 posts - 1 through 15 (of 17 total)
1 2 Next
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 9/10
17/2/24, 22:50 DNS resolution for DB listener – SQLServerCentral Forums
You must be logged in to reply to this topic. Login to reply
https://www.sqlservercentral.com/forums/topic/dns-resolution-for-db-listener 10/10
