Modifying AlwaysOn Read Only Routing Lists
There are several good references that show how to create a Read Only Routing List for an AlwaysOn Availability Group. This article will not go into how to create Read Only Routing lists. For a list of those articles, see the References section at the bottom of this post.
Recently while assisting a customer with an issue, we needed to know what routing lists were present (if any) for their availability group. The Microsoft SQL Server product team has provided a DMV for this information, sys.availability_read_only_routing_lists, but by itself it is rather cryptic:
SELECT * FROM sys.availability_read_only_routing_lists
Enhancing the query a little bit, will yield much more helpful information:
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority
The output above shows the routing list ordered by priority which is the order in which read-only connections will be routed to if the client has met all of the pre-requisites to use a Read-Only Routing list. That list again is:
· A routing list must be present.
· The client must connect to the Availability Group Listener.
· The client must specify “ApplicationIntent=ReadOnly” on its connection string.
· The client must specify “Initial Catalog=<db_in_avail_group>” on its connection string.
· The client must be using the latest providers that support ApplicationIntent parameter:
o SQL Server Native Client Support for High Availability, Disaster Recovery
o JDBC 4
o .NET 4.5
Now that we have a query that can give us the Availability Group Replica Servers in the order in which they will be routed to, what if I need to change that list? – Perhaps I want to remove a server altogether from the routing list, for example if the server is down for extended maintenance. Or, perhaps, I just want to change the order because of performance reasons? How do I change the order of the routing list?
The answer is with the TSQL command, ALTER AVAILABILITY GROUP.
To REMOVE a server from the routing list simply specify a new READ_ONLY_ROUTING_LIST that omits the server to be removed. For example, if the original routing list was created with the following TSQL command with two servers:
ALTER AVAILABILITY GROUP [AGUP]
MODIFY REPLICA ON N'LC2SQLAOLB1'
WITH (PRIMARY_ROLE
(READ_ONLY_ROUTING_LIST=('LC2SQLAOLB2','LC2SQLAOLB1')));
and we want to remove ‘LC2SQLAOLB2’ because we’re going to do maintenance, then the new TSQL would look like this:
ALTER AVAILABILITY GROUP [AGUP]
MODIFY REPLICA ON
N'LC2SQLAOLB1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LC2SQLAOLB1')));
Notice, in the list of replica servers for the READ_ONLY_ROUTIN_LIST option, the replica server ‘LC2SQLAOLB2’ has simply been omitted.
If we go back and re-execute our query that lists our routing lists, you will see that when LC2SQLAOLB1 is serving as the primary replica, its routing list only has one replica server listed. (It happens to be itself, but it could be any replica server.)
It is important to understand that each replica in an availability group can have its own routing list and order of servers to which routing is defined. Now that we have removed LC2SQLAOLB2 from the routing list defined on LC2SQLAOLB1, there exists only one entry in its list. The routing list defined for LC2SQLAOLB2 still has two servers defined in its list (LC2SQLAOLB1 and LC2SQLAOLB2).
Perhaps we want to change the existing routing list order. There is no need to remove the list or entries from the routing list. The answer is again to use the ALTER AVAILABILITY GROUP syntax to change the order. If our original TSQL statement to create the routing list was:
ALTER AVAILABILITY GROUP [AGUP]
MODIFY REPLICA ON N'LC2SQLAOLB1'
WITH (PRIMARY_ROLE
(READ_ONLY_ROUTING_LIST=('LC2SQLAOLB2','LC2SQLAOLB1')));
and now we wish the routing list order for LC2SQLAOLB1 to be reversed, we simply issue the same TSQL statement but with the list of replica servers in a different order as show here
ALTER AVAILABILITY GROUP [AGUP]
MODIFY REPLICA ON N'LC2SQLAOLB1'
WITH (PRIMARY_ROLE
(READ_ONLY_ROUTING_LIST=('LC2SQLAOLB1','LC2SQLAOLB2')));
Once it has been executed, if we again execute our query to show the routing list, we can see that the priority order has changed from the original:
to this:
In summary, to change the routing list for an Availability Group Listener, issue the ALTER AVAILABILITY GROUP command and specify the READ_ONLY_ROUTING_LIST parameter with the new list of replica servers in the order that you want them.
Consider that these examples only change the routing list for when LC2SQLAOLB1 was acting as the primary replica (as seen by the fact that we specified MODIFY REPLICA ON N'LC2SQLAOLB1') . If you need to change the routing lists for when other replicas are acting as the primary replica, then execute the TSQL commands again but use the other replica names in the MODIFY REPLICA ON N’<otherreplica>’ portion of the ALTER AVAILABILITY GROUP command.
REFERENCE LINKS:
· ALTER AVAILABILITY GROUP TSQL Syntax
· Configure Read-Only Routing for an Availability Group
· Read-Only Routing with SQL Server 2012 Always On Database Availability Groups
Comments
Anonymous
June 23, 2015
I figured that but hadn't found and article about. Thanks a lot, it is brilliant.Anonymous
April 15, 2016
Sorry, but there's a bug. RO routing doesn't work. I can demo it for you if you like. This is a good query - the problem I found, is that when the server is in the Column "When Primary Replica Is" Read only routing does not work. It doesn't matter if you change the Routing list (remove it, etc.). Now, I'm trying to figure out how to remove a Node from that list. I would open a case, but that costs money, and I really don't feel like paying Microsoft to fix it's own products. But I AM willing to demo a repro anytime.