AlwaysOn Availability Groups and SQL Server Jobs, Part 23: Maintenance Plan Backups and SSIS Packages

In my last post, we took a look at some of the details involved in actually implementing backups against databases being hosted in AlwaysOn Availability Groups. In addition to providing a high-level overview of how sys.fn_hadr_backup_is_preferred_replica() works, I also mentioned that integrating it into backups managed by SQL Server Maintenance Plans isn’t as easy as what you’ll run into with other types of backups.

As such, in this post I’ll provide a quick overview of the pros/cons of integrating if/else checks into SQL Server Maintenance Plans (for backups) – and, by extension, into SSIS packages in general. As you’ll see, this isn’t rocket science – but there are a few things to be mindful of. Along those lines, we should probably start off our discussion with a quick ‘detour’ into the feasibility of using SQL Server Maintenance Plans in general.

Maintenance Plans In Perspective – Good and Bad (Mostly Bad)

As a bit of an aside, I’ve got quite a bit to say when it comes to SQL Server Maintenance Plans. First: I think that the infrastructure upon which Maintenance Plans were built is pretty powerful. Or, in other words, because SQL Server Maintenance Plans are built upon SQL Server Integration Services (SSIS), DBAs pick up some serious power in being able to break complex operations up into small, discrete, tasks, set up precedence operators between them, and generally tackle a whole host of problems via a very sophisticated framework.

The problem, however, is that while the architecture provided for managing Maintenance Plans is arguably quite powerful, the ‘tasks’ provided via Maintenance Plans were either written by interns or totally clueless morons. Now, if that sounds harsh, look no further than the Reorganize and Rebuild Index Tasks provided out of the box with Maintenance Plans. Both allow a minimum of preferences to be set (i.e., which databases to target and whether or not to sort results in the tempdb and/or to rebuild indexes online/offline when it comes to the Rebuild Task), but don’t allow any type of specification about WHICH indexes to rebuild or reorganize. Consequently, these jobs simply rebuild/reorganize EVERY, SINGLE, index in your target database(s) – whether maintenance is needed or not.

In light of this serious failure – coupled with some other failures – it’s easy to see why so many veteran DBAs absolutely despise SQL Server Maintenance Plans. Personally, I’m not QUITE to the point of despising them – because I do, personally, think that the Backup Database Task can be used fairly easily to create very solid and reliable backup plans when organizations don’t have a third party backup solution. That, and they DO provide the ability to put backups from specific databases into their own folders – something I value quite a bit.

That said, I typically recommend that if/when clients want to use SQL Server Maintenance Plans within their environments that they a) pretty much “don’t use it for anything other than backups”, and b) if they do use it for backups, they take extra care to make sure they’re only tackling backups and backups alone (instead of trying to intermingle other tasks along with their backups). As such, while I don’t openly despise SQL Server Maintenance Plans like many of my peers, I’m not a huge fan – even though I have made a bit of peace with them when it comes to backups.

Otherwise, when it comes to using Maintenance Plans (for backups) or to using SSIS Packages against Availability Group databases, there are a couple of key things you’ll need to tackle – like determining when a job should fire (i.e., if it’s on the Primary Replica or not) as well as keeping Job details synchronized between servers. Synchronization, it turns out, is a bit of a pain in the butt – so we’ll tackle that in my next post.

How to Tackle Maintenance Plan Execution Logic

As mentioned in my last post, with most backups you simply find the code that sends commands off for backups, and wrap that with if/else logic leveraging sys.fn_hadr_backup_is_preferred_replica() to determine if backups should be run on the current host or not. With SQL Server Maintenance Plans, you don’t get the option of actually viewing or modifying the code created by your Maintenance Plan. Instead, the commands, details, and settings you specify become wrapped up as part of a SQL Server Integration Services (SSIS) package that gets executed on your server when schedules and which then generates commands at run time – which it then sends to the server.

As such, you’re left with two primary options for how to tackle if/else checks when it comes to managing backups with Maintenance Plans:

