question

Samanthar-3682 avatar image
0 Votes"
Samanthar-3682 asked Cathyji-msft commented

AG secondary replica use cases

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Samanthar-3682,

Any update for this thread? Did the reply could help you? If the response helped, do "Accept Answer". If you have further question, please feel free to ask.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Samanthar-3682 commented

So, would like to know pros & cons of implementing

What for "cons" do you expect? If there would be "cons" as show-stopper, why would anyone use it?

Secondary to offload read operations

That's what a replica is good for.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Olaf,

Don't take me wrong. Just want to know if there any other limitations/anything came up post implementation.

0 Votes 0 ·
Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered Cathyji-msft edited

Hi @Samanthar-3682,

You can get the answers from below MS document. Please read below document, hope this could help you.

Offload read-only workload to secondary replica of an Always On availability group

Such as :

Beginning in SQL Server 2014 (12.x), readable secondary replicas can remain online even when the primary replica is offline due to user action or a failure, for example, synchronization was suspended due to a user command or a failure, or a replica is resolving status due to the WSFC being offline. However, read-only routing does not work in this situation because the availability group listener is offline as well.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Cathy,

Thanks for the reply,
I have a question, 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?

0 Votes 0 ·

No. Read-only routing refers to automatically routing incoming listener connections to a readable secondary replica that is configured to allow read-only workloads. Primary replica is always read and write.

0 Votes 0 ·