What happens to the connections when a failover occurs in Always On?

Category: sql server dr

Question

Zoe.Ohara on Mon, 23 Sep 2019 13:43:33


Hi!

Just wondering what happens in a 2 node always On AG when a manual failover occurs? 

Do the connections just move over to the replica node or are they terminated? Are the users even aware of the failover?

Thanks,

Zoe

Replies

Olaf Helper on Mon, 23 Sep 2019 14:09:06


Physical Connections can not move between nodes, they terminate and the client has to re-connect.

Hilary Cotter on Mon, 23 Sep 2019 15:14:57


If they are connected to the listener, they will be disconnected. Hopefully the application will have some retry logic in there, so they will try to connect again and this time they will connect through the listener to the new primary (or secondary, depending on if application intent is set).

If the listener is configured for multisubnets, you will need set your HostRecordTTL cache according on your client to they will be reconnected to the new subnet.

consult this link for more information.

https://techcommunity.microsoft.com/t5/SQL-Server-Support/Connection-Timeouts-in-Multi-subnet-Availability-Group/ba-p/318334

Amelia Gu on Tue, 24 Sep 2019 03:27:36


 Hi Zoe,

>> Do the connections just move over to the replica node or are they terminated?

During a failover, a secondary replica transitions to the primary role and the former primary replica transitions to the secondary role. During the failover process, all client connections to both the primary replica and secondary replicas are terminated. After failover, clients must reconnect to the current primary database.

>>Are the users even aware of the failover?

If the availability group comes back online during a client application's connection attempt but before the connect timeout period, the client driver may successfully connect during one of its internal retry attempts and no error will be surfaced to the application in this case.

When a client reconnects to the availability group listener, the listener reconnects the client to the new primary replica, except for a read-intent connect request.

Help this could help you.

Best Regards,

Amelia Gu

Zoe.Ohara on Tue, 24 Sep 2019 08:17:46


Thanks everyone! Thats cleared that up for me!

Zoe