Understanding Backup Preferences for AlwaysOn Availability Group Databases

We know SQL Server Backups are resource intensive operation that strain on I/O and CPU (with backup compression). AlwaysOn Availability Group has very good capability to offload SQL Server backups and read operations from primary replica to reduce such workloads and utilize secondary replicas in a better way. Read this article to understand backup preferences for AOAG databases.

Before going ahead, we need to understand the rules/points that are very important in order to decide the backup preferences in AOAG configuration. Below are the important points you should consider during designing backup strategy of availability databases.

  • Regular Full Backups are not allowed from secondary replicas. Only COPY_ONLY full backups are allowed on secondary replicas. That means you cannot run regular Full backup from any of the secondary replica. Remember that copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not supported on secondary replicas. If you must run differential backups for your availability databases, you should not choose backup preferences as secondary replicas.
  • Only regular Log backups are supported on secondary replicas. COPY_ONLY log backups are not supported on secondary replicas.
  • Secondary replica must be in SYNCHRONIZED or SYNCHRONIZING state to backup a secondary database.
  • Log backup chain is supported across all replicas regardless of where the log backup is taken (primary or secondary replicas) or the mode of the replication (asynchronous or synchronous).
  • In a distributed availability group, backups can be performed on secondary replicas in the same availability group as the active primary replica, or on the primary replica of any secondary availability groups.
How SQL Server Decides Which Replica will execute Backup – Backup Preferences ?

It’s little complex process to determine where backup jobs should run.  If you are confused about this question, then keep reading to get the answer.

To decide on which replica, you should run your backup job depends on your Backup Preferences setting. To address this, configure the availability group to specify which availability replicas where you would prefer backups to be performed.

If you choose backup preferences as secondary only then you can create jobs on only secondary replicas or if you choose to run it on primary replica, then you can configure it on primary replica only. But if you decided to go with default option that is preferred secondary then you should schedule backup jobs on all replicas.  Although, Microsoft suggests to create scripted backup-jobs for every availability database on every server instance that hosts an availability replica that is a candidate for performing backups.

Suppose you have chosen secondary only preference and you have 3 secondary replicas then question might arise how database engine will decide that on which replica backup should run. Database engine decides where should run backup based on preferred backup replica settings and backup priority settings. I have explained about these settings later in this article.

AOAG (AlwaysOn Availability group) has a function named sys.fn_hadr_backup_is_preferred_replica  to determine which replica is set as preferred backup replica.  If the out of this function returns 1 it means current instance is the preferred backup replica and backup will run on this instance. If returns 0, it means backup will not run on that replica.  We can use this function in a backup script to determine job should run on which replica, so only one of the scheduled jobs actually proceeds to the backup stage.

In the event of a failover, none of the scripts or jobs needs to be modified. Also, if you reconfigure an availability group to add an availability replica, managing the backup job requires simply copying or scheduling the backup job. If you remove an availability replica, simply delete the backup job from the server instance that hosted that replica. Microsoft has given a sample code logic for this function that can be used to develop backup script.

If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1
BEGIN
-- If this is not the preferred replica, exit (probably without error).
END
-- If this is the preferred replica, continue to do the backup.

If you use the Maintenance Plan Wizard to create a given backup job, the job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function.

If you want to check the preferred backup replica for any availability database, you can get it by running below command. If the output of this function returns 1 it means current instance is the preferred backup replica and backup will run on this instance. If returns 0, it means backup will not run on that replica.

--Get the backup preference replica for availability database TechYaz.
SELECT sys.fn_hadr_backup_is_preferred_replica ('TechYaz');
GO

I have executed same function on my secondary replica. You can see the output is showing as 1, it means this replica is set to run backups.

Get preferred backup replica

Configure Backup of Availability Databases to Run only from Secondary Replicas using GUI
  1. Connect to the server instance that hosts the primary replica, and click the server name to expand the server tree. You would not be able to change Backup Preferences from any secondary replicas. If you open the AOAG name property, you will find all options greyed out.
  2. Expand the Alwayson High Availability node and the Availability Groups
  3. Click the availability group whose backup preferences you want to configure, and select the Properties.
  4. In the Availability Group Properties dialog box, select Backup Preferences You can see backup preference is set to Prefer Secondary, now we need to change it to Secondary only option. The details about each type of backup preference is given in the screenshot and AOAG backup preference property window.

AOAG Property Window

  1. There are 4 backup preferences in AOAG that are given below with their capabilities. Choose any as per your requirements. Now I will add one more replica to this availability group as per our requirement and set backup preference to secondary only so that backups always run from either of the both secondary replicas. It is not mandatory for you to add additional replica.
Backup PreferenceBackup Runs onRemarks
Prefer SecondarySecondary ReplicaDefault Option. You cannot run differential backup in this option. If only Primary Replica is online, backup will run on Primary Replica.
Secondary onlyOnly Secondary ReplicasBackup will never run on Primary Replica. If only Primary Replica is online, backup will not run. You cannot run differential backup in this option as well.
PrimaryPrimary ReplicaChoose this option if you want to run differential backup.
Any ReplicaCan run on any replica based on backup priority of each availability replica in combination with its operational state and connected state.Set backup priority carefully between replicas.
  1. Use the Replica backup priorities grid to change the backup priority of the availability replicas. Backup Priority (Lowest=1, Highest=100) specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. In my case backup will run on the replica that has priority 60% and if this will not be available then backup will run on the replica that has priority of 50%. Backup will never run on primary replica irrespective of backup priority settings because we have chosen to run backups only on secondary replica.
    Availability group property Window
    We have also an option Exclude Replica to exclude any replica where you don’t want to run backups. This is useful for remote availability replicas to which you never want to run backups there.
    You should click on checkbox for your respective replica name for which you want to exclude the backup as shown in above screenshot.
  1. Click on OK button to apply the changes.
Configure SQL Server Backup in AOAG to Run with default backup preference using T-SQL

I have shown you how to change backup preference setting to run backups from secondary replicas using GUI. We can do the same using T-SQL commands as well. Here i will show you how to change backup preference using T-SQL. As we have changed the backup preference to secondary only option where backups will never run from primary replica. Now we can revert this change to default option using T-SQL. Prefer Secondary is default option in which backups run from secondary replica and if only primary replica is online then it will run from primary replica only.

Connect to the server instance that hosts the primary replica and run below command.

--Choose backup preference as per your choice and pass that value in AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }
ALTER AVAILABILITY GROUP AG_AOAGName SET (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
GO

Change backup preference using T-sql

Now your backup preference would be set to Prefer Secondary and backups will run from primary replica if only primary replica will be online.

Related Articles:

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
Understanding SQL Server Backup for AlwaysOn Availability Group Databases
Article Name
Understanding SQL Server Backup for AlwaysOn Availability Group Databases
Description
As we know SQL Server Backup are resource intensive operations that strain on I/O and CPU (with backup compression). AlwaysOn Availability group has very good capability to offload read operations and SQL Server backups from primary replica to reduce such kind of workload from primary replica and utilize secondary replicas in a better way.
Author
Publisher Name
https://www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *