Read-only Routing for an Always On

As DBAs, we generally come across our clients complaining that the current Production Server is not able to hold the load on the server and whether the load may be balanced with the Secondary Server. This is possible with a database in DR Server with Read-only database in Log Shipping and Secondary SQL Server replicas in Always On Availability Group. The biggest advantage of Always On Groups is that it allows us to set up group level HA for any number of databases and we can create up to four secondary replicas and this is a combination of Clustering, Log Shipping and Database Mirroring where the data transmission is more flexible and functional.

Always On Readable Secondary Replica has a feature to handle particular read-only connection requests called Read-only routing. Generally, by default, both read and read-intent are directed to the Primary replica and nothing is intended for the secondary replicas. Now, the secondary replicas can be not just used for Backup, DBCC and reporting purposes but also can be queried in the future by using ‘ReadOnly’ as their Application Intent in the application connection string.

We have three replicas SQL1, SQL2 and SQL3 in the Windows failover cluster. Each node has a standalone SQL Server 2012 instance installed and configured with Always On AG. We are always on AG Group named “CODEAG” with the listener name “CODELIS”

In the following screenshot, SQL1 is a primary replica, SQL2 is a secondary replica, SQL3 is a secondary replica.

How to Configure Read-only routing list in always on availability groups

Step1:

Connections are made to the Availability Group using the Listener name or IP. Now, to create multiple listeners for one availability group, please follow the below procedure.

To manually create or configure a listener for an availability group 

1. Under Object Explorer, connect to the instance that holds the primary replica of the availability group.
2. Expand the AON Group and click the Availability Group for which we are trying to manually configure the listener and proceed.
3. Right-click the Availability Group Listeners Node and select New Listener Command. This opens a new dialog box for configuring a listener.
4. Port Number of an existing listener can be changed by expanding the Availability Group Listeners Node followed by right-clicking the listeners and selecting the properties.
5. Now, enter the new Port Number and click OK.

Identify the listener name configured for Always On replication by querying DMV as below.

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 following screenshot, the AG group name is CODEAG and AG listener IP is CODELIS.

Step 2:

To configure read-only routing, we need to configure the replicas to read-intent only to allow read-only connections to secondary replicas.

1. Connect to the instance that holds the Primary replica.
2. Expand the AON High Availability Node, then AG Group Node.
3. Click the AG Group whose replica should be changed.
4. Right-click the replica and select properties to change the connection access for the Primary and Secondary roles as follows.

The Secondary role has a new value from the readable secondary drop list.

Read-Intent only

This option allows reading access of the secondary databases of this replica. Only read-only connections are allowed.

Yes

This option allows to read access only but all the connections are allowed for the secondary replica.

No

This stops all the user connections to the secondary replica and doesn’t even allow you to read.

Set the readable secondary properties to Read-intent only.

In the following screenshot, the Readable Secondary properties of each secondary replica are set to Read-intent only.

Step 3:

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 for all of the replicas in our Availability Group.

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

ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL1.abc.com:17999'));

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


ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL2.abc.com:17999'));

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

ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL3' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL3.abc.com:17999'));

Step 4:

For the replica that we are marking as read-only routing when it is the primary replica, there is a need to specify a read-only routing list and this gets effected only when the local replica is running under the primary role.

ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL2','SQL3')));

ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL3' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL2','SQL1')));

ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3','SQL1')));

In the above script, the example when SQL1 is the primary replica, the read-intent only workload will be redirected to the readable secondary replicas; the SQL2 and SQL3 respectively: similarly, if SQL3 is the primary replica, the read-intent only workload will be redirected to the readable secondary replicas; the SQL2 and SQL1 respectively.

Read-only directed traffic will be routed to the first available replica until and unless it is not accessible it would direct the traffic to the next available replica in the routing list. When you have more than one replica, it is not possible to share the load between replicas till SQL Server 2012 and 2014. But, SQL server 2016 allows you to balance the load between read-only replicas.

Example:

ALTER AVAILABILITY GROUP [CODEAG]
MODIFY REPLICA ON
N'SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL3','SQL1'), ‘SQL2’)));

This routing list behavior ‘load balances’ read-only connections between SQL3 and SQL1. Above, we have two embedded lists in the routing list:

List 1: ‘SQL3’, ‘SQL1’

List 2: ‘SQL2’

Route to the replicas in the first list. SQL3 and SQL1 are accessible to read-only connections. The first incoming read-only connection will be routed to SQL3, the second read-only connection will be routed to SQL1, the third read-only connection will be routed to SQL3, the fourth read-only connection will be routed to SQL1, and so on, with a ‘round-robin’ distribution of read-only connections between the two replicas in the first list.

If any replicas become unavailable, routing will continue with remaining replicas in the first list. If SQL3 or SQL1 becomes inaccessible to read-only connections, then the read-only connections will only be routed to the accessible read-only replicas in the first list. For example, if SQL3 is in a not synchronized state or ALLOW_CONNECTIONS is set to NO, then all read-only connections will be routed to SQL1. So long as one of the servers is available for read-only connections, NO read-only connections will be routed to SQL2.

If all replicas in the first list are inaccessible, route to replicas in next list. If SQL3 and SQL1 become inaccessible to read-only connections, then all read-only connections will only be routed to the next list of replicas, which in this case is SQL2.

Resume routing to the first list if any replicas become available. As secondary replicas that have higher priority in the list become accessible to read-only connections, future read-only connections will connect to them as appropriate.

In SQL Server 2016, you can configure load-balancing across a set of read-only replicas.

Step 5:

sys.availability_read_only_routing_lists DMV, that returns the read-only routing list of each Availability Group replica in the Always On Availability Group.

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 following screenshot, the result shows routing URL and routing priority.

Step 6:

To test read-only routing using SQLCMD, use –K ReadOnly parameter that shows secondary replica receiving read connections as per the routing list.

In the following screenshot, the secondary replica is accepting the read connections i.e… SQL2.

Step 7:

Failover the availability group and test read-only routing.

1. In Object Explorer, connect to a server instance that hosts a secondary replica of the availability group that needs to be failed over. Expand the server tree.
2. Expand the AlwaysOn High Availability node and the Availability Groups node.
3. Right-click the availability group to be failed over, and select Failover.

Now, the SQL2 becomes primary replica and connections are handled by SQL1.

The Connection String Syntax that application should use will depend on the SQL Server Provider.

If it is .Net Framework Data Provider 4.0.2 for SQL Server, the syntax will be as follows:

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

Conclusion:

To bring down the workloads, this Read-only routing remains the best option. An application involving SQL Server Reporting Services hosted in SharePoint or the Native Mode installation of Report Server can use Read-only intent that avoids typical blocking, Memory and CPU Usage on Primary Nodes.