what happens to user connections when an AG fails over?

Question

I'm not sure quite how to word this, but I am looking for anything a DBA can do (things outside of changing code in the application) to reduce the impact to users when an AG fails over.

We have a 2 node AG running SQL 2016 SP2 CU10 in synchronous commit mode, connecting with a listener.

What controls the switching of connections to the new primary node? is there a way to accelerate the switch? Anything in the connection string, AG settings, changing timeouts, etc. that would help make the switch faster for users?

Answer 1

what controls the switching of connections to the new primary node?

The Availability group (AG) listener directs incoming connections to either primary or read replica depending on how the AG is configured (whether it allows read requests or not).

When a failover occurs, a role reversal happens and a secondary replica transitions to the primary role and the former primary will transition to a secondary role.

During a failover, all client requests are terminated. This is your application downtime. Now, after the failover, when a client reconnects to the AG listener, the listener will reconnect to the new primary (except for read intent connect requests).

Anything in the connection string, AG settings, changing timeouts, etc. that would help make the switch faster for users?

• If your application supports :

  • MultiSubnetFailover = TRUE, then you will have a perf gain (as described in my answer.)

  • MultiSubnetFailover = False, then you should lower the HostTTL settings to a lower value e.g. 5 secs vs 300 secs of default.

Ref:

• How the Connection Access Configuration Affects Client Connectivity
• Connection Timeouts in Multi-subnet Availability Group

Answer 2

Do you mean the client connections establish slowly when pointing to AG listener, and want to avoid that? if so, that is usual/known issue particularly when availability groups configured in multi sub-net environment, and the issue is due to default timeout for a TCP connection attempts from client to DNS Server.

However, even in single sub-net using additional parameter (MultiSubnetFailover=Yes) within connection string could make client connections faster than normal. For more details..