question

bernsteingreg avatar image
bernsteingreg asked

Losing connectivity with AlwaysOn

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.
alwayson
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

When an active query falls victim to an AG failover, you should see error 64 when the connection is broken (and the connection will always break, as @Kev Riley mentions): > Msg 64, Level 20, State 0, Line 0 > A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) To provide the seamless user experience you are after, your applications should catch this error, reconnect to the AG listener, then re-run the query. The user may experience a delay in receiving the query results, but they shouldn't have to take any action of their own to recover from the failover - the application would do it all for them. I wouldn't imagine this would work in every conceivable scenario (perhaps a series of dependent queries were being run and the application no longer has all of the original or intermediate inputs), but it could get you close to that seamless experience.
0 Likes 0 ·
Thank you Ken.
0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Kev
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.