Practical SQL Server

AlwaysOn Availability Groups and SQL Server Jobs, Part 22: Executing Backups

Ultimately, there are a number of ways that DBAs can regularly create backups of their databases. Some of the more common options for managing backups include:

  • Manual scripts (either in T-SQL or via Powershell or something similar) to execute backups on a given schedule as well as to remove older backups to conserve disk space.
  • SQL Server Maintenance Plans. (More on this in a follow-up post.)
  • Specialized 3rd Party SQL Server Backup Tools/Solutions to tackle things like multi-server backups, encryption, and (pre-SQL Server 2008/2008R2) backup compression. (And please note that I’m not talking about 3rd party backup tools primarily devoted to ‘file’ backups that come with ‘SQL Server’ add-on components; because while SOME of these solutions work fairly well, others do insanely stupid things with databases and are, frankly, an abomination. As such, I just don’t have time to cover those kinds of tools in this series of posts.)
  • 3rd party scripts – like Ola Hallengren’s excellent SQL Server Backup solution – that can be used to manage backups and other maintenance routines (including support for managing backups via 3rd party backup solutions).

In each case, though, regardless of whether DBAs are specifying backup preferences and details via a GUI (such as with a 3rd party backup tool or when using SQL Server Maintenance Plans), or manually plugging values and preferences into a set of scripts (their own or, say, something like Ola’s), the end-result is that DBAs end up with a set of T-SQL Commands that end up being executed against target SQL Servers in order to kick-off backups and to manage any other related tasks along the way.

As such, the concern when dealing with AlwaysOn Availability Groups, is to make sure that actual backup commands are only executed on target or preferred servers – as outlined in my previous post on Assigning Backup Preferences. Implementation, though, depends upon modification to existing commands to ensure that they check or evaluate whether the code being run is being executed on the preferred server or not – and then acts accordingly.

Managing Backups with AlwaysOn Availability Group Preferred Replicas

Happily, SQL Server 2012 and higher ship with a mechanism to easily evaluate whether or not code is being run on the ‘preferred’ server – in the form of sys.fn_hadr_backup_is_preferred_replica. Examples of how to run ‘if checks’ using this Function can be found here – only, the examples listed include the RETURN operator – something that you can’t use within a SQL Server Agent Job Step (i.e., the example used in the sample documentation was clearly written to be part of a stored procedure that manages backups – something that I’m guessing few DBAs will actually do). Still, the general idea of how to use this function is pretty simple – as shown by the following/sample pseudo-code:

If sys.fn_hadr_backup_is_preferred_replica('nameOfTargetDBHere') = 1 BEGIN 
	PRINT 'This is the primary - execute backups here.';
	PRINT 'This is NOT the primary. Don''t do anything here.';

All in all, this pattern or technique for evaluating whether or not backups should be executed or not on the current server is pretty tame. The trick comes from the fact that you’ll need to do the following:

  • Set up backup jobs on all servers participating in your Availability Group.
  • Schedule the jobs on ALL servers to run, effectively, at either the same time or at the EXACT same time.
  • Tweak all existing job code/executions to evaluate for the preferred backup replica using sys.fn_hadr_backup_is_preferred_replica (in association with having already specified replica preferences as per my last post).
  • Avoid one additional caveat / gotcha when using this function – which is that you’ll need to make sure to correctly spell (and collate) the name(s) of the database(s) you’re evaluating and backing up – as per this connect item.

Otherwise, the only real trouble in setting up the actual code to tackle your backups is ‘injecting’ the ‘if checks’ into whatever routine you’re using already. So, for example, if you’re using 3rd Party SQL Server backup tools (like backup solutions from Redgate, Idera, LightSpeed, Apex SQL, and so on), then you’ll need to find the actual T-SQL being created/executed by those jobs (across all of your AlwaysOn Availability Group hosts) and ‘wrap’ whatever’s being called and executed in ‘if check’ logic that only actually executes the backup if the server in question is hosting the preferred replicas.

Worse, you’ll also have to make sure that if you ‘customize’ any of the SQL Server Agent Jobs you’ve set up via your 3rd party backup solutions, that you pay attention and make sure that any subsequent changes to your backup plans/settings don’t overwrite the ‘customizations’ or directives you’ve put in place – otherwise you can/could lose your ‘if checks’ in the future and that would lead to some potential nightmares or even disasters.

And, of course, the notion of injecting if-checks into your existing commands all works fine and well (albeit potentially a bit tediously) if you’re using anything other than SQL Server Maintenance Plans. Which, in turn, is why we’ll be looking at those in more depth in my next post.

Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
Part 5Setting 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
Part 21: Assigning Backup Preferences

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.