As the topic itself is shouting the description we hardly require an explanation on it.But yes for those who are new to this concept emerged in SQL 2016 and later versions of SQL server with some interesting value added feature is covered in this article to add a new confidence in you as a DBA.
Read routing is basically to forward the read only requests to the server’s stand by or secondary replica (As named in AG 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 (that is, a replica that is configured to allow read-only workloads when running under the secondary role).
Mandatory:
The client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests. Follow below procedure to configure while making connection:
NOTE:
You must set the read-only routing URL of a replica before configuring its read-only routing list.
Prerequisites
· The availability group must possess an availability group listener. For more information.
· One or more availability replicas must be configured to accept read-only in the secondary role
Load-balancing across read-only replicas: SQL 2016 introduced load balancing feature in which the read only connections can be modified to any replica replacing the old method in which the connections were forwarded to the first available replica.
NOTE:
One more interesting feature came from this functionality the we can force to rout the connection between set of replicas using SINGLE PARENTHESES: ” ( )”
Code 1:
READ_ONLY_ROUTING_LIST = (‘Server1’,'Server2’, 'Server3’,…,…,…,…, 'Server8’) Modified to make replica set as below:
Note: Each entry in the routing list can itself be a set of load-balanced read-only replicas. The above example demonstrates this.
Code 2:
represents the configuration that it will rout the read only connection to server1/2, in case if both are not online the connections will be forwarded to next provided replica.
Example (Transact-SQL)
The following example modifies two availability replicas of an existing availability group, AG1 to support read-only routing if one of these replicas currently owns the primary role. To identify the server instances that host the availability replica, this example specifies the instance names—COMPUTER01 and COMPUTER02.
when all the replicas are offline and only primary is online:
ALTER AVAILABILITY GROUP AG_Name MODIFY REPLICA
ON N'Replica1’
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('Replica2’, 'Replica3’), 'Replica1’)));
Acknowledgement:
!! Details of SPECIAL CASE:!!
In one of our encounter with the failover of AG group the read routing was not working since after failover the database took time to come online on secondary. This caused the read only connections to fail for reporting purpose.
That’s what struck us that we did not consider this situation where only primary is online, so the read request must reach to primary then we configured this condition and resolve the issue.
NOTE: Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.
No comments:
Post a Comment