At SSG, we work with Always On availability groups (AGs) every day. Most of our clients who have implemented AGs need the high availability that they provide, as well as the ability to offload much of the reporting/read-only workloads to the read-write node. To accomplish this, SQL Server provides the ability to configure read-only routing in availability groups.
With this, you can route connections specified as read-only to a secondary replica configured as a secondary role. Not too long ago, you had to configure read-only routing using T-SQL or PowerShell. Anyone that has done read-only routing using T-SQL knows that it can be a bit tricky to get it right. However, it wasn’t too long ago that Microsoft added the capability to configure read-only routing to the Availability Group Properties dialog.
If you’ve been following us lately and reading our recent blog posts, you know that Randy and I recently took it upon ourselves to only use Azure Data Studio whenever possible. Unfortunately, ADS doesn’t have an option for working with Always On availability groups. There also isn’t a decent extension out there that does … yet. Hehe. Anyway, for this exercise we turned to the trusty SQL Server Management Studio.
Configuring Read-Only Routing
Configuring read-only routing using the UI is quite easy. It simply requires two steps:
- For each readable secondary replica that you want to support read-only routing, specify a read-only routing URL.
- For each replica in the AG that you want to support read-only routing when it is the primary replica, specify a read-only routing list.
In this example, we have two nodes, we’ll call them SQL01 and SQL02. When SQL01 is the primary, we want SQL Server to route all read-only connections to the secondary replica. If the secondary replica isn’t available, send the connections to the primary. To do that, we select the appropriate replicas, in order, from the Available replicas box and move them over to the Read-only routing list.
Thus, we select SQL01 in the top box. Then in the Available Replicas box, we first select SQL02 and add that to the routing list, followed by SQL01. In this way, SQL Server routes all read-only connections to the secondary replica, SQL02. If SQL02 isn’t available, the connections go to SQL01.
We repeat the process for SQL02, but in reverse. In the Available Replicas box, we first select SQL01 and add that to the routing list, followed by SQL02. In this way, SQL Server routes all read-only connections to the secondary replica, SQL01. If SQL01 isn’t available, the connections go to SQL02.
What I like about this is that when you first open the dialog and select the Read-only routing page, the Routing URL values are already filled in for you. You just need to specify the list. Sweet. After you configure everything, it should look something like the following.
You don’t need to add both nodes to the routing list. You can only list the secondary replica in the routing list, but it is nice to have a fallback if the secondary replica isn’t available.
One Final Note: T-SQL
If you haven’t done this with T-SQL, there’s a nifty option here. Once you have configured read-only routing, before you click OK, script the actions to a new query window so you can see what the T-SQL looks like:
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG01]
MODIFY REPLICA ON N'SQL01' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL02',N'SQL01')))
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG01]
MODIFY REPLICA ON N'SQL02' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL01',N'SQL02')))
GO
This Post Has 6 Comments
Gteat post Scott.
I have 2 comments:
1. You might want to use the server IP instead of server name for routing url since if the refferencing App or user are out of the domain you might have a problem..
2. Routing will not be done unless you add “ApplicationIntent=ReadOnly” (it is case sensitive) to the connection string and make sure that secondary replica is readable.
But the greatest challenge here is to educate Developers, DBA, QA and etc. to properly use it (via SSMS, App and etc.) and only in connections that does only reading and not writing.
It took us a while and few presentations to do that 😀
Regards
Great point on the client side ApplicationIntent. That may be worth a follow-up post of it’s own.
Thanks for reading!
why do we need to add both the server that are part of AG to the read only routing list? I though we only the replica and not both instances.
Because you don’t always know which replica will be PRIMARY and which will be SECONDARY/READONLY. This way if you failover, Read-Only Routing will still work. Also, if for some reason the read-only replica(s) are not available, you want it to be routed to the primary.
But won’t it do that automatically? My testing (on SQL 2022) shows that it does.
That may be true in 2022. I have not specifically looked at it to see. But it is still worthwhile to review and make sure the replicas are listed in the right order and based on how you want it to behave. If you have a replica in another data center for example, you typically wouldn’t want it to receive the queries from an app server in the main data center.