Backing Up SQL Server Databases Participating In AlwaysOn Availability Groups

By: Chad Churchwell   |   Comments (17)   |   Related: > Availability Groups

Problem

So I am using this new technology called Availability Groups that was introduced in SQL Server 2012. I have heard I can offload my backups to a secondary replica, but I am not sure how that works or how to configure those backups.  Are there any limitations?  Check out this tip to learn more.

Solution

SQL Server 2012 AlwaysOn Availability Groups allows the offloading of CERTAIN types of backups to a replica. At the time of this writing, only transaction log backups and full backups with COPY_ONLY are supported on secondary replicas. Differential backups are not supported on secondary replicas, so if differential backups are in your plans, you may be stuck with running on the primary node only.

Below is a list of limitations taken from SQL Server Books Online:

• BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups on secondary replicas
• Differential backups are not supported on secondary replicas
• BACKUP LOG supports only regular log backups on secondary replicas, the copy-only option is not supported
• Secondary replicas must be in SYNCHRONIZED or SYNCHRONIZING state and be able to communicate with the primary

Your backup preferences, and I stress the word "preferences" are set up when configuring the availability group, or can be modified on an existing availability group. There is nothing that prevents you from running supported backup types on any of the replicas, although there is a mechanism that will take your preferences into consideration, which is discussed later in this tip.

The options for backup preferences are:

• Prefer Secondary - Will backup on secondary with highest priority. If no secondary's are available, it will backup on primary.
• Secondary Only - Will backup on secondary with highest priority. If no secondary's are available, no backups will occur.
• Primary - Backups will occur on the primary only, whichever instance that happens to be at the time of the backup.
• Any Replica - Looks just at the backup priority and backups on the replica with the highest priority.

Below is how this is configured using SQL Server Management Studio. For my testing, CHAD-SQL1 and CHAD-SQL2 are set up with synchronous data movement and CHAD-SQL3 is set up with asynchronous data movement. Based on this configuration, I want backups to occur on either CHAD-SQL1 or CHAD-SQL2 since the risk of data loss is eliminated. Only as a last resort would I want to use CHAD-SQL3, which is why it has the lowest priority. You also have the ability to exclude a replica if that meets your architecture needs.

Below is how the backup preferences are configured using T-SQL. You can see in the code where the backup preference is set as well as the backup priority.

CREATE AVAILABILITY GROUP [CHAD-AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [TestPub]
REPLICA ON 
 N'CHAD-SQL1' WITH (ENDPOINT_URL = N'TCP://CHAD-SQL1.sqlchad.local:5022', 
   FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
 N'CHAD-SQL2' WITH (ENDPOINT_URL = N'TCP://CHAD-SQL2.sqlchad.local:5022', 
   FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   BACKUP_PRIORITY = 80, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
 N'CHAD-SQL3' WITH (ENDPOINT_URL = N'TCP://CHAD-SQL3.sqlchad.local:5022', 
   FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
   BACKUP_PRIORITY = 20, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

Once you have your backup preferences and priority established, then it is time to set up a SQL Server Agent Job to perform the backups. Thankfully Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings. Skeleton code for how this looks is below.

IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
BEGIN
  --Perform backup
END

Unfortunately there currently is no way to set backup preferences for full backups to one server and transaction log backups to another server. The backup preferences are for backups in general, not the backup type. This needs to be kept in the back of your mind when setting up your backups since secondary replicas only support full backups with COPY_ONLY as well as transaction log backups. The backup plan that is architected has to stay within these boundaries if you are using secondary replicas for your backups. Another thing to keep in mind if you start mixing backups from different replicas (take full backup from one node, and transaction log backups from another node) that there GUI cannot reproduce a restore chain as it looks only locally into MSDB system tables. The node that has the transaction log backups will know nothing about the full backup taken from the other node. My personal preference is to write backups from all nodes to the same file share and to put the server name, database name, and datetime stamp into the file name. That way you know where the backups originated from and can easily formulate a restore chain if needed.

Here is a script I use, and I setup a job that calls this code on every instance in the Availability Group. If it is not the preferred backup location, it will not do anything and will exit gracefully. This script can of course be further customized according to your needs.

CREATE PROCEDURE usp_BackupDatabaseAG 
(
 @DatabaseName SYSNAME, 
 @BackupPath VARCHAR(256),
 @BackupType VARCHAR(4)
)
AS
BEGIN
DECLARE @FileName varchar(512) = @BackupPath + 
 CAST(@@SERVERNAME AS VARCHAR) + '_' + @DatabaseName
DECLARE @SQLcmd VARCHAR(MAX)
IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
 IF @BackupType = 'FULL'
 BEGIN
  SET @FileName = @FileName + '_FULL_'+ 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.bak'
  SET @SQLcmd = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + 
   ' TO DISK = ''' + @FileName + ''' WITH COPY_ONLY ;'
  --PRINT @SQLcmd
  EXECUTE(@SQLcmd);
 END
 ELSE IF @BackupType = 'LOG'
 BEGIN
  SET @FileName = @FileName + '_LOG_'+ 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.trn'
  SET @SQLcmd = 'BACKUP LOG ' + QUOTENAME(@DatabaseName) + 
   ' TO DISK = ''' + @FileName + ''' ;'
  --PRINT @SQLcmd
  EXECUTE(@SQLcmd);
 END
END

Next Steps

• Learn the limitations of using secondary replicas for backups
• Perform extensive testing including restores before implementing in a production environment
• Check out these additional resources:
  • SQL Server AlwaysOn Availability tips
  • Configure Backup on Availability Replicas
  • Backup on Active Secondary's

About the author

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

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

View all my tips