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?

SELECT	ag.name
	, agl.dns_name
	, agl.ip_configuration_string_from_cluster
FROM	sys.availability_group_listeners agl
INNER JOIN sys.availability_groups ag on ag.group_id = agl.group_id

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

SELECT replica_server_name
	, read_only_routing_url
	, secondary_role_allow_connections_desc
FROM sys.availability_replicas

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

SELECT	  agr1.replica_server_name		--the source replica
		, agr2.replica_server_name		--the read only replica for the source replica
		, agr2.read_only_routing_url	—the configured read-only routing URL for the read-only replica
		, agr.routing_priority			--the routing priority for that read-only replica
FROM sys.availability_read_only_routing_lists agr
INNER JOIN sys.availability_replicas agr1 ON agr.replica_id = agr1.replica_id
INNER JOIN sys.availability_replicas agr2 ON agr.read_only_replica_id = agr2.replica_id
INNER JOIN sys.availability_groups ag ON ag.group_id = agr1.group_id
ORDER BY agr1.replica_server_name, agr.routing_priority

From 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

ALTER DATABASE [TestAG1] SET HADR SUSPEND;
GO

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.

sqlcmd -S AGL1 -d TestAG1 -K ReadOnly -Q "Select @@servername"

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Unable to access the 'TestAG1' database because no online secondary replicas are enabled for read-onlyaccess. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

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. 

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL14-A'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'SQL14-B',N'SQL14-A')))

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL14-B'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'SQL14-A',N'SQL14-B')))

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

So let’s test with SQLCMD again

sqlcmd -S AGL1 -E -d TestAG1 -K ReadOnly -Q "Select @@servername"
------------------------------------------------
SQL14-A

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.

ALTER DATABASE [TestAG1] SET HADR RESUME;
GO

And let’s try with SQLCMD once more

sqlcmd -S AGL1 -E -d TestAG1 -K ReadOnly -Q "Select @@servername"
------------------------------------------------
SQL14-B

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!