Configure Backup on Availability Replicas (SQL Server)

Article
06/14/2017

This topic describes how to configure backup on secondary replicas for an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2014.

Note

For an introduction to backup on secondary replicas, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups).

Before You Begin

Prerequisites

You must be connected to the server instance that hosts the primary replica.

Security

Permissions

Task	Permissions
To configure backup on secondary replicas when creating an availability group	Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
To modify an availability group or availability replica	Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Using SQL Server Management Studio

To configure backup on secondary replicas

In Object Explorer, connect to the server instance that hosts the primary replica, and click the server name to expand the server tree.
Expand the AlwaysOn High Availability node and the Availability Groups node.
Click the availability group whose backup preferences you want to configure, and select the Properties command.
In the Availability Group Properties dialog box, select Backup Preferences page.
On the Where should backups occur? panel, select the automated backup preference for the availability group, one of:

Prefer Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.

Secondary only
Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Primary
Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Important

If you plan to use log shipping to prepare any secondary databases for an availability group, set the automated backup preference to Primary until all the secondary databases have been prepared and joined to the availability group.

Any Replica
Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

Important

There is no enforcement of the automated backup preference setting. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups. For more information, see Follow Up: After Configuring Backup on Secondary Replicas later in this topic.
Use the Replica backup priorities grid to change the backup priority of the availability replicas. This grid displays the current backup priority of each server instance that hosts a replica for the availability group. The grid columns are as follows:

Server Instance
The name of the instance of SQL Server that hosts the availability replica.

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. The value is an integer in the range of 0..100. 1 indicates the lowest priority, and 100 indicates the highest priority. If Backup Priority = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently available.

Exclude Replica
Select if you never want this availability replica to be chosen for performing backups. This is useful, for example, for a remote availability replica to which you never want backups to fail over.
To commit your changes, click OK.

Alternative ways to access the Backup Preferences page

Using Transact-SQL

To configure backup on secondary replicas

Connect to the server instance that hosts the primary replica.
For a new availability group, use the CREATE AVAILABILITY GROUP (Transact-SQL) statement. If you are modifying an existing availability group, use the ALTER AVAILABILITY GROUP (Transact-SQL) statement.

Using PowerShell

To configure backup on secondary replicas

Set default (cd) to the server instance that hosts the primary replica.
Optionally, configure the backup priority of each availability replica that you are adding or modifying. This priority is used by the server instance that hosts the primary replica to decide which replica should service an automated backup request on a database in the availability group (the replica with highest priority is chosen). This priority can be any number between 0 and 100, inclusive. A priority of 0 indicates that the replica should not be considered as a candidate for servicing backup requests. The default setting is 50.

When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. In either case, specify the BackupPriorityn parameter, where n is a value from 0 to 100.

For example, the following command sets the backup priority of the availability replica MyReplica to 60.
```
Set-SqlAvailabilityReplica -BackupPriority 60 -Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MyAg\AvailabilityReplicas\MyReplica  
```
Optionally, configure the automated backup preference for the availability group that you are creating or modifying. This preference indicates how a backup job should evaluate the primary replica when choosing where to perform backups. The default setting is to prefer secondary replicas.

When creating an availability group, use the New-SqlAvailabilityGroup cmdlet. When modifying an existing availability group, use the Set-SqlAvailabilityGroup cmdlet. In either case, specify the AutomatedBackupPreference parameter.

where,

Primary
Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Important

If you plan to use log shipping to prepare any secondary databases for an availability group, set the automated backup preference to Primary until all the secondary databases have been prepared and joined to the availability group.

SecondaryOnly
Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default behavior.

None
Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

Important

There is no enforcement of AutomatedBackupPreference. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups. For more information, see Follow Up: After Configuring Backup on Secondary Replicas later in this topic.

For example, the following command sets the AutomatedBackupPreference property on the availability group MyAg to SecondaryOnly. Automated backups of databases in this availability group will never occur on the primary replica, but will be redirected to the secondary replica with the highest backup priority setting.
```
Set-SqlAvailabilityGroup -Path SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MyAg `  
 -AutomatedBackupPreference SecondaryOnly  
```

Note

To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider, see SQL Server PowerShell Provider and Get Help SQL Server PowerShell.

Follow Up: After Configuring Backup on Secondary Replicas

To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job. For example, a typical snippet of a backup-job script would look like:

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))  
BEGIN  
      Select 'This is not the preferred replica, exiting with success';  
      RETURN 0 - This is a normal, expected condition, so the script returns success  
END  
BACKUP DATABASE @DBNAME TO DISK=<disk>  
   WITH COPY_ONLY;

Scripting a backup job with this logic enables you to schedule the job to run on every availability replica on the same schedule. Each of these jobs looks at the same data to determine which job should run, so only one of the scheduled job 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.

Tip

If you use theMaintenance Plan Wizardto 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. However, the backup job will not return the "This is not the preferred replica..." message.Be sure to create the job(s) for each availability database on every server instance that hosts an availability replica for the availability group.

To Obtain Information About Backup Preference Settings

The following are useful for obtaining information that is relevant for backup on secondary.

View	Information	Relevant Columns
sys.fn_hadr_backup_is_preferred_replica	Is the current replica the preferred backup replica?	Not applicable.
sys.availability_groups	Automated backup preference	automated_backup_preference automated_backup_preference_desc
sys.availability_replicas	Backup priority of a given availability replica	backup_priority
sys.dm_hadr_availability_replica_states	Is replica local to the server instance? Current role Operational state Connected state Synchronization health of an availability replica	is_local role, role_desc operational_state, operational_state_desc connected_state, connected_state_desc synchronization_health, synchronization_health_desc

Share via