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:

In each case, though, regardless of whether DBAs are specifying backup preferences and details via a GUI (such as with a 3^rd 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.';  ENDELSE BEGIN PRINT 'This is NOT the primary. Don''t do anything here.';END

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:

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 3^rd 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 3^rd 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 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
Part 21: Assigning Backup Preferences