Spread workload with always on high availability groups

Question

I am wondering if it is possible to split up a very large query to have parts of it done on the secondary read only servers, or If this feature only allows me to run separate queries or scripts on the secondary servers. I would also like to know if anyone is using AlwaysOn High availability groups for this purpose, how is it working for you?

Answer 1

Clustering and high availability is used to minimize downtime, not provide load balancing. A quote from this article says

"Clustering is not a solution for load balancing either. Load balancing is when many servers act as one, spreading your load across several servers simultaneously. Many DBAs, especially those who work for large commercial websites, may think that clustering provides load balancing between the cluster nodes. This is not the case; clustering helps improve only uptime of SQL Server instances. If you need load balancing, then you must look for a different solution. A possibility might be Peer-to-Peer Transactional Replication."

You can use a second reporting server to do expensive queries. We did that by using transactional replication to move all applicable data to a second server that was used for reporting only. However, you will have to specify the server you are querying, so it is not a simple solution where SQL decides what server to query on based on workloads.

Answer 2

Availability Groups allow you to offload read-only queries to a secondary read-only replica, but this offloading is done at the connection level. As such, you would not be able to have only part of a query run on the read-only replica. Additionally, you cannot have different queries from the same connection routed to different replicas(servers).

You must use completely separate connections (with different connection string attributes) to have some queries run on the primary read/write replica, and others run on the secondary read-only replica. With SQL Server 2012, an ApplicationIntent attribute was added to the supported connection string format for the SQL Server Native Client. In this attribute, you can specify if your connection's intent is for ReadWrite or ReadOnly data access. Details are in the BOL article on SQL Server Native Client Support for High Availability, Disaster Recovery.

However, in order for the server to know what to do with this connection string attribute, you must first configure read-only routing. This configuration is required for the Availability Group listener to properly direct connections to either the primary read/write replica, or a secondary read-only replica. Details are in the BOL article on how to Configure Read-Only Routing for an Availability Group.