MultiSubnet AG Listener

  • I have an AG listener set up on two subnets - two nodes are in the same subnet and a third node is on a different subnet in a separate data center. The issue I'm having is that applications are timing out when connecting to the listener. Adding MultiSubnetFailover=True to the connection strings isn't possible due to some legacy apps so that is where this gets complicated. Through some research, I have set RegisterAllProvidersIP to 0 and HostRecordTTL to 120 in order to not register the offline IP and also reduce the DNS cache. I followed all of the steps including taking the resource offline and back online again but the changes still do not fix the issue. I test this by pinging the virtual node the listener uses and sometimes it replies and other times it doesn't. Has anyone experienced having to make these changes? Is there anything else I should be changing?

  • I'm not sure if this helps so apologises if I'm wasting your time but when the ping fails is it because?:

    a) It fails to find an IP in the DNS

    b) It find the correct IP and can't connect to it

    c) It finds the wrong IP

    Can you succesfully ping the IP of the active host directly?

  • When I ping the DNS it will give a reply at first and then fail on the second attempt. I suspect that's because the first attempt returned the active IP while the second attempt returned the offline IP. This is confirmed by pining the active IP directly and getting a reply and then pinging the offline IP and getting a request timed out response. How can I get the IPs to run in parallel so that there are no time out errors? I was under the impression that changing RegisterAllProvidersIP to 0 and HostRecordTTL to 120 would fix that but that is not the case.

  • Hi

    This connection time out error due to multiple IP's for listener.

    When a client operating system (OS) needs to resolve the AGL name to IP by querying the DNS server, the DNS server will return multiple IP addresses – one for each subnet. The listener IP address in the subnet currently hosting the availability group primary replica will be online. The other listener IP address(es) will be offline. Because not all of the IP addresses returned by DNS will be online, client applications can run into problems when attempting to connect to the listener.

    By default, the behavior of the SQL client libraries is to try all IP addresses returned by the DNS lookup - one after another (serially) until the all of the IP addresses have been exhausted and either a connection is made, or a connection timeout threshold has been reached. This can be problematic, because depending upon DNS configurations, the “correct” or “online” IP address may not be the first IP address returned.

    Here, you have 1 name(listener)  with two IP's (two different subnets). Now create another name (similar to listener) in DNS with 1 IP which is you already used for the listener ( which has two nodes are in the same subnet )

    Reference,

    https://techcommunity.microsoft.com/t5/sql-server-support/connection-timeouts-in-multi-subnet-availability-group/ba-p/318334

  • The article you referenced is the one I followed to change the RegisterAllProvidersIP and HostRecordTTL settings. I'm a little confused what you mean by this part

    Here, you have 1 name(listener)  with two IP's (two different subnets). Now create another name (similar to listener) in DNS with 1 IP which is you already used for the listener ( which has two nodes are in the same subnet )

    Suppose my AG listener is AG_Listener and my virtual node is VN_Name. What other name should I create in DNS? Does that new name only get the subnet of the two nodes in the same subnet? How does that work as far as failing over to the third node outside of the subnet since I assume my connection strings would be looking at a different name?

     

    • This reply was modified 6 months, 1 week ago by  RonMexico.
  • Suppose my AG listener is AG_Listener and my virtual node is VN_Name. What other name should I create in DNS? --> you can create as AG_Listener1.

    Does that new name only get the subnet of the two nodes in the same subnet? -->Yes, you can use the same IP address for the AG_Listener1 which you used for AG_Listener

    How does that work as far as failing over to the third node outside of the subnet since I assume my connection strings would be looking at a different name? --> it will not failing over to the third node

  • I'm still having trouble following and maybe it's because I don't see how it will work for my situation. My setup is exactly like "Architectural Solution 2" in this article http://sqlserverbase.com/index.php/dba/133-3-nodes-multi-site-ha-and-dr-architectural-solution-in-sql-server-geo-cluster 

    Even with the manual failover to DR, I would still need all my connection strings to stay the same. That's why I don't think having two separate listeners will work. Am I understanding that incorrectly?

    • This reply was modified 6 months, 1 week ago by  RonMexico.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply