Read Routing Features for Always-On Server

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 up to 8 readable secondary replicas. The connections are redirected based on the routing rules.

Always-On and Problem:

The SQL Server Always-On Availability Groups concept was introduced the first time in SQL Server 2012 as an enterprise-level high availability and disaster recovery solution that will replace the database mirroring feature. Always-On Availability Group provides a high availability solution on the group level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas.

In the Always-ON configuration if the Client is having a 2-node cluster. By default, the secondary server will be used for Fail over purpose; that means all the Write queries and Read-Only queries are landing on the Same or Primary server which will lead to performance issues like Blocking, Deadlock, CPU, and Memory high on the primary server. Moreover, the second server or Secondary server is underutilized by the Client as All the application connection to the database server is connecting to the Primary server.

Solution:

SQL Server 2012 Always-On Availability Groups introduces the concept of Read routing by making full use of standby hardware for Read-Only workloads. Not only that, but it also improves performance on the primary database by offloading Read-Only workloads and redirecting them to a secondary replica.

The secondary replicas can also be configured as an active readable secondary to allow Read-Only access to all secondary databases, as the data in the secondary databases is near real-time data.

Setting the readable secondary as Read-Intent Only will allow the secondary server to serve the Read-Only workload only if the connection string of the application contains the Application Intent = "ReadOnly" parameter.

Connections with Application Intent value equal to "ReadOnly" can be enforced to secondary replicas using a new Always On Availability Group feature called Read-Only Routing.

Benefits:

• The workload for Read and Write queries will be distributed among the Primary and Secondary server.
• Proper Utilization of Both Primary and Secondary servers.
• Performance-related to Blocking and Deadlock will decrease.
• Performance of the Queries and server will increase.

Implementation Of Read-Only Routing in Always-On:

The first thing that we need to do is configure the secondary replica to allow Read-Only workload. This can be done by modifying the  Readable Secondary option in the Availability Group Properties window

There are three different options under the  Readable Secondary drop-down menu.

• No: This is the default value. This means that the database does not allow Read-Only workloads when running as a secondary role. Any attempts to run SELECT statements in this database will return an error similar to that of when running queries against a database mirror.
• Read intent only: This setting allows Read-Only workloads when running as a secondary role only if the application connection string contains the parameter: Application Intent = Read-Only.
• Yes: This setting allows Read-Only workloads regardless of whether or not the application connection string contains the parameter: Application Intent = Read-Only.

Once the Readable Secondary option has been configured, you can test this by running your Read-Only applications against the secondary replica. Be sure to add the connection string parameter Application Intent = Read-Only  in your applications.

Enabling Read Routing URL:

To configure Read-Only routing, we need to do the following on our Availability Group.

• Specify a read_only_routing_url: A read_only_routing_url is the entry point of an application to connect to a readable secondary. It contains the system address and the port number that identifies the replica when acting as a readable secondary. This is similar to the endpoint URL we specify when configuring database mirroring.
• Specify a Read-Only routing list on all replicas: For each replica that will act as the primary, we need to define the corresponding secondary replicas that will act as the routing target for Read-Only workloads. This means that if the replica is acting as a primary, all Read-Only workloads will be redirected to the replicas in the Read-Only routing list.

Conclusion:

Always-On features are widely used by Major organizations like banking, Infrastructure, Retail, Healthcare, etc. The heavy Read\write transaction is running on the database servers by each organization. All the Organization are using Synchronous configuration which contain the real-time data with primary server on a secondary server.

Read-Only routing is features that help the organization to distribute the load of Write and read into a different server. The same connection string (listener name) will be used for both Read\Write applications except for the additional Application Intent = Read-Only parameter in one of them. Because of this parameter, the application gets redirected to the replica defined in the Read-Only routing list - ALWAYSON-AG2 - when ALWAYSON-AG1 acts as a primary replica. When we fail over the Availability Group, the application automatically gets redirected to the new secondary replica without making changes to the application. This makes the fail over and fail back process very seamless with the applications.