AlwaysOn Availability Groups and SQL Server Jobs, Part 21: Assigning Backup Preferences

An option when creating new AlwaysOn Availability Groups is to specify Backup Preferences.

It’s also something you can easily configure once the Availability Group has been set up as well – by simply right-clicking on the Availability Group and selecting Properties – then navigating into the Backup Preferences tab.  

And, while some aspects of these preferences might seem a bit obscure – at least initially – the reality is that setting these preferences is pretty trivial. So much so that Microsoft’s documentation on them is actually sufficient to provide an overview of the options available.

There are two pretty substantial caveats though.

Caveat the First: Licensing

While the documentation that Microsoft provides to manage Backup Preferences for AlwaysOn Availability Group databases, it doesn’t really call out that most of the options involved require the use of additional licenses. Or, in other words, if you’re setting up a ‘simple’ 2-node Availability Group for High Availability Purposes only (at which point a FCI might make more sense in some scenarios), then the only option you’ll really be able to specify is “Primary” – or that you’d like your backups made on or against the server hosting your Primary Replica. Anything else is or would be rightly considered a scale-out scenario – meaning that you’ll need an additional license in play to support scale-out operations. (So, in other words, if you’re thinking about trying to “offload” backups from your “busy” or primary server on to your secondary server to help “spread the load”, that’s a totally viable option – but it’s NOT a simple failover scenario and is, therefore, not covered by typical Software Assurance licensing which provides a ‘free’ failover license or host per every fully licensed primary/active host.)

Caveat the Second: Preferences Don’t Actually Do Anything

Interestingly enough, while you can “mix and match” and set priorities and preferences all day long on the Backup Preferences tab (or directly via T-SQL), nothing you do with or about these preferences really translates into anything that SQL Server “has” to obey or will even check.

Instead, as Books Online calls out:

There is no enforcement of the automated backup preference setting. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups. For more information, see see Follow Up: After Configuring Backup on Secondary Replicas later in this topic.

Or, in other words, while you can specify preferences as part of an Availability Group’s settings – and, of course, query those details (in a round-about way), the reality is that if you want your backups to “obey” or even “consider” these preferences, you’ll have to code that functionality yourself. Which, in turn, means that you’ll have to wire-up additional logic around your backups to pay attention to these preferences, and then backup (or not backup) accordingly.

The problem, of course, is that there are a number of different ways in which you can actually create, or run, your backups – either from within SQL Server Maintenance Plans, via scripts, or via 3^rd party backup tools and solutions.

As such, we’ll take a look at some of these options – and some specific challenges in terms of implementation details – in our next post.

Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
Part 5: Setting Up Failover Alerts
Part 6: High-Level Options and Rationale for Handling Batch Jobs
Part 7: Detecting Primary Replica Ownership
Part 8: Dynamic Detection Gotcha #1
Part 9: Dynamic Detection Gotcha #2
Part 10: Dynamic Detection Gotcha #3
Part 11: A Fork in the Road
Part 12: Issues with Enabling and Disabling Batch Jobs
Part 13: Creating and Populating a Batch Job State Table
Part 14: Establishing Synchronization Checks
Part 15: Setting Up Linked Servers
Part 16: Job Synchronization Checkup Logic
Part 17: Creating Jobs to Check on Synchronization
Part 18: Health Checks for Availability Groups
Part 19: Availability Group Database Backups
Part 20: Avoiding Backup Fragmentation