Edit

Configure backups on secondary replicas of an Always On availability group

  • Article

Applies to: SQL Server

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

Note

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

Prerequisites

You must be connected to the server instance that hosts the primary replica in SSMS. The secondary replica must be healthy, which includes being connected to the current primary replica and in the secondary role.

Note

The secondary replica does not need to be readable to offload backups to it. Backups will still succeed on the secondary replica even if Readable Secondary is set to no.

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

  1. In Object Explorer, connect to the server instance that hosts the primary replica, and click the server name to expand the server tree.

  2. Expand the Always On High Availability node and the Availability Groups node.

  3. Click the availability group whose backup preferences you want to configure, and select the Properties command.

  4. In the Availability Group Properties dialog box, select Backup Preferences page.

  5. 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.

  6. 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.

  7. To commit your changes, click OK.

Alternative ways to access the Backup Preferences page

Using Transact-SQL

To configure backup on secondary replicas

  1. Connect to the server instance that hosts the primary replica.

  2. 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

  1. Set default (cd) to the server instance that hosts the primary replica.

  2. 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

  3. 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

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 (sys.fn_hadr_backup_is_preferred_replica(@DBNAME) != 1)  
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 = '<path to backup file>'  
   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 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. 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

Related Content

See Also

Overview of Always On Availability Groups (SQL Server)
Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)