select operation drop when SQL server AOG failing over.

marrowyung
marrowyung used Ask the Experts™
on
hi all,

when I doing a select (*) operation against a MS SQL server 2016 AOG with 2 x read-only load balanced replica, I tried to failover to other node and see what will happen to the select (*)  operation,

I got this:

Msg 10054, Level 20, State 0, Line 14
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 。)

Open in new window



any reason why the select (*) is not keep going but return an error during AOG failover ?

I tried that using server local version of SSMS, it seems it is the SSMS version problem! What I found out is sth else, the AOG failover will WAIT until the existing select query is finished, not that good, right?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
How are you connecting to the database Replica? Can you post the current connecting string that you're using?
Also, how many Replicas the database has?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Also, how many Replicas the database has?"

1  x primary and 2 x secondary.

"How are you connecting to the database Replica?"

via connector.

"an you post the current connecting string that you're using?"

Server= tcp:SQLAOGlistener,1433; Trusted_Connection=true; Database=WideWorldImporters;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
1  x primary and 2 x secondary.
From which Node to which did you failed over the AOG?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
login to primary and failover to secondary.
IT Engineer
Distinguished Expert 2017
Commented:
I've already posted this article in your another question but I think they are related so check if it helps.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.
marrowyungSenior Technical architecture (Data)

Author

Commented:
i might come back.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial