READONLY ROUTING in Always On.

This query will tell you how to handle READONLY ROUTING in AlwaysOn.
ALTER AVAILABILITY GROUP AvailabilityGroupName
MODIFY REPLICA ON N'NODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP AvailabilityGroupName
MODIFY REPLICA ON N'NODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://NODE1.HADRDOMAIN.COM:1433'));

ALTER AVAILABILITY GROUP AvailabilityGroupName
MODIFY REPLICA ON N'NODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP AvailabilityGroupName
MODIFY REPLICA ON N'NODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://NODE2.HADRDOMAIN.COM:1433'));

ALTER AVAILABILITY GROUP AvailabilityGroupName
MODIFY REPLICA ON N'NODE1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE2','NODE1')));

ALTER AVAILABILITY GROUP AvailabilityGroupName
MODIFY REPLICA ON N'NODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1','NODE2')))
I am providing how read-only routing looks in GUI, but don't compare with above script here as the server names and domains are different. And instead of .com I have used .domain here

And also please note down the below change in the properties of AG replica. We need to set "Application Intent=ReadOnly" for the secondary replica as shown below.

Replica or server names look different from image to image, please ignore it.



























To test Read-only routing connect though Availability Group(AG) Listener name and provide Availability Group database name under "Connect to Database" don't provide 'master' database here. If you provide master database routing only will not work and take you to the Primary replica. So always provide those database names which are part of Availability group databases.









Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server