Problems with Listener in AlwaysOn AG in AWS 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?
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.