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.

more ▼

asked Jul 17, 2017 at 07:56 PM in Default

avatar image

30 2

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.

Jul 18, 2017 at 09:06 PM KenJ

Thank you Ken.

Aug 17, 2017 at 02:36 PM bernsteingreg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 18, 2017 at 08:57 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Thank you Kev

Aug 17, 2017 at 02:48 PM bernsteingreg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 17, 2017 at 07:56 PM

Seen: 31 times

Last Updated: Aug 17, 2017 at 02:48 PM

Copyright 2018 Redgate Software. Privacy Policy