Share via

SQL 2012 AlwaysOn and Backups – Part 2 - Configuring Backup Preferences and Automating Backups

  • Article

In my last post I showed that you can perform backups from your replicas. A thought you may have had is “Well that is great, but how do I know if that instance is the replica and has not become primary?  What if I want a particular replica to perform the backup but allow another to be able to do it just in case that one is not available?” Luckily, the product team thought of just those questions and created some configuration options for backup preferences.

The easiest way to start to describe these preferences is to show you the GUI within the Availability Group Properties window’s Backup Preferences:

image

 

As you can see there are a lot of options here.  I am not going to go into all of them, as they are all described in the BOL here. Though, to illustrate how you can create a combination of options, I will describe my setup shown above.  If you recall from my previous post I have 3 replicas.  SQL1 is the primary, SQL2 is a sync replica, and SQL3 is an async replica. By looking at my Backup Preferences, you can see that SQL2 would be the first choice for backups since it is set to “Prefer Secondary” and SQL2 has the highest backup priority. Now what do you think will happen if SQL2 is the primary?  Since SQL1 has the second highest backup priority, and it is now a secondary replica. It will be the preferred location for backups.  The reason that I have SQL3 selected here as the last option for backups, is that it is an async replica which may allow data loss and typically would be in a distant network location.  I could even exclude this replica altogether!  As you can imagine, there are a plethora of combinations here.

Also note that these options can be set via the GUI, via the ALTER AVAILABILITY GROUP T-SQL command, as well as the Set-SqlAvailabilityReplica PowerShell cmdlet!

Keep in mind that the key word here is PREFERENCES. There is no enforcement of these preferences. For example, there is nothing stopping me from doing a transaction log backup against the replica on SQL3  You can still manually go and attempt any backup you want on any replica directly.  So then what is the point of all these options? One simple word …. AUTOMATION!!!  You want your backups to happen where you want, when you want, and you don’t want to have to think about it day after day!  This is where these preferences are a lifesaver!

In my example, I don’t want to have to think about making sure that backups start happening on SQL1 instead of SQL2 if the Availability Group fails over…. I want it to just happen!  This is where the automation part of this post comes in….

Your key here is a new function sys.fn_hadr_backup_is_preferred_replica. When you call this function with the database name as an input parameter, it returns a 0 if the current instance is not the preferred backup location and a 1 if it is.  This is where the magic happens folks!  So in order to automate backups so that they happen at the right place, you need to set up the jobs on all the instances where they might possibly happen, but you will use the new function to determine if they will happen. You would script something like this…..

image

This way your scheduled jobs would run on each instance.  The job will perform the backup if it is supposed to, and it will not back it up if it isn’t!

“Ugh but I hate writing my own scripts!” you say?  No worries, it is built into database maintenance plans automatically.  There is a checkbox to turn OFF this feature…Keep in mind the wording.  You check the box only if you want to ignore backup preferences and backup on that machine no matter what state the replica is in.

image

I know a lot of people that use Ola Hallengren’s backup scripts.  The current version of that backup script uses this function as well.  I have not checked, but I am sure many 3rd party backup products do too. If you use one, check the documentation.

I breezed through a lot of stuff in this topic. Before you deploy an Availability group, consider how you plan to do backups.  They are extremely important yet often not given much planning. There are a number of ways you can fully automate backups to happen where you want them to happen, no matter where the primary database currently resides. With this also comes more ways you can mess things up if you are not careful.

Here are some key summary points:

  • There are a lot of backup preferences.  Read the BOL and understand the options.  Test the options you plan to deploy in all your failover scenarios. This way you can make sure your configuration does what you expect it to do.
  • These are preferences that are not enforced. Use sys.fn_hadr_is_preferred_replica in your backup solution to enforce the preferences. Database Maintenance Plans use this automatically
  • You will need to configure jobs on any instance that you expect could possibly be performing backups.
  • I briefly mentioned this in my last post, but it is best to have all backups write to the same network location. This will make finding all of your backup files much easier. 

In my next post I will talk a little bit about how your backup decisions can affect recovery.

Comments

  • Anonymous
    July 01, 2015
    The comment has been removed
    • Anonymous
      July 06, 2016
      The comment has been removed