2

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?

2

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 :

Ref:

|improve this answer|||||
  • We are using a multi-subnet setup, so I'll look into changing the Host TTL setting, I am not sure what that is set for. I am aware that all connections are terminated when a failover happens, but my QA team is asking for ways to reduce the time it takes for the application to reconnect to the new primary. When the Host TTL expires, is the connection automatically retried, or would it require some application action to do that? I remember looking into the multisubnetfailover option in the connection string and think it was not supported by our application, but I will take a second look. – DBA Greg14 Oct 30 '19 at 17:07
0

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..

|improve this answer|||||

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.