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

Availability Group Improvements In SQL Server

Allan Hirt points out two improvements to Availability Groups in the latest SQL Server 2016 and 2017 patches: First and foremost, SQL Server 2016 Service Pack 2 was just released today. There are two major improvements in it for AGs: 1. SQL Server 2016 now has full Microsoft Distributed Transaction Coordinator (DTC) support. SQL Server 2016 had partial […]

Read More

Putting The Distribution Database In An Availability Group

Sourabh Agarwal announces that you will soon be able to put the distribution database into an Availability Group: Many enterprise customers have asked the capability to combine the usage of SQL Server replication and Always On, such that they can place replication distribution databases within an Always On AG to achieve high availability for their […]

Read More

Categories