If you’d like an alternative to using SQL Server Maintenance Plan logic for backups, you can take a look at the following script. It achieves functional parity with the backup components provided via Maintenance Plans – in terms of the outcome.

/*-- This sproc replaces SQL Server Maintenance Task Backups. -- It just requires a path, a list of dbs to backup, and the kind of backup... --as well as a timestamp for stuff older than X to delete. -- NOTE: on Express and Web servers... COMPRESSION isn't supported. -- System Database Backups: DECLARE @olderThan datetime; SET @olderThan = DATEADD(dd, -3, GETDATE());EXEC dbo.dba_DatabaseBackups@BackupType = 'FULL', @DatabasesToBackup = '[SYSTEM_DBS]', @BackupDirectory = 'D:SQLBackupsSystem',@OlderBackupDeletionTime = @olderThan;GO-- Full backups of ALL User Databases: DECLARE @olderThan datetime; SET @olderThan = DATEADD(hh, -48, GETDATE());EXEC dbo.dba_DatabaseBackups@BackupType = 'FULL', @DatabasesToBackup = '[USER_DBS]', @BackupDirectory = 'D:SQLBackupsUser',@OlderBackupDeletionTime = @olderThan;GO-- Full backups of SPECIFIED User Databases: DECLARE @olderThan datetime; SET @olderThan = DATEADD(hh, 25, GETDATE());EXEC dbo.dba_DatabaseBackups@BackupType = 'FULL', @DatabasesToBackup = 'meddling,ssv2', @BackupDirectory = 'D:SQLBackupsUser',@OlderBackupDeletionTime = @olderThan;GO-- DIFF backups of SPECIFIED Databases:DECLARE @olderThan datetime; SET @olderThan = DATEADD(hh, -48, GETDATE());EXEC dbo.dba_DatabaseBackups@BackupType = 'DIFF', @DatabasesToBackup = 'meddling,ssv2', @BackupDirectory = 'D:SQLBackupsUser',@OlderBackupDeletionTime = @olderThan;GO-- T-Log Backups of all User DBs: DECLARE @olderThan datetime; SET @olderThan = DATEADD(hh, -36, GETDATE());EXEC dbo.dba_DatabaseBackups@BackupType = 'LOG', @DatabasesToBackup = '[USER_DBS]', @BackupDirectory = 'D:SQLBackupsUser',@OlderBackupDeletionTime = @olderThan;GO*/USE master;GOIF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULLDROP PROC dbo.dba_DatabaseBackups;GOCREATE PROC dbo.dba_DatabaseBackups@BackupTypesysname, @DatabasesToBackupnvarchar(1000), @BackupDirectorysysname, @OlderBackupDeletionTimedatetime, @PrintOnlybit= 0ASSET NOCOUNT ON;DECLARE @jobStart datetime; SET @jobStart = GETDATE();-- verify IF UPPER(@BackupType) NOT IN ('FULL', 'DIFF','LOG') BEGIN PRINT 'Usage: @BackupType = FULL|DIFF|LOG';RAISERROR('Invalid @BackupType Specified.', 16, 1);ENDIF @OlderBackupDeletionTime >= GETDATE() BEGIN RAISERROR('Invalid @OlderBackupDeletionTime - greater than or equal to NOW.', 16, 1); END-- determine databases: DECLARE @targetDatabases TABLE ( database_name sysname NOT NULL); IF UPPER(@DatabasesToBackup) = '[SYSTEM_DBS]' BEGIN INSERT INTO @targetDatabases (database_name)SELECT 'master' UNION SELECT 'msdb' UNION SELECT 'model';END IF UPPER(@DatabasesToBackup) = '[USER_DBS]' BEGIN IF @BackupType = 'LOG'INSERT INTO @targetDatabases (database_name)SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL' AND name NOT IN ('master', 'model', 'msdb', 'tempdb') ORDER BY name;ELSE INSERT INTO @targetDatabases (database_name)SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb','tempdb') ORDER BY name;END IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN -- deserialize the list of databases to backup: SELECT TOP 400 IDENTITY(int, 1, 1) as N INTO #TallyFROM sys.columns;DECLARE @SerializedDbs nvarchar(1200);SET @SerializedDbs = ',' + REPLACE(@DatabasesToBackup, ' ', '') + ',';INSERT INTO @targetDatabases (database_name)SELECT SUBSTRING(@SerializedDbs, N + 1, CHARINDEX(',', @SerializedDbs, N + 1) - N - 1)FROM #TallyWHERE N < LEN(@SerializedDbs) AND SUBSTRING(@SerializedDbs, N, 1) = ',';IF @BackupType = 'LOG' BEGINDELETE FROM @targetDatabases WHERE database_name NOT IN (SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL');  ENDELSE DELETE FROM @targetDatabasesWHERE database_name NOT IN (SELECT name FROM sys.databases);END-- verify that we've got something: IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN PRINT 'Usage: @DatabasesToBackup = [SYSTEM_DBS]|[USER_DBS]|dbname1,dbname2,dbname3,etc';RAISERROR('No databases for backup.', 16, 1);END-- normalize path: IF(RIGHT(@BackupDirectory, 1) = '')SET @BackupDirectory = LEFT(@BackupDirectory, LEN(@BackupDirectory) - 1);-- Begin the backups:DECLARE backups CURSOR FAST_FORWARD FOR SELECT database_name FROM @targetDatabasesORDER BY database_name;DECLARE @currentDB sysname;DECLARE @backupPath sysname;DECLARE @backupStatement nvarchar(2000);DECLARE @backupName sysname; DECLARE @now datetime;DECLARE @timestamp sysname;DECLARE @extension sysname;DECLARE @offset sysname;DECLARE @verifyStatement nvarchar(2000);DECLARE @Errors TABLE ( ErrorID int IDENTITY(1,1) NOT NULL, [Database] sysname NOT NULL, ErrorMessage nvarchar(2000));DECLARE @ErrorMessage sysname;OPEN backups;FETCH NEXT FROM backups INTO @currentDB; WHILE @@FETCH_STATUS = 0 BEGIN SET @backupPath = @BackupDirectory + N'' + @currentDB; -- make sure the subdirectory exists: IF @PrintOnly = 1 BEGIN PRINT 'Verify/Create Directory: ' + @backupPath;  END ELSEEXECUTE master.dbo.xp_create_subdir @backupPath;-- create a backup name: SET @extension = '.bak';IF @BackupType = 'LOG'SET @extension = '.trn';SET @now = GETDATE();SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(sysname, @now, 120), '-','_'), ':',''), ' ', '_');SET @offset = RIGHT(CAST(CAST(RAND() AS decimal(12,11)) AS varchar(20)),7);SET @backupName = @currentDB + '_backup_' + @timestamp + '_' + @offset + @extension;-- primary diff between this and a maintenance plan backup is: CHECKSUM... SET @backupStatement = 'BACKUP {0} ' + QUOTENAME(@currentDB, '[]') + ' TO DISK = N''' + @backupPath + '' + @backupName + ''' WITH {1} NOFORMAT, NOINIT, NAME = N''' + @backupName + ''', SKIP, REWIND, NOUNLOAD, CHECKSUM, STATS = 10;'IF @BackupType IN ('FULL', 'DIFF') BEGIN SET @backupStatement = REPLACE(@backupStatement, '{0}', 'DATABASE');IF @BackupType = 'DIFF'SET @backupStatement = REPLACE(@backupStatement, '{1}', 'DIFFERENTIAL,');ELSESET @backupStatement = REPLACE(@backupStatement, '{1}', '');  ENDELSE BEGIN -- log file backupSET @backupStatement = REPLACE(@backupStatement, '{0}', 'LOG');SET @backupStatement = REPLACE(@backupStatement, '{1}', '');ENDSET @verifyStatement = 'RESTORE VERIFYONLY FROM DISK = N''' + @backupPath + '' + @backupName + ''' WITH NOUNLOAD, NOREWIND;';BEGIN TRYIF @PrintOnly = 1 BEGIN PRINT @backupStatement;PRINT @verifyStatement;  ENDELSE BEGIN EXEC sp_executesql @backupStatement;EXEC sp_executesql @verifyStatement;ENDEND TRY BEGIN CATCHSELECT @ErrorMessage = ERROR_MESSAGE();INSERT INTO @Errors ([Database], ErrorMessage)VALUES  (@currentDB, @ErrorMessage);END CATCHFETCH NEXT FROM backups INTO @currentDB; END;CLOSE backups;DEALLOCATE backups;-- Now cleanup any/all files as needed: DECLARE @deleteStatement sysname; SET @deleteStatement = 'EXECUTE master.dbo.xp_delete_file 0, N''' + @BackupDirectory + ''', N''' + REPLACE(@extension, '.','') + ''', N''' + REPLACE(CONVERT(nvarchar(20), @OlderBackupDeletionTime, 120), ' ', 'T') + ''', 1;';BEGIN TRY IF @PrintOnly = 1 PRINT @deleteStatementELSEEXEC sp_executesql @deleteStatement; END TRY BEGIN CATCHSELECT @ErrorMessage = ERROR_MESSAGE();INSERT INTO @Errors ([Database], ErrorMessage)VALUES  ('File Deletion', @ErrorMessage);END CATCHIF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN PRINT 'The Following Errors were Detectected: ';DECLARE errors CURSOR FAST_FORWARD FOR SELECT [Database],[ErrorMessage] FROM @ErrorsORDER BY ErrorID;OPEN errors; FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'DATABASE/OPERATION: ' + @currentDB + ' -> ' + @ErrorMessage;FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;END CLOSE errors;DEALLOCATE errors;-- Raise an error so we know there were problems:RAISERROR('Unexpected errors executing backups - see output.', 16, 1);ENDRETURN 0;GO

