Backup Maintenance Plan for Secondary read only replica for Always Availability Group

Question

Is it recommended to back up Maintenance Plan for Secondary read only replica for Always Availability Group. We have configured for the Primary Replica.

Answer 1

Good day

Adding to what was told already, I recommend to check this tutorial:

https://www.mssqltips.com/sqlservertip/4976/sql-server-alwayson-availability-group-backup-preference-setting/

In general, performing backups on a secondary replica is recommended in many cases. You should configure the AUTOMATED_BACKUP_PREFERENCE and BACKUP_PRIORITY parameters and you need to configure backup jobs for every availability database on every server instance that hosts an availability replica that is a candidate for performing backups.

Check out this document as well:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups?view=sql-server-ver15

Answer 2

What is the question? Whether you should also backup the secondary? Logically, it is the same database, so there is little point in it. And something in the back of my head tells me that you cannot even take a full backup on a secondary, but I may recall incorrectly.

However, keep in mind that tomorrow the roles may have reversed, so that the machine which now hosts your secondary will host the primary, and in this case you should have backups running on this machine.

Answer 3

Hi @Sajith Gopalakrishnan Hema ,

Even if the secondary is read only replica, we can schedule backup from the Maintenance Plan section right?

Yes, you can.
But there are some limitations to take backup Maintenance Plan on Secondary Replicas:

• BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
• Differential backups are not supported on secondary replicas.
• BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).
• A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
• To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

And to configure backup on secondary replicas, you need to Always On High Availability node and the Availability Groups node-> Availability Group Properties->Backup Preferences page, change it to Prefer Secondary or Secondary only.
Please refer to this doc for more details.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Amelia's reply sums up what your options are. But I like to reiterate: you secondary will not remain a secondary forever. One day it will become the primary (because else why did you create an availability group). So you need to have a backup plan where you have an unbroken chain of full and log backups, so that you can recover from a disaster.