I have set up SQL 2016 AlwaysOn availability groups between a Primary and Secondary node. I open a session in on one of the databases and execute a long running tsql command. While that is running, I reboot the primary node so that it fails over to to the secondary node. But my tsql command does not finish and a connection error occurs. Is there a way to prevent this from happening? I want the connection between my applications and their databases to be seamless. So if I need to reboot a server, I don't want the application to get disconnected at all.
A couple of details: the availability mode on both the secondary and primary nodes is Synchronous Commit. The failover modes are Automatic.
Thank you in advance.
That's not how AlwaysOn AGs work unfortunately. You would have to retry the query, which in turn requires a re-connect.
If you want to reboot a server, then it would be better to make another replica the primary at a quieter time, and have a controlled failover.
For more info see 'Behavior of Client Connections on Failover' in the documentation : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover#CCBehaviorOnFailover
answered Jul 18, 2017 at 08:57 AM
Kev Riley ♦♦