locked
Read Only Routing RRS feed

  • Question

  • I need to direct the query request to the secondary replica in AG.

    I searched that the read only routing can do it. But I still do not understand how to set it.

    Monday, December 24, 2018 5:10 PM

Answers

  • Hi Cornelius,

    Please refer the below link for step by step procedure to Configure Read Only Routing

    http://www.sqlservercentral.com/articles/always+on/167193/

    Let em know if you need any more info

     Hope this helps...

    ---------------------------------------------------------------------------------------------------------

    Please don't forget to “mark the replies as answers if they helped, also set "like" it’s a boost for us to keep blogging J

    Click here to learn more. Visit the dedicated Community forum to shareexplore and talk to experts about Microsoft Kaizala.

    Monday, December 24, 2018 5:56 PM
  • Hi Cornelius_RE,

     

    To configure an Always On availability group to support read-only routing in SQL Server, you can use either Transact-SQL or PowerShell.

     

    Firstly, please make sure that one or more availability replicas must be configured to accept read-only in the secondary role.

     

    Next, for each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. This URL takes effect only when the local replica is running under the secondary role.

     

    Then, for each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list.

     

    By the way, read-only routing is available in SQL Server 2016 (13.x) and later.

     

    For more detailed information, please refer to the document: Configure read-only routing for an Always On availability group

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Cornelius_RE Wednesday, December 26, 2018 4:18 PM
    Tuesday, December 25, 2018 3:01 AM

All replies

  • Hi Cornelius,

    Please refer the below link for step by step procedure to Configure Read Only Routing

    http://www.sqlservercentral.com/articles/always+on/167193/

    Let em know if you need any more info

     Hope this helps...

    ---------------------------------------------------------------------------------------------------------

    Please don't forget to “mark the replies as answers if they helped, also set "like" it’s a boost for us to keep blogging J

    Click here to learn more. Visit the dedicated Community forum to shareexplore and talk to experts about Microsoft Kaizala.

    Monday, December 24, 2018 5:56 PM
  • Hi Cornelius_RE,

     

    To configure an Always On availability group to support read-only routing in SQL Server, you can use either Transact-SQL or PowerShell.

     

    Firstly, please make sure that one or more availability replicas must be configured to accept read-only in the secondary role.

     

    Next, for each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. This URL takes effect only when the local replica is running under the secondary role.

     

    Then, for each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list.

     

    By the way, read-only routing is available in SQL Server 2016 (13.x) and later.

     

    For more detailed information, please refer to the document: Configure read-only routing for an Always On availability group

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Cornelius_RE Wednesday, December 26, 2018 4:18 PM
    Tuesday, December 25, 2018 3:01 AM