1

I have a 3-node FCI: 1 primary / 1 secondary co-located in same data center on same subnet, and 1 secondary in a DR data center on another subnet.

Periodically, the virtual IP (VIP) for the AG name flips to the DR site and clients can't connect the Primary site. The quick fix is to remove the listener from the node in failover cluster manager, then remove the DNS entry that points to the DR site from AD.

I've checked most SQL and Windows logs and I can't find anything related to this. In fact, the AG doesn't actually flip to the DR site; it's just the DNS entry that gets updated with the incorrect IP for the DR server.

Has anyone else experienced this? I have 13 AGs, on a 9 node cluster, and only 2 AGs have experienced this. One of those I tore down and completely rebuilt with a new IP on the AG listeners, but the issue returned a week or two later.

Thanks for any help or info!

  • We had an issue similar to this. On your failover cluster do you have 2 networks configured? If so, our solution was to uncheck the 'Allow clients to connect through this network' option in the Cluster Network properties for the DR subnet. – Kris Gruttemeyer Nov 17 '17 at 19:59
  • Hi Kris - thank you for taking the time to reply. This is an interesting approach...are there any downfalls / gotchas that you have encountered? i.e. can you still connect to the instance in DR directly if you need to manage it? I would assume direct connection would work, but once the AG flipped to DR then connecting via the AG name wouldn't work until this checkbox was re-enabled. Am I on the right track? Thanks again! – Jim Vogler Nov 17 '17 at 20:15
  • 1
    I'm not entirely sure. I didn't post it as an answer because I'm not entirely sure that's the solution. It's just something we ran across and that was how we fixed it. – Kris Gruttemeyer Nov 17 '17 at 20:20
  • How did you configure the VIP? Was it automatically set up through the availability group or did you add it in cluster manager? – Kent Chenery Nov 18 '17 at 0:14
  • The VIP was added through the AG group, not through Cluster Manager. Perhaps there's a difference in the end result doing through one method vs. another? – Jim Vogler Nov 20 '17 at 16:19
1

This sounds a lot like you need to add MultiSubnetFailover=true to your connection strings.

See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover

That article explains fully why you need it enabled and even why you should specify it even if only using one subnet.

The main reason is that by default SQL Client will select the first IP address it learns about. If DNS returns more than one IP address, it'll pick the first one. Its possible for the the order from DNS to shift. This setting overrides that behaviour. Now SQL Client will attempt connections to all IP addresses and resolve which is the correct one to use.

You only need to update the connection string. No other code changes are required.

  • Hi Kent - thank you for the reply. We do, in fact, have MultiSubnetFailover=True in our connection string for the app. Even machines not connecting through the app are affected (i.e. through SSMS), and a ping to the AG group name will return the IP from the secondary DR site. So something appears to be updating DNS "automagically" with the secondary IP. Of course, when we delete that entry and flush DNS things return to normal. I'm just not sure what is creating that DNS entry to the DR site... – Jim Vogler Nov 20 '17 at 16:17
  • So you're getting a totally rogue IP address showing in your DNS for that listener name? – Kent Chenery Nov 21 '17 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.