Introduction

AlwaysOn Availability Groups are a great way to provide high-availability and disaster-recovery for your clients.  An Availability Group provides a read-write primary database, and up to eight corresponding secondary databases.  Unlike mirroring, Availability Group secondary replica databases are available for read-only operations.  Clients doing read-only operations can be routed to the secondary replica, thereby offloading work and improving performance on the primary replica.

Read-Only Routing

Read-Only routing is accomplished via the ApplicationIntent property in the connection string.  When this property is set to ApplicationIntent=ReadOnly, SQL Server will route that connection according to the configuration of the availability group, which can route to a secondary replica database.

Server=tcp:Avg1,1433;Database=AdventureWorks;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly

As an example, the following routing can be configured. I retrieved ths data via the below query.

select
 ag.name as "Availability Group"
 , ar.replica_server_name as "Source Replica"
 , ar2.replica_server_name as "Read-Only Destination"
 , rl.routing_priority as "Routing Priority"
 , ar.secondary_role_allow_connections_desc as "Allowed Secondary Role"
 , ar2.read_only_routing_url as "Read-Only Routing Url"
from sys.availability_read_only_routing_lists rl
 inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
 inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
 inner join sys.availability_groups ag on ar.group_id = ag.group_id
order by ag.name, ar.replica_server_name, rl.routing_priority

With the above connection string, the primary database processes the incoming read-only request and attempts to locate an online secondary replica, if found the client connects to the identified read-only replica.

But if your clients are not using a provider that supports the ApplicationIntent keyword, routing doesn't happen and you will be connected to the primary database.

An Alternative Read-Only Routing Technique

ApplicationIntent=ReadOnly works well when configured properly, however it is only available for Microsoft based clients with data providers that implement support for the ApplicationIntent property.

To take advantage of secondary read-only replicas for Python or other non-Microsoft clients, you’ll have to connect to the read-only replica directly.  But that has a big disadvantage since that replica could become un-healthy or auto-failover and become the new primary.  This takes away from the high-availability we intend to provide.

A more dynamic approach can be established with a read-only pool using a Load Balancer. As an example, a pool can be made up of the following nodes.  These nodes can accept read-only traffic in round robin or other load balanced techniques.  What we have to determine next is how to mark a node up or down dynamically based on whether it’s a healthy secondary replica.

The following query provides a summary of all the nodes in the Availability Group and their status.

select
 ag.name as "Availability Group"
 , rcs.replica_server_name as "Node"
 , rcs.join_state_desc as "Join State"
 , rs.role_desc as "Role"
 , rs.synchronization_health_desc as "Health"
 from sys.dm_hadr_availability_replica_cluster_states as rcs
 join sys.dm_hadr_availability_replica_states as rs on rs.replica_id = rcs.replica_id
 join sys.availability_groups as ag on rcs.group_id = ag.group_id

So we have a way now to determine if a node should be up in our Load Balanced pool.  What we need to do next is implement a health check for each node.  The health check connects to the SQL Server hosted on the given node and executes the below query, which checks if the node is healthy and in a secondary role.  This should be wired up as a health check in the Load Balancer for each node in the read-only pool.  Once in place the pool is dynamically managed and only secondary replicas receive connections via this pool. If a failover occurs changing the primary node, the health check will automatically adjust the pool accordingly without any manual intervention.

--- health check for read-only node, [count(*) == 1]
select count(*)
from sys.dm_hadr_availability_replica_cluster_states as rcs
join sys.dm_hadr_availability_replica_states as rs on rs.replica_id = rcs.replica_id
where rs.role_desc = 'SECONDARY'
and rs.synchronization_health_desc = 'HEALTHY'
and rcs.replica_server_name = '${node_name}'

An example of a health monitor rule in an F5 Load Balancer.

Conclusion

AlwaysOn Availability Groups are a great improvement to high-availability and disaster-recovery solutions.  Read-only work can be offloaded to secondary replica improving performance on the primary database.  With a little more setup this offloading mechanism can also be established for non-Microsoft clients with automatic failover detection.