One of my clients came up with the requirement to load balance the traffic coming from application to secondary SQL Server replicas in an Always On Availability Group. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica. The advantage of setting the readable secondary as Read-Intent Only is to allow the secondary server to serve the read-only workload, though only if the connection string of the application contains Application Intent=Readonly parameter. 

In my scenario, we have three replicas: DB1, DB2 and DB3 in a Windows Failover Cluster. Each node has a standalone SQL Server 2012 instance installed and configured with an Always On AG. We have the AG Group named “AGTEST01” with a listener name of “AGLIS01”. In the below screenshot, the DB1 is the primary replica, DB2 is the secondary replica, and DB3 is the secondary replica.

How to Configure Readonly routing list in always on availability groups

Connections are made to the Availability Group using the listener name or IP. Identify the listener name configured for Always On replication by querying the DMV with this code.

SELECT AV.name AS AVGName
 , AVGLis.dns_name AS ListenerName
 , AVGLis.ip_configuration_string_from_cluster AS ListenerIP
FROM sys.availability_group_listeners AVGLis
INNER JOIN sys.availability_groups AV on AV.group_id = AV.group_id

In the below screenshot, the AG group name is AGTEST01 and the AG listener name is AGLIS01

To configure read-only routing we have to configure the replicas to be read intent only to allow read only connections. Right click on the Availability Group and select properties. On the  general tab, change the readable secondary properties to Read-intent only. In the below screenshot, the Readable Secondary properties of each secondary replica is set to Read-Intent Only

Each readable secondary replica can be assigned a read-only routing URL that will be used for routing read-intent connection requests to a specific readable secondary replica. Use T-SQL to specify a read-only routing URL's for all of the replicas in an Availability Group.

ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB1' WITH
 (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB1' WITH
 (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DB1.abc.com:17999'));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB2' WITH
 (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB2' WITH
 (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DB2.abc.com:17999'));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB3' WITH
 (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB3' WITH
 (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DB3.abc.com:17999'));

For each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. A given read-only routing list takes effect only when the local replica is running under the primary role. This list must be specified on a replica-by-replica basis, as needed.

ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB1' WITH
 (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB2','DB3')));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB3' WITH
 (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB2','DB1')));
ALTER AVAILABILITY GROUP [AGTEST01]
 MODIFY REPLICA ON
 N'DB2' WITH
 (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB3','DB1')));

The sys.availability_read_only_routing_lists DMV returns the read-only routing list of each Availability Group replica.

SELECT   AVGSrc.replica_server_name AS SourceReplica
 , AVGRepl.replica_server_name AS ReadOnlyReplica
 , AVGRepl.read_only_routing_url AS RoutingURL
 , AVGRL.routing_priority AS RoutingPriority
 FROM sys.availability_read_only_routing_lists AVGRL
 INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
 INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
 INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
 ORDER BY SourceReplica

In the below screenshot are the results showing routing URL and routing priority.

Test read only routing using SQLCMD. Use the below statement in SQLCMD with the –K Readonly parameter, along with the listener name and the database name in the Availability Group. The output shows the secondary replica receiving read connections according to read-only routing list.

In below screenshot, the secondary replica is accepting the read connections, i.e... DB2

Failover the availability group and test readonly routing. Now the DB2 becomes primary replica and connections are served by DB1

The syntax for a connection string depends on the SQL Server provider an application is using.  The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing.

Server=tcp:MyAgListener,portnumber;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Linkedin