Read only routing fail

Question

Read only routing fail

askcoffman

Mr or Mrs. 500

Points: 580
More actions
February 5, 2019 at 3:17 pm

#416675

I'm setting up read only routing for a 2 server AlwaysOn cluster. I cannot get the routing to correctly go to the secondary host. It either fails or goes to the primary in the different configurations I've used.
Here are my settings below.

Error message from SQLCMD statement. "sqlcmd -S sqlqa16Listener,1433 -E -d DBA -K ReadOnly"
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Client unable to establish connection because an error was encountered during handshakes before login.
Thoughts? Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

askcoffman - Tuesday, February 5, 2019 3:17 PM
I'm setting up read only routing for a 2 server AlwaysOn cluster. I cannot get the routing to correctly go to the secondary host. It either fails or goes to the primary in the different configurations I've used.
Here are my settings below.

Error message from SQLCMD statement. "sqlcmd -S sqlqa16Listener,1433 -E -d DBA -K ReadOnly"
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Client unable to establish connection because an error was encountered during handshakes before login.
Thoughts? Thanks in advance.

Did you try querying sys.availability_read_only_routing_lists to check the routing list?

Sue

askcoffman Mr or Mrs. 500 Points: 580 More actions · Answer 2

Sue_H - Tuesday, February 5, 2019 4:21 PM
askcoffman - Tuesday, February 5, 2019 3:17 PM
I'm setting up read only routing for a 2 server AlwaysOn cluster. I cannot get the routing to correctly go to the secondary host. It either fails or goes to the primary in the different configurations I've used.
Here are my settings below.

Error message from SQLCMD statement. "sqlcmd -S sqlqa16Listener,1433 -E -d DBA -K ReadOnly"
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Client unable to establish connection because an error was encountered during handshakes before login.
Thoughts? Thanks in advance.
Did you try querying sys.availability_read_only_routing_lists to check the routing list?
Sue

The 2nd screenshot includes the priority from that table. But if it helps I included the entire output.
replica_id routing_priority    read_only_replica_id
DA74A2A1-B37D-40B1-9FEA-6E269CB517AF    1    D73F4FA4-771A-46E8-843D-AF9A2BBD378B
D73F4FA4-771A-46E8-843D-AF9A2BBD378B    1    DA74A2A1-B37D-40B1-9FEA-6E269CB517AF

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

I think the issue is that the port number at the end of your routing URL (5022) looks to be the same as for your Endpoint URL. Can you try changing it to 1433 (or whatever port you're using for that instance if it's a named instance)?