Post reply

Read only routing fail

  • 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.

  • February 5, 2019 at 4:21 pm

    #2021224

    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

  • February 5, 2019 at 4:53 pm

    #2021228

    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

  • February 6, 2019 at 3:47 am

    #2021240

    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)?

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

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