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

SGH 1,221 Reputation points
2020-12-21T05:55:04.51+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2020-12-22T09:47:02.13+00:00

    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

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 101K Reputation points MVP
    2020-12-21T22:39:23.56+00:00

    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.

  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2020-12-22T06:43:28.86+00:00

    Hi @SGH ,

    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.

  4. Erland Sommarskog 101K Reputation points MVP
    2020-12-22T07:30:25.88+00:00

    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.

    0 comments