SQLGardner

Lisa Gardner - SQL Sever Premier FIeld Engineer

SQL 2012 AlwaysOn and Backups – Part 1 – Offloading the work to a replica

SQL 2012 AlwaysOn and Backups – Part 1 – Offloading the work to a replica

Rate This
  • Comments 1

One of the greatest aspects of AlwaysOn is the ability to offload the work of backups and integrity checks to a replica.  This can really help preserve resources on your primary for your application workload. I am going to focus primarily on transaction log backups in this post as the log backup piece is best to use in order to illustrate this new feature. One of the key pieces enabling this backup ability is the single log chain that is maintained across all replicas. I will illustrate this by showing the log_reuse_wait_desc from sys.databases and the output of DBCC SQLPERF(‘logspace’)

My environment has 3 instances participating in an Availability Group with the good ol’ AdventureWorks database.  I have a primary (SQL1),  a synchronous replica set to non-readable (SQL2), and an async replica that is readable (SQL3).  The reason I chose this particular configuration is simply to have a nice combination of sync/async and readable/non-readable to test.

Note that I have recently performed a full backup from SQL1, but there are some transactions in the transaction log.  Remember that a full backup does not truncate the transaction log!  I will keep repeating the following table after each command to show you how the log is kept the same amongst the replicas

Instance log_reuse_wait_desc Log Size Log Used %
SQL1 LOG_BACKUP 249.99 66.7
SQL2 LOG_BACKUP NA NA
SQL3 LOG_BACKUP 249.99 66.1

Note that since SQL2 is not set as readable, output for this database will not show in DBCC SQLPERF(‘logspace’). 

I will now execute a transaction log backup command on SQL2.

Even though this replica is not readable, we can still backup the transaction log from here.

Instance log_reuse_wait_desc Log Size Log Used %
SQL1 NOTHING 249.99 6.3
SQL2 NOTHING NA NA
SQL3 NOTHING 249.99 5.75

While the exact space used is very slightly different due to various reasons, you can see that performing the transaction log backup on 1 of the databases  cleared the log for them all.

Now I have generated a little more activity in the database to put more in the transaction logs. I am also setting the replica on SQL2 to readable so that we can get some output from DBCC SQLPERF (‘logspace’). You can see that reflected in the stats below

Instance log_reuse_wait_desc Log Size Log Used %
SQL1 LOG_BACKUP 249.99 13.19
SQL2 LOG_BACKUP 249.99 12.56
SQL3 LOG_BACKUP 249.99 12.56

Now let’s take a transaction log backup from SQL3 and see what our output looks like

Instance log_reuse_wait_desc Log Size Log Used %
SQL1 NOTHING 249.99 1.09
SQL2 NOTHING 249.99 0.46
SQL3 NOTHING 249.99 0.46

So now you can see that you can take transaction log backups from any replica and that is all part of a single log chain. When the log is truncated on one replica, it is reflected on the others.

One big thing to keep in mind is although there is a single log chain, there is not a single msdb across all your replicas.  You will notice that only backups taken from that instance will appear in the msdb backup tables.  While this seems rather obvious, it is important to note that you really must be careful that you know where backups are occurring.  Should you need to restore, you will need to piece together the necessary files to complete the log chain. In my example where I took a full backup on SQL1, and the first transaction log backup on SQL2, msdb on SQL2 had no knowledge of the full backup that was taken on SQL1. A best practice to combat this is to make certain that all backups are directed to the same file location.

I also want to point out that there are some limitations. For example full backups of replicas must be copy_only backups. If you simply perform full backups and transaction log backups, the only affect this will have on you is making sure you add copy_only to the backup command. The key point to this is that a copy_only full backup cannot serve as a differential base backup. Also, differential backups cannot be performed against replicas. This can be a bummer if you were hoping to perform differential backups from a replica.  Typically full and differential backups are performed at low application workload times anyway, so it should not be too much of an issue.

In my next blog post, I will describe some of the many options available to set backup preferences and how to automate backups for databases participating in an AvalabilityGroup.

Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
  • Nice job Lisa. Moving the IO for backups to the secondaries was a huge win in 2012.

Page 1 of 1 (1 items)