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.
SQL Server 2016 supports up to eight replicas. By default, the secondary replicas do not allow any workload, which is the same as the secondary party of the SQL Server Mirroring site, where the replica will be used only for failover purposes in a disaster recovery situation. 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. In this way, the queries that will perform read-only processes that are directed to the Always On Availability Group listener will be redirected to the secondary replicas instead of the primary replica.
In SQL Server 2012 and 2014 versions, the read-only workload redirection process will redirect the queries specifically to the first secondary replica defined in the routing list, unless it was not accessible, and then it will direct the connection to the next replica in the routing list. The other secondary replicas will not participate in serving the read-only workload, limiting the load balancing mechanism to only one replica. As a workaround, the Read-Only routing list can be updated periodically, so that you will make sure that all replicas will be used to serve the read-only workload. SQL Server 2016 removes this limitation by introducing the native load balancing mechanism, as we will see later.
Assume that we have configured the Availability Group AG40VS that contains three replicas: DB41VS, DB42VS, and DB43VS. The DB41VS SQL Server is configured as the primary replica, and the DB42 and DB43 SQL servers are configured as readable Secondary replicas. Both DB41VS and DB42VS are configured for Automatic Failover.
In order to create the Read-Only Routing list, we should check first that an Availability Group Listener is configured, as the read-only client will direct the connection requests to the Availability Groups listener. We can do that by querying the sys.availability_group_listeners DMV and join it with the sys.availability_groups DMV to get the Availability Group name as follows:
|
1 2 3 4 5 6 7 |
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 = AVGLis.group_id |
The result in our case will show us that the Availability Group listener is already configured as below:
The second prerequisite for creating a Read-Only Routing list is that at least one secondary replica should be configured for read-only access, where it can be set to allow all connections for read-only access or only the read-intent connections. Expanding the AlwaysOn High Availability node from the SQL Server Management Studio, right-click on the Availability Group, and choose Properties. From the Availability Group Properties window, change the Readable Secondary properties of each secondary replica with YES value to allow all connections for read-only access or Read-Intent Only to allow only the read-intent connections. In this demo, we will allow only the read-intent connections as follows:
Now we are ready to configure Read-Only Routing. Read-Only Routing allows SQL Server to route the incoming read-intent connections that connect to the availability group listener to be served by the available readable secondary replica. To support that, the readable secondary replica should have a read-only routing URL that works when the replica is working as a secondary replica. The read-only routing URL consists of the system address or the port number that defines the readable secondary replica, which is similar to the endpoint URL that is used when configuring the SQL Server Mirroring. 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. In this way, the read-only routing URL is defined on a replica-by-replica basis. The ALLOW_CONNECTIONS property of the read-only routing can be set to READ_ONLY or ALL.
Unfortunately, until SSMS 17.4, there is no way via the GUI to define Read-Only Routing using SQL Server Management Studio. It can be defined through T-SQL or PowerShell commands only. The T-SQL script below modifies each replica from our Always On Availability Group to allow Read_Only workload when it acts as a secondary replica and defines the read-only routing URL for each replica:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB41VS' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB41VS' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DB41VS.test.com:50000')); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB42VS' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB42VS' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DB42VS.test.com:50000')); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB43VS' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB43VS' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP:// N'DB43VS'.test.com:50000')); |
The sys.availability_replicas DMV can be used to review the configured read-only routing URL for each replica as below:
|
1 2 3 4 5 6 |
SELECT replica_server_name , read_only_routing_url , secondary_role_allow_connections_desc FROM sys.availability_replicas |
The result in our case will be like:
For each primary replica, you should define at least one secondary replica that will work as the routing target to which the read-only workloads will be redirected. These secondary replicas that will server these redirected read-only requests can be defined in the Read-Only Routing List, that will be considered only when the replica is running under the primary role. The below T-SQL script is used to define the read-only routing list for each replica when acting as primary replica. For example, if the DB41VS SQL Server is the primary replica, the read-intent only workload will be redirected to the readable secondary replicas; the DB42VS and DB43VS consequently:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB41VS' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB42VS','DB43VS'))); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB42VS' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB43VS','DB41VS'))); ALTER AVAILABILITY GROUP [AG40VS] MODIFY REPLICA ON N'DB43VS' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('DB42VS','DB41VS'))); |
The Always On Availability Group read-only routing list can be checked using the sys.availability_read_only_routing_lists DMV, that returns the read-only routing list of each Availability Group replica in an AlwaysOn Availability Group, joined with the sys.availability_replicas and sys.availability_groups DMVs as follows:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
The Read-Only Routing List with the routing URLs and routing priorities in our case will be like:
The last point to check before starting our tests is that the replica to which the read-only workload is directed is in synchronized or synchronizing state. The synchronization status can be checked from the Databases node of the SQL Server Management Studio as follows:
Now the Read-Only Routing is fully configured and the secondary replicas are ready to receive the read-only workload. From the application side, the application connection string should be modified to set the Application Intent property value to “ReadOnly”, so that the connection requests that come from that application will be classified as read-intent requests. An example of connection string that specify the application intent type to ReadOnly is shown below:
Server= tcp:AG40VS,50000; Database=test; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;
Starting from SQL Server 2012, the ApplicationIntent ODBC connection string keywords was added to support Always On Availability Groups in SQL Server Native Client. If your application uses an ODBC connection string to connect to the SQL Server, you will be able to specify the Application Intent value to ReadOnly from ODBC Data Source configuration, which works same as adding the “ApplicationIntent=ReadOnly” value to the connection string. In this way, the read-only workload will be directed to the readable secondary replicas as follows:
Assume that you manage to use the secondary replicas to run your read-only queries, in order not to affect the performance of the primary replica. If you try to connect to the DB42VS secondary replica and run a simple SELECT statement from the TEST database, an error will be generated showing that this database is only available for the ReadOnly application intent connections as below:
To overcome this problem, we need to pass the ApplicationIntent=ReadOnly parameter to Additional Connection Parameters screen in SSMS, which will be shown when you click on the Options button from the Connect to Server dialog as follows:
Adding this connection parameter specifies that the connection requests that come from SSMS will be classified as read-intent requests. If you try to run the same previous SELECT statement, the query will be executed successfully retrieving the requested data from the secondary replica without affecting the primary replica performance as below:
As configured previously in the read-only routing lists, the read intent workload will be redirected to the DB42VS secondary replica when the DB41VS is the primary replica, and redirected to the DB43VS when the DB42VS is the primary replica.
Let us test that workload redirection practically using the sqlcmd tool. To do that, we should specify the ReadOnly value for the –K application intent parameter. We should also provide the Availability Group Listener name in the –S parameter and the Availability Group database name in the –d parameter.
In our current Availability Group setup, the DB41VS SQL Server is the primary replica. Running the below SQL CMD command:
sqlcmd -S AG40VS,50000 -E -d test -K ReadOnly
The result will show us that the current server that is receiving my ReadOnly application intent workload is the first secondary replica in the read-only routing list, which is the DB42VS SQL Server as follows:
Let us perform a failover for the Availability Group to the DB42VS replica:
Now, the DB42VS is the primary replica. If we run the same previous SQL CMD command:
sqlcmd -S AG40VS,50000 -E -d test -K ReadOnly
The result will show us that, the server that is serving my ReadOnly application intent request is the first secondary replica in the read-only routing list, which is the DB43VS SQL Server as below:
We mentioned previously that, only the first secondary replica from the read-only routing list will receive the ReadOnly application intent connections, and the other secondary replicas will not participate in serving that read-only connections. To resolve that issue, SQL Server 2016 introduced load balance lists in the read-only routing list.
Let us modify the read-only routing list of the DB41VS SQL Server that acts as primary replica to use the load balance list below:
|
1 2 3 4 5 |
ALTER AVAILABILITY GROUP AG40VS MODIFY REPLICA ON N'DB41VS' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DB42VS', 'DB43VS'), 'DB41VS'))); |
This new read-only routing list has two routing lists: the first list contains the DB42VS and DB43VS secondary replicas and the second list contains the DB41VS replica only. The first received read-only connection will be served by the DB42VS replica, and the second read-only connection will be routed to the DB43VS replica, with a round-robin distribution of the read-only connections between these two replicas. If one of the secondary replicas in the first read-only routing list is not available, the read-only connections will be received only by the available replica of the first read-only routing list. The second read-only routing list will start receiving read-only connections when all replicas in the first read-only routing list become unavailable.
Having the DB41VS SQL Server acting as the primary replica. If we run the previous SQL CMD command again two times:
sqlcmd -S AG40VS,50000 -E -d test -K ReadOnly
The result will show us that, the first read-only request will be served by the DB42VS secondary replica and the second read-only request will be served by the DB43VS secondary replica as below:
Conclusion
SQL Server Always On Availability Groups is an enterprise-level high availability and disaster recovery feature that provides a high availability solution on the databases groups level. By default, the secondary replicas refuse any read-only workload unless you configure it to receive all connections for read-only access or only read-intent connections. Read-Only Routing, which is not configured by default, allows the read-only requests that are directed to the Always On Availability Groups listener to be served by the secondary replicas instead of the primary replica. This can be performed by configuring the read-only routing URL for each replica and defining the read-only routing list for these replicas. SQL Server 2016 resolved the load balancing problem by defining the new load balance list in which all the secondary replicas from a read-only routing list can handle the read-only workload, rather than directing the workload to the first secondary replica in the read-only routing list in the prior SQL Server versions. The examples in this article showed us practically how all of that works.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021