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

To configure the availability group use the following AlwaysOn Availability Groups settings:

• Automated backup preference (configured for the availability group as a whole)

  At an availability group level, specify whether backups should or should not run on the primary replica. The possible preferences for where backups should run are as follows:

  Preference	Description
  Only on the primary replica	Backups should always occur on the primary replica. This alternative is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.
  On secondary replicas	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.
  Only on secondary replicas	Backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.
  No preference	Backup jobs should 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.

  For information about how to specify these settings, see Configure Backup on Availability Replicas (SQL Server).

• Backup priority (configured individually for each availability replica)

  To specify whether an availability replica is a candidate for running backup jobs, specify its backup priority. By specifying different backup priorities for different availability replicas, you can specify an ordered preference among secondary for running backup jobs. To indicate the priority for a given availability replica, set backup priority to a value from 0 to 100, as follows:

  Setting	Description
  1..100	The relative priority of a given replica relative to the backup priorities of the other replicas in the availability group. 100 is the highest priority. By default, all secondary replicas have the same backup priority (50), making all replicasle equal candidates for running backup jobs unless you specify a different value for at least one replica.
  0	The availability replica will never be chosen for performing backups. This is useful, for example, for a remote secondary replica to which you do not want your production backup jobs to fail over. This is also useful for a computer that lacks the facilities to handle backups.

  For information about how to specify these settings, see Configure Backup on Availability Replicas (SQL Server).

The availability group and replica configuration settings have no effect unless you script backup jobs to use these setting. On every availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group.

You can determine whether the current replica is the preferred backup replica by calling the sys.fn_hadr_backup_is_preferred_replica function. 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 that queries this function, to determine which replica should run a given backup job, and if the function returns '1', runs a backup job.

The logic for this script is as follows:

If (top-priority replica is local)

Run backup job

Else

Exit with success

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.

Scripting a backup job using this sort of 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 need to be touched. 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.

For a sample script, see the "Follow Up: After Configuring Backup on Secondary Replicas" section of Configure Backup on Availability Replicas (SQL Server).