Not too long ago, I had a conversation with a friend about how SQL Server Always On Availability Groups and how they could be configured for Read-Only routing. This is a typical scenario when folks want to leverage their secondary replicas (nodes) for read-only workloads.
This allows you to build an active-active cluster where one node is primary (meaning read-write) and all nodes in the AG (or cluster) can be made to handle your read-only workloads, instead of just being idle/stand-by without putting that hardware to good use. In general, most database system workloads are ~80% read and ~20% write. The cool part about Read-Only routing is how it automatically load balances the Read-Only connections among all of replicas in an AG (or cluster) when configured properly – even if a fail-over occurs.
Here’s some of the questions my friend had before we started the conversation.
- Does a read-only routing list have to be configured? Yes.
- If there’s no routing list setup, is it still going to send the read-only traffic to the secondary? No.
- Is it going to automatically go down the list of servers in the AG? No.
- If a failure occurs while connected to a secondary, will you be re-routed? Yes.
- Is there another fail-over order? Yes.
- Does it automatically decide which node to fail-over onto? Yes.
- If there’s just 2 servers would you setup a routing list? Yes.
- What about in SQL Server 2012, did you create it via T-SQL? Yes.
Note, this was a candid recording and I hope helps you out in case you’re looking to implement similar technology. I redacted some sections of the screen of my demo lab for privacy reasons.
Are you using this Read-Only routing rules? Do you have a better solution or recommendation that you’d like to share, please feel free to comment below.
- SQL Server
- Create and configure an AG in SQL Server
- Configure Read-Only routing for an AG in SQL Server
- The order of automatic fail-over
- Fail-over Policy