Otherwise, you’re left with the task of establishing IF/ELSE Checks in SSIS.

Conditional Checks for AlwaysOn Replica Preferences within SSIS Packages

Fortunately, tackling this is fairly easy. Unfortunately, taking this approach means that you’re going to have to ‘litter’ your packages with some IF/ELSE checks that will add a tiny bit of additional complexity.

To tackle this, you really only need to make two additional changes to your SSIS packages or SQL Server Maintenance Plans.

To put that into perspective, here’s a screenshot of what your Maintenance Plan might look BEFORE you modify it:

Then, here it is after you modify it:

And note how the precedence operator is set up to only ‘flow’ on success.

Likewise, here’s a screenshot of the Execute T-SQL Statement Task itself – where you can see that I’m throwing an error if/when the server in question is NOT hosting the preferred replica.

Of course, in this case, we’re dealing with SQL Server Maintenance Plans – or backups. If you’re working with SSIS packages being run or executed as batch jobs (see post #3 – Defining Batch Jobs), then you’ll probably want to use different logic than determining whether or not the server is hosting your preferred replica. As such, you can use some of the logic outlined in post #7 – Detecting Primary Replica Ownership, as a starting point for some options to consider in terms of determining how you’ll find and determine which servers to run your SSIS packages off of. (Though, just to be PERFECTLY clear, we’re talking about scheduling or not whether SSIS packages should be run or not and NOT talking about how to determine which of your hosts to target for data operations as targeting should ALL be done against your Availability Group Listener.)

Finally, once you’ve added this IF check into your plans (make sure you modify all sub-plans) or once you’ve plunked this change into your SSIS packages being used for batch job processing, then you’ll need to tackle one other task that is actually ugly: synchronizing these changes across all of the servers hosting your Availability Group Databases. In my next post we’ll look at some of the pitfalls associated with synchronization – and help firm up the case for why Maintenance Plans for backups are pretty much “not worth it” when dealing with Availability Group databases.

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
Part 22: Executing Backups