SQL Server AlwaysOn Availability Group Backup Preference Setting

By: Simon Liew   |   Updated: 2017-07-14   |   Comments (7)   |   Related: > Availability Groups

Problem

I have a user database in a SQL Server AlwaysOn Availability Group running on a 3-node Windows cluster. I set up a full backup of the user database in a SQL Server Maintenance Plan using the Back Up Database Task on the primary replica of the SQL Server AlwaysOn Availability Group.  The full backup job executed successfully, however the backup job did not generate any database backup files. Why is my user database backup not generated?

Solution

SQL Server AlwaysOn Availability Group (AG) allows backups to be taken from the primary replica or any of the secondary replicas in the AG. By default, an AG backup preference is set to Prefer Secondary when you setup the AG using the New Availability Group Wizard and use the default configuration. When the backup preference is set to prefer secondary in the AG, the user database backup will not be generated on the primary replica when a backup is configured using the Back Up Database Task in a Maintenance Plan. This is because the Back Up Database Task in a Maintenance Plan automatically adds a check to determine if the current replica is the preferred backup replica for databases in the AG. If the replica is the preferred replica for backup, then the BACKUP DATABASE command will execute, otherwise the database backup will be skipped.

Understanding Backup Preferences in AG

Let’s assume we have an AG configuration like the one below to learn more about Backup Preferences in AG.

The Backup Preferences setting is accessible when you right-click on the Availability Group > Properties on your primary AG replica.

The screen shot below shows the Backup Preferences setting form. This is the default setting when creating an AG using the New Availability Group Wizard and accepting the default.

Say we now set up a Back Up Database Task in a Maintenance Plan on SQLP1. We execute the backup job and it runs successfully, but when you check the backup folder there aren’t any user database backups generated.

If we go back to the Maintenance Plan and click the [View T-SQL] button, you will notice there is a check for the preferred replica to determine if the BACKUP DATABASE command will execute or not.

Looking at the T-SQL, the logic indicates when the @preferredReplica variable does not return 1, then the database will not be backed up. This system function returns a value based on the setting of the Backup Preferences. It is added automatically when configuring the backup of user databases in the AG using the Back Up Database Task in a Maintenance Plan.

Multiple Secondary Replicas

In our scenario, we have 2 secondary AG replicas. So, how do we know which secondary replica is the preferred replica and will create the backup? Well, the same command also allows us to manually check which SQL Server replica we should use to configure the Back Up Database Task.

If we launch a Query window and enable SQLCMD mode, we can easily check all AG replicas to determine which replica is the preferred replica that will produce the backup. In our scenario, the preferred replica for database backup is SQLP2.

:CONNECT SQLP1
SELECT @@SERVERNAME SQLName, [master].sys.fn_hadr_backup_is_preferred_replica('AGplaceholder') PreferredReplica
GO

:CONNECT SQLP2
SELECT @@SERVERNAME SQLName, [master].sys.fn_hadr_backup_is_preferred_replica('AGplaceholder') PreferredReplica
GO

:CONNECT SQLP3
SELECT @@SERVERNAME SQLName, [master].sys.fn_hadr_backup_is_preferred_replica('AGplaceholder') PreferredReplica
GO

Now let’s tweak the backup priority for SQLP3 to a higher number and re-run the query.

As expected, the backup has now shifted to SQLP3.

:CONNECT SQLP1
SELECT @@SERVERNAME SQLName, [master].sys.fn_hadr_backup_is_preferred_replica('AGplaceholder') PreferredReplica
GO

:CONNECT SQLP2
SELECT @@SERVERNAME SQLName, [master].sys.fn_hadr_backup_is_preferred_replica('AGplaceholder') PreferredReplica
GO

:CONNECT SQLP3
SELECT @@SERVERNAME SQLName, [master].sys.fn_hadr_backup_is_preferred_replica('AGplaceholder') PreferredReplica
GO

Conclusion

Hopefully walking through these steps has helped you understand how the Availability Group Backup Preferences setting works.

When the database backup is set up using the Back Up Database Task in a Maintenance Plan, the system function sys.fn_hadr_backup_is_preferred_replica will first check if the current replica is the preferred backup replica. The backup priority setting allows you to configure the AG to specify the availability replica that you want to use for backups.

Be mindful that backups taken on a secondary replica require that the secondary SQL Server instance be licensed even if you are just offloading backups.

Next Steps

• Configure Backup on Availability Replicas (SQL Server)
• Backing Up SQL Server Databases Participating In AlwaysOn Availability Groups
• Making the most out of your secondary replica for SQL Server AlwaysOn Availability Groups

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips