May 19, 2022 at 5:05 am
Hi All,
I need some advice on Always on secondary replica usage.
We are using Microsoft SQL Server 2017 (RTM-CU23) Enterprise Edition in our environment. We have 3 node multi-subnet Always on availability group setup.
In current prod scenario, the secondary replica is underutilized and all the load is taken by Primary replica itself ( app read-write operations, full backups, diff backups, log backups, index maintenance, checkdb, update stats etc...).
We wanted to implement read-only routing on Secondary to offload read operations & full backups to be performed on secondary. So, would like to know pros & cons of implementing Read-only routing. If any limitations or licensing things please share your thoughts.
Thank you.
-Sam
May 19, 2022 at 2:21 pm
Unless you are actually having issues where you know that offloading the reads is going to help - then read-only routing is only going to make your system more complex to manage and maintain with no benefits.
For read-only routing to work, the applications connection strings need to be updated - and the application needs to be modified so that it knows which connection to use for each operation. For a read-only connection to work it needs to have the ApplicationIntent=ReadOnly qualifier added - which redirects the connection to the secondary node. If the application then attempts to update data on that connection it will fail.
Now - you can use a read-only secondary to offload full backups and transaction log backups. You cannot offload index or statistics maintenance as those require the ability to modify the database. Offloading backups requires a bit more coding - as you need to check which node the job is running on and if it is the primary and a secondary exists - then don't run. You also need to make sure the jobs are run on the correct secondary if you have more than 1.
One final note - if you offload your full backups to a secondary you will not be able to perform differential backups, since you must use the COPY_ONLY flag on the read-only secondary to be able to perform the full backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2022 at 4:28 pm
Thanks for the inputs. Have couple of questions.
Q1) In worst case scenario, if the secondary db is unavailable or the secondary replica is overloaded, in that case does all read only connections are routed to PRIMARY replica? if not, what happens to those in-coming connections to secondary?
Q2) When log backups are taken on secondary, does the log backup happens on secondary server or it connects to Primary and take log backups on primary? Reason for asking this question is, there is no COPY_ONLY Log backups. please correct me if I am wrong.
Q3) Logically, why can't we take differential backups on Secondary?
Q4) For offloading backups on to secondary, will there be a licensing cost involved or no charges are applicable? As per my knowledge goes, know that no charges are applicable but when it is used for READ_ONLY Routing, cost is involved. Is that correct?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply