x

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 at 07:56 PM in Default

avatar image

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

2 days ago KenJ
(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 at 08:57 AM

avatar image

Kev Riley ♦♦
65.6k 48 63 81

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x17

asked: 3 days ago

Seen: 15 times

Last Updated: 2 days ago

Copyright 2017 Redgate Software. Privacy Policy