0

I have a 2 node AlwaysOn AG cluster with a single database being replicated between the 2 nodes. Windows Failover Clustering Services are installed on each node.

The AG is configured to allow connections for either node when it is primary but not to allow connections if the node is the secondary.

When I connect using the listener name, I randomly connect to the secondary node (which I would not expected to happen).

I am reviewing the IP addresses and subnets with my Infrastructure team to confirm that I have configured the AG correctly.

Is this a known problem with SQL Server 2016 SP2 CU6?

  • Are you providing the database name in your connection string? AG connection resolution functions at the database level, not the server level. – George.Palacios Jun 18 '19 at 7:49
  • That should not matter if I'm connecting via SSMS. It appears to happen whether I connect programmatically or via a GUI. – ArgeeSix Jun 18 '19 at 10:20
  • But it does matter - imagine if you had two AG's set up with the same two nodes - how would the driver know which server to connect to? If you don't provide the database either in the GUI or in the connection string (remember that SSMS builds your connection string behind the scenes anyway), there is no way to know which server the user wants. – George.Palacios Jun 18 '19 at 10:28
1

When I connect using the listener name, I randomly connect to the secondary node (which I would not expected to happen).

It really shouldn't happen, so it tells me something is not compliant in the environment. Given this is networking related, it could be to do with the DNS/cache settings or the way AWS does their internal networking and updates.

Is this a known problem with SQL Server 2016 SP2 CU6?

No and I highly doubt it would even fall under SQL Server's purview. You'll want to take a network trace, at a minimum, from the client.

AG connection resolution functions at the database level, not the server level.

Not quite. When using read only routing a database name is required, but to just connect to an availability group it's not needed. Connection "resolution" is not at the database level, in fact the listener is backed by your typical Network Name + IP resources in Windows Server Failover Clustering, so technically most of the listener functionality isn't even within SQL Server (since the DNS/IP point to the server).

[...] imagine if you had two AG's set up with the same two nodes - how would the driver know which server to connect to?

The drivers asks DNS what addresses it has for the name, then depending on the driver and version of the driver it will attempt to connect to one or more of those addresses.

  • If there are Windows event log entries that say that there are timeouts talking to the DNS server, that would seem to point to DNS being the issue, right? – ArgeeSix Jun 19 '19 at 19:34
0

Problem turned out to be a bad IP record in the AG Listener configuration that was pointing to the Cluster IP instead of the AG node IP. Once that was fixed, the problem disappeared. After that, I used sys.dm_hadr_cluster_networks across all of my AGs and had the system engineers verify that they had provided me with the correct IPs for all of my listeners.

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.