Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Backing Up SQL Server Databases Participating In Availability Groups

MSSQLTips author Chad Churchwell By:   |   Read Comments (9)   |   Related Tips: More > AlwaysOn Availability
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.

Backup Preferences for SQL Server AlwaysOn Availability Groups

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


Last Update: 2/13/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, February 13, 2013 - 9:54:04 AM - Shankar Read The Tip

Very nice and informative article,thanks Chad...


Tuesday, March 12, 2013 - 9:30:43 AM - Ranga Read The Tip

Nice...like the idea of having the servername, dbname on the backups and always using the same file share!


Friday, March 15, 2013 - 9:50:42 AM - Sreekanth Read The Tip

Nice! But you should have warned(While doing Full Backups on one Server and T-Log on the other) about risks involved for doing a Point in Time Restore if we loose a single T-Log Backup(s) residing on a different server :)

 

Thanks-Sree!


Monday, July 22, 2013 - 10:55:55 AM - Bart Read The Tip

Your comment about mixing backups from different replicas for a restore is not true.  I am able to restore a backup with a full backup from the primary and transaction log backups from the secondary.  I even verified that I was getting the logged transactions that occurred after the full backup.

Also, realize that a "copy-only" backup is not part of a backup-chain.  Copy-only backups are stand-alone backups only.


Monday, July 22, 2013 - 11:38:52 AM - SDC Read The Tip

Quick question: is it sufficient to only do the log backups on one node? Specifically, does doing the backup on the one node prevent growth on all nodes? I am thinking the answer here is yes and that running log backups on all nodes would lead to an unholy mess of a log backup chain, but on the other hand notice a very large log in a DB for one of my Availability groups.

 

Many thanks


Tuesday, July 23, 2013 - 8:28:38 AM - Chad Churchwell Read The Tip

If the transaction log backups are offloaded to a replica (not using the COPY_ONLY option as that is only required to offload FULL backups) it fill free space in all the log files.  Please see the following blog post

 

http://blogs.msdn.com/b/sqlgardner/archive/2012/07/18/sql-2012-alwayson-and-backups-part-1-offloading-the-work-to-a-replica.aspx

 


Friday, September 13, 2013 - 7:54:16 PM - Chad Churchwell Read The Tip

In response to Bart, I mentioned the GUI does not build the restore chain, not that you could not restore manually.  The limitation is in the GUI when it normally rebuilds the restore chain for you


Tuesday, September 17, 2013 - 11:02:46 AM - Peter Read The Tip

Does anyone have a sample script how to restore a database in a situation where transaction logs are taken from one node and full backups are taken from another? Like mentioned above. many thanks.


Tuesday, February 04, 2014 - 5:39:51 AM - Ana Read The Tip

What will happen if there are multiple secondaries and backup priority of all the replicas are set as same say 50 for all. How would SQL decide on which replica to perform the backup?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.