Standard

Posted by

Posted on

September 27, 2017

Posted under

AlwaysON

Comments

Leave a comment

AlwaysON – How to disable Read-Only routing for an Availability Group

-> We have configured Read-Only routing list for an availability group. Now the requirement changes and the Read-Only routing needs to be disabled. If you have a scenario like this in your hand, then this post is for you!

-> Please refer article “https://jbswiki.com/2017/09/06/configuring-read-only-routing-list-for-alwayson-availability-group/” to create read-Only routing list for you Availability group.

Environment

Blog29_1

-> Looking at SSMS,

Blog_34_1.PNG

-> Checking the Read-Routing List that is setup currently on the Primary Replica using below query,

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
Blog_34_2.PNG

-> Trying a connection with Switch -KReadonly to see if it Read-Routing list works and connects to JBSERVER3.

Blog_34_3.PNG

-> We will Disable the Read-Routing list using below query,

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'JBSERVER1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'JBSERVER2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'JBSERVER3' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));

-> Checking the Read-Routing details on the Primary Replica after it is removed,

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
Blog_34_4.PNG

-> It is evident from the above screenshot that the Read-Routing listi ndeed is removed. Lets check what happens if we connect the listener using -KReadonly switch,

Blog_34_5.PNG

-> It connects to the primary Replica. this proves that read-only routing is indeed disabled.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply