How to remove a replica from an AlwaysOn AG routing list?

Hello people,

There are a lot of blog posts about creating readonly routing lists for AlwaysOn AGs out there and some about modifying them, but I failed to spot a blog post about removing a specific replica from a routing list. So this is the reason of this post.

I have a client who has started using AlwaysOn AGs for some time ago. They had their 2 node traditional Failover Clustered Instances before their current AlwaysOn AG replicas. We performed the upgrade side by side. We used two servers temporarily to perform this task. After the upgrade, we set up the old servers from scratch and have configured them as other AlwaysOn replicas. Now there are 5 replicas in this SQL Server 2012 environment. Temporary replicas will be removed soon. But before that, I wanted to see if I can remove the temporary replicas from the readonly routing lists or not?

Here's the result of the routing list (sorry, most of it is blurred):

And there's the query itself:

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

As I said, I wanted to remove a specific replica from this list, but frankly I had not done it before, because I did not need to do so, then I asked Google to find a working method. But it came up with nothing. Maybe it's my bad, but this led me to go to Books Online and I started digging about "ALTER AVAILABILITY GROUP" command. Then I saw the following section:

READ_ONLY_ROUTING_LIST = { ( ‘’ [ ,...n ] ) | NONE }
…

NONE

Specifies that when this availability replica is the primary replica, read-only routing will not be supported. This is the default behavior. When used with MODIFY REPLICA ON, this value disables an existing list, if any.

Then I tested the following code block and that did the trick.

ALTER AVAILABILITY GROUP  

MODIFY REPLICA ON

N'' WITH 

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= NONE));

That specific replica is not on my routing list anymore and it's still part of the AG, everything's working fine.

I know this is not that deep and may look easy peasy, but I wanted to blog about it anyway, so that it may make someone's life easier some day.

Cheers,
Ekrem Önsoy