Configuring Read Only Routing List for AlwaysON Availability Group

Environment

Blog27_1.PNG

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

Blog27_2.PNG

-> Adding the databases to Availability group,

Blog27_3.PNG
Blog27_4.PNG
Blog27_5
Blog27_6.PNG
Blog27_7.PNG
Blog27_8.PNG
Blog27_9.PNG
Blog27_10.PNG
Blog27_11.PNG

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

Blog27_12.PNG

-> 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,

Blog27_13.PNG

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

Blog27_14

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

Blog27_15.PNG
Blog27_16.PNG
Blog27_17.PNG
Blog27_18

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.

2 thoughts on “Configuring Read Only Routing List for AlwaysON Availability Group

  1. Pingback: Reporting Services using AlwaysON Availability Group | JBs Wiki
  2. Pingback: The target database ('JBDB') is in an availability group and is currently accessible for connections when the application intent is set to read only - JBs Wiki

Leave a Reply