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.
Video:
References:
- SQL Server
- Create and configure an AG in SQL Server
- Configure Read-Only routing for an AG in SQL Server
- sqlcmd
- The order of automatic fail-over
- Fail-over Policy
- SSMS
Thx,
Hiram
Love how you present! The speaking speed is correct, along with the tone of voice and the way you go through demos and explain 🙂 Always a pleasure watching/attending your presentations ! Thx!
Just these 2 things things: the sound at the beginning didn’t let me hear you well, and not sure if it’s my computer, but I can’t read anything, I zoomed in, but still the same.
By the way, what software did you use to record this video?
LikeLiked by 1 person
The capture is recorded with Zoom the screen share program and I am using a Microsoft mic during the Zoom meeting. Then I edited with the native windows 10 app that does Edit pictures and videos. The blurring was done on youtube, no special software required. Youtube also has a video editor that seems promising on their website called Youtube Studio.
Yes, to be able to read you click on the youtube Gear icon and set it to 1080p cause they automatically adjust the quality down to stream depending on your bandwidth.
Also, Thank you so much for the feedback. Anything helps. I’m glad to hear I’m doing a good job.
Sincerely,
HF
LikeLike
[…] Source: SQL Server Read-Only Routing […]
LikeLiked by 1 person