How to configure Read-Only routing on SQL Server 2016 Always On Availability Group (AG)?

The great advantage of Always On Availability Group over other availability solutions is their ability to scale-out read operations (SELECT queries). Read-only routing is a feature of Always On Availability Group, which redirects connection requests from applications into readable secondary. In SQL Server 2016 Always On Availability Group, you can configure upto 8 readable secondary replicas.

The connections are redirected based on the routing rules. Always On Availability Group provides the following options to define the rules:

• Read-Only Routing URL
• Read-Only Routing List

Before defining the routing rules, we must understand the following conditions:

The application must connect to the Virtual Network Name (VNN) and not to the secondary replica directly. VNN is defined at the time of configuring listener.

The application connection string must contain the read-only connection parameter, ApplicationIntent=ReadOnly;

There must be at least on readable secondary exist on the AG. Let’s configure the routing rules:

Read-only Routing URL:

The URL is used when an application explicitly trying to connect to readable secondary with read-only intent. This URL contains the hostname and port number.

Format : TCP://server.domain.com:1433
Example : TCP://node1.ms.com:1433 (note: this on node1.ms.com)

This rule is applicable only when the node is acting as a secondary replica (if it is primary, obviously it will accept, read and write connections).

Configure read-only routing URL using T-SQL:

ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON
N’node1’
WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://node1.ms.com:1433’));
GO

Read-Only Routing List:

The read-only routing list contains the list of readable secondary with their priority. For example, node1.ms.com is the primary replica, and when an application is trying to connect to AG with explicit read-only intent, the primary replica will redirect the read-only connection to available secondary replicas as defined on the routing-list.

Format : ‘replica1’,’replica2’
Example  : ‘node2’,’node3’ (note; on node1.ms.com)

Configure read-only routing list using T-SQL:

ALTER AVAILABILITY GROUP [TestAG] 

MODIFY REPLICA ON 

N’node1’ 

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (N’node2’, N’node3’))); 

GO

Note the role, it is when node1.ms.com server is in primary role, it will take effect. Now you have configured the routing URL and routing list, follow the below steps to verify it is working as expected.

Steps to verify Read-Only routing:

In SSMS login, enter server name as Always On AG listener and go to options and find the additional connector parameters.

Enter the below connection parameters and click on the connect button.

ApplicationIntent=ReadOnly; InitialCatalog=databasename;

Open new query window and see the server name to identify, currently which server it is connecting.

SELECT @@SERVERNAME;

The output should be name of Read-only Secondary Replica. 

At the Read-Only Secondary Replica, if you try to update tables, you will get the error message stating, you cannot perform DML operation on secondary replica.

If you are not getting the Read-Only Secondary replica, mention it on comment section, I will be glad to help you and get back to as soon as possible.

Load-balancing across Read-Only Secondary Replicas:

Starting from SQL Server 2016, you can configure the load balancing across the read-only replicas.  Load balancing can be configured as below:

ALTER AVAILABILITY GROUP [TestAG] 

MODIFY REPLICA ON 

N’node1’ 

WITH 

(

PRIMARY_ROLE 

(

READ_ONLY_ROUTING_LIST= ((’node1’, ’node2’,’node3’), ‘node4’, ‘node5’)); 

));

GO

Load-balancing performed using Round-robin algorithm, and connection requests are load-balanced between node1, node2 and node3. Next priority will be node4 and the last priority will be node5.

I hope this article helps you in configuring the read-only routing, if you have questions or doubts, please mention it on comment section.