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.

Advertisements

One thought on “Configuring Read Only Routing List for AlwaysON Availability Group

  1. Pingback: Reporting Services using AlwaysON Availability Group | JBs Wiki

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s