Configure Backup on Availability Replicas (SQL Server)

  • Article

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

Note

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

  • Before you begin:

    Prerequisites

    Security

  • To configure backup on secondary replicas , using:

    SQL Server Management Studio

    Transact-SQL

    PowerShell

  • Follow Up: After configuring backup on secondary replicas

  • To Obtain Information About Backup Preference Settings

  • Related Content

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.

Arrow icon used with Back to Top link[Top]

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 AlwaysOn 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 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

Arrow icon used with Back to Top link[Top]

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.

Arrow icon used with Back to Top link[Top]

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 BackupPriority n 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

Arrow icon used with Back to Top link[Top]

Follow Up: After Configuring Backup on Secondary Replicas

To take 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 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.

Arrow icon used with Back to Top link[Top]

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

Arrow icon used with Back to Top link[Top]

Related Content

Arrow icon used with Back to Top link[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)