Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server AlwaysOn Availability Group Backup Preference Setting


By:   |   Read Comments (1)   |   Related Tips: More > 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.

AG Configuration

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

Access Backup Preferences in AG

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.

Backup Preferences form

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.

Backup job successful - Description: Backup job executed successfully but no backup was 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.

Back Up Database Task - Description: T-SQL in Back Up Database Task showing checks for preferred replica

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
T-SQL to check backup preferred replica

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

Tweaking backup priority

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
T-SQL to check backup preferred replica

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


Last Update:






About the author
MSSQLTips author Simon Liew 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, July 14, 2017 - 3:06:06 PM - Srinath M Back To Top

Very nice article but one important point is missed here:
In case 'Backup priority' is same for all servers, sql server would like to prefer the top one in the order listed under 'Replica backup prorities'.
That's exactly why 'Preffered Replica' returned 1 for SQLP2 in the first test case.


Learn more about SQL Server tools