Availability Group Read-only Routing

This morning at a customer site I was researching an issue where Availability Group read-only routing was not working correctly. Quickly I was able to determine the issue was a misconfigured read-only routing URL list. In this blog post I’ll show you the requirements for read-only routing in Availability Groups, how I determined the URL list was the issue and what to do to fix the situation. The requirements for Read-only routing in Availability Groups are:

  1. A configured listener
  2. At least one replica is configure for read-only access
  3. Each secondary is configured with a URL
  4. Each replica has a configured routing list
  5. The replica being routed to must be synchronized or synchronizing.

Now, let’s explore the requirements and then what happens with condition 5 is not met and the steps to find and remedy the issue.

Is there a listener configured?

Screen Shot 2015 01 25 at 12 16 23 PM This shows us that there is one AG listener, requirement 1 met! Review the current access configuration and read only routing URL configuration of each replica

Screen Shot 2015 01 25 at 12 14 38 PM1

So we can see each is configured for read-only access, indicated by secondary_role_allow_connections_desc = ALL and that each replica has a read only routing url, excellent requirements 2 and 3 are met, let’s move on.

Let’s explore the routing lists for the Availability Group

Read only routing listsFrom this output we can see that when SQL14-A is a read-only replica of SQL14-B and SQL14-B is a read-only replica of SQL14-A. Requirement 4 met, right? Yes, but let’s dig deeper. 4. Let’s test out connectivity under the condition when the current read only replica does not meet the routing requirements listed above. Stop data movement to SQL14-B, which changes the database state to Not Synchronizing

Screen Shot 2015 01 25 at 11 53 19 AM Now let’s test connectivity with SQLCMD, which now has ApplicationIntent as a command line parameter. You’ll need the -d parameter which sets the database context for read only routing to work.

Yikes, that didn’t work. Shouldn’t it have just failed back to SQL14-A. It didn’t. Why not? Well our only configured read-only replica SQL14-B no longer meets the connection requirements. We need to tell the system what to do in this state. 4. Let’s change our read-only routing URLs to include both the primary and the read-only replica

What we need to to configure the read-only routing URL list to point to all possible read-only replicas, including the primary. 

Let’s check our routing list again, as you can see each server now has two entries and the priority is set for the entries relative to their position in our routing list

Screen Shot 2015 01 25 at 12 19 45 PM So let’s test with SQLCMD again

Excellent, the AG is back up and running for applications configured with ApplicationIntent=ReadOnly, but we’re not quite there yet, we still are pointing to the Primary. We need to restart data movement on SQL14-B to get our database state back to Synchronized so the workload can use the read-only secondary.

Screen Shot 2015 01 25 at 11 58 30 AM And let’s try with SQLCMD once more

Success! The key take away here is that the read-only routing URL needs to list all possible read-only replicas, including the primary not just the servers that are current secondaries.  Please feel free to contact me with any questions regarding Availability Groups or other SQL Server related issues at: aen@centinosystems.com References: Configure Read-Only Routing for an Availability Group (SQL Server)     

Shout out to Jared Karney for picking up an error in this post! Thanks!

1 thought on “Availability Group Read-only Routing

  1. KCV

    thank you. nice article on read Only routing. but if i need to remove the read only routing entries.. what level of permissions i need to have on AG and how can i delete the read only routing urls?


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.