This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
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?
asked 2014-06-27 by user235389
Answer
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.
I’ve written a procedure for my DBA database that can simplify configuration of read-only routing in certain cases. Specifically, I wrote this procedure when I was at Stack Overflow to simplify our process for ensuring read-only routing configuration is correct after maintenance or a failover.
answered 2015-12-28 by Andy Mallon