Post reply

What are the immediate after-effects of changing the read_only_routing_url for the servers in my high availability group?

  • February 22, 2019 at 10:08 am

    #381553

    When I make changes to the READ_ONLY_ROUTING_URL or READ_ONLY_ROUTING_LIST settings on the servers in my HA group, does this have any immediate performance consequences?  Can I do it on a live system, or should I schedule a maintenance window?

    For example, these are the commands I am talking about to configure the read only routing.  

     ALTER AVAILABILITY GROUP [AG40VS]  MODIFY REPLICA ON N'DB43VS' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP:// N'DB43VS'.test.com:50000'));
    ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB41VS' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB42VS','DB43VS')));

    Thanks!
    -Tina

  • February 26, 2019 at 6:29 am

    #2022959

    Changes to Read Only Routing should take effect immediately after making the change. However, applications must contain the parameter ApplicationIntent=ReadOnly in the connection string to use the readable secondary. If the applications using the SQL Server have that, then you should see more connections on the readable secondary. 

    Typically, most organizations would probably have you make this change during a maintenance window for a production system. I would check with whomever is in charge of your change management at your organization. I've only ever had one issue when setting up Read Only Routing and that's because I had a typo.

Viewing 2 posts - 1 through 1 (of 1 total)

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