AlwaysOn Readable secondary

Set up AlwaysOn to use readable secondaries and have the readers automatically connect to the secondary.

You need a couple of things done, nope it’s not totally automatic, first the developers have to decide which SQL they want/can run on a readonly database, only SELECT’s will work so they have to be careful.

When they have found the SQL’s that will work the connections for them will have to have an added parameter applicationintent=readonly

The thing you as a DBA have to do is configure AlwaysOn to actually route the readonly connections to the secondary.

See below for an example:

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER1' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER1' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER1.domain.com:1433'));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER2' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER2' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER2.domain.com:1433'));

ALTER AVAILABILITY GROUP [SQLAG] 
MODIFY REPLICA ON
N'SERVER1' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER2','SERVER1')));

ALTER AVAILABILITY GROUP [SQLAG] 
MODIFY REPLICA ON
N'SERVER2' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER1','SERVER2')));
GO

Check that the routing list looks ok with

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

I’ll write more later on how to detect how far behind the secondary is.

Leave a Reply

Your email address will not be published. Required fields are marked *