locked
Always-on High Availability issue RRS feed

  • Question

  • Hi,

    I have configured Always-on high availability on Windows Cluster server and it is working fine on both cluster the nodes.

    But while Connecting to SQL Server from third machine within a domain using listener name listener is connecting to secondary server where databases are in Read-only mode instead of connecting to primary.

    Please Someone help me.

    • Moved by Mekh Subba Friday, April 10, 2015 3:20 PM Moving to correct forum
    Friday, April 10, 2015 12:55 PM

All replies

  • Hello,

    Have you setup read only routing? That's the only thing that would make sense in this situation.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Saturday, April 11, 2015 2:40 AM
    Answerer
  • The Listener Name will always redirect to the primary replica unless we configure a read-only routing list. A read-only routing list is a list of all Availability Group Replicas that are configured to accept read-only workloads. SQL Server will redirect client connections that have the Application Intent=Read-only parameter in the connection string to one of the replicas in the read-only routing list. 


    http://kushagrarakesh.blogspot.in/2013/08/overview-and-benefits-of-read-only.html

    Also make sure the listener is online:
    SELECT * FROM sys.dm_tcp_listener_states;
    To restart an offline listener:
    ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'myAG_Listener';


    • Edited by dave_gona Saturday, April 11, 2015 7:38 AM
    • Proposed as answer by Michelle Li Monday, April 13, 2015 3:14 AM
    Saturday, April 11, 2015 7:37 AM
  • Hi Sean,

    I've set up read-only routing.

    -haka

    Monday, April 13, 2015 11:09 AM
  • Hi,

    I've unchecked the Read-only secondary replica even then it's showing the same behaviour.

    Please Help!!!!!

    -haka1 

    Monday, April 13, 2015 11:39 AM
  • Haka,

    Could you list the nslookup of the machines (individually) and the listener?

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Thursday, April 16, 2015 4:25 PM
    Answerer
  • Could you please let us know, Which application are you using on your 3rd machine to connect to AG Listener, If it is backup solution software, it will by default connect to read only replica if Backup preference is configured as secondary. By defult it is set to Secondary, you can always change it.
    Sunday, April 19, 2015 12:53 PM