Environment

-> SQL Server instances as seen in the SQL Server Management Studio,

-> Adding the databases to Availability group,

-> Availability group as seen in SQL Server Management Studio,

-> Configuring ReadOnly Routing List using the below query,

ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://JBSERVER1.JBS.COM:1433'));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://JBSERVER2.JBS.COM:1433'));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER3' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER3' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://JBSERVER3.JBS.COM:1433'));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('JBSERVER3','JBSERVER2')));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('JBSERVER3','JBSERVER1')));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N'JBSERVER3' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('JBSERVER2','JBSERVER1')))

-> The Read-Routing list order is as below,

-> Checking if Read-Routing List is working using SQLCMD. After we use the switch -KReadOnly, the servername returned is JBSERVER3 which proves that the readonly queries are routed to the secondary.

-> Verifying Read-Only list using SQL Server Management Studio,

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.