Availability Group Backup Preferences

Shaun Stuart points out that the Backup Preferences tab of the Availability Group Properties for an AG is a little tricky:

The default, and the way my AG was configured, was Prefer Secondary. As the image shows, this means backups will be made on the secondary, unless the secondary is unavailable, in which case, they will be made on the primary.

There are a couple of things to note when you use this setting:

  1. Full backups made on the secondary are Copy Only backups. This means they won’t reset the differential bitmap and your differentials will continue to increase in size until a full backup is made on the primary.

  2. Differential backups cannot be made on the secondary.

  3. Transaction log backups can be made on the secondary and they do clear the log, so your log file will not continue to grow.

Read on for more details.

Related Posts

Creating A Basic Availability Group

Ginger Keys shows us two methods for creating Basic Availability Groups in SQL Server 2016 and later: AlwaysOn Basic Availability Groups (BAGs) are available with SQL Server 2016 and 2017 Standard edition.  The functionality is generally the same as database mirroring (which has been deprecated).  This feature replicates transactions to a database on a secondary […]

Read More

Alerting On Missed Log Backups

Jamie Wick shows us how to build out an alert if we’ve exceeded a threshold for time since the last log backup: The primary reason most transaction logs start to (unexpectedly) grow is that the transaction log hasn’t been backed up. This is usually caused by one of two situations: the log backup job failed, […]

Read More

Categories