Configuring availability replicas to run scheduled data backups

When an availability database is replicated across multiple availability replicas in an availability group, a configuration option is available to enable you to select a single replica on which to run a backup operation instead of backing up all replicas.

About this task

Microsoft SQL Server 2012 and later versions provide a set of configuration options that you can use to specify whether scheduled backups are run on the primary or secondary availability replica. You can use the Data Protection for SQL Server GUI to set these options.

The configuration option can also be used to offload the backup from a primary replica to a secondary replica for load balancing. When databases fail over, backups must continue to run from other replicas to ensure that high availability is maintained.

Procedure

  1. Start Microsoft Management Console (MMC).
  2. In the Management section of the window, click Protect Data next to the SQL Server workload.
  3. On the Actions pane, click Properties.
  4. Click the AlwaysOn Preferences property page.
  5. In the Availability group field, select the AlwaysOn Availability Group for which you want to set up backup preferences.
  6. In the Preferred replica field, select your preferred replica on which to run scheduled backups.
    • Select Prefer Secondary replica if you want scheduled backups to occur on a secondary replica, if it is available. Otherwise, use the primary replica for the scheduled backup.
    • Select Secondary only if you want scheduled backups to occur only on a secondary replica.
    • Select Primary if you want scheduled backups to occur only on the primary replica.
    • Select Any replica if you want scheduled backups to occur on any availability replica.
  7. For each availability replica that is listed in the Availability replicas list box, specify whether it is a candidate to run scheduled backups by specifying the backup priority for that replica. A value of 1 has the lowest priority, and a value of 100 has the highest priority. A value of 0 indicates that the replica is excluded from schedule backup operations.
  8. Click OK to save your configuration and exit the Data Protection Properties page. The settings are saved to the tdpsql.cfg file and can be replicated to the other replicas in the availability group.