November 6, 2018 at 10:23 am
Hi,
I have the following configuration Always On configured on a Multisubnet, when I create a Linked server to the Cname pointing to the AGListener Name, the linked server does not work, or it works intermittently,
Even there are two DNS entries associated with the - AGLXXXX- The listener IP address for the subnet currently hosting the Availability Group primary replica is online and the other IP address show as offline on the failover cluster manager, that is by design , just to access the active /primary replica. But there are two DNS entries on the DNS server associated to the AGListener Name.
How can I make this work? the connection to the linked server fail or success intermittently.
Windows Cluster Name: WCXXXX
Windows Cluster Ips: 10.9.XX.XXX, 10.0.XX.XXX (different subnets)
Network Cluster Quorum Shared Path:\\XXXXXXX
Nodes: pXXXX102 (10.0.XX.XXX), pXXXX202 (10.9.XX.XXX)
IP Listener Ips: 10.9.XX.XXX, 10.0.XX.XXX
AGListener Name: AGLXXXX
AAG Name: CXXXXAAG
CName: PXXXX – to - AGLXXXX
Thanks
November 6, 2018 at 11:22 am
I think you should have one dns entry with two ip addresses, change register all ip address to 0 for ag group, then you will have one dns entry with active ip in dns.
November 6, 2018 at 11:52 am
Refer to this article fore more details on how to handle timeout issues in a multi subnet environment.
https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
In a multi subnet setup, By default, the behavior of the SQL client libraries is to try all IP addresses returned by the DNS lookup in a serial fashion until the all of the IP addresses have been exhausted and either a connection is made, or a connection timeout threshold has been reached(I think this is why your linked server is working intermittently). Another trick without tweaking settings mentioned above is to create a system DSN and leverage that in linked server. Did you try creating Linked server with MultiSubnetFailover set to “TRUE” and see if that makes any difference? When set to TRUE, the connection attempts all of the IP addresses in parallel. BTW as mentioned above, for this method you need to create a Data Source and then use that Data Source in the Linked Server.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply