Skip navigation
Practical SQL Server

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:

  • To Not To. i.e., to quote Mader from the Pixar Films cars when asked what he was told about interacting with certain people, he mentioned he was told “to not to”. And, in short, that can apply as well to Maintenance Plans – meaning that if you’re just using them for backups (which is about the only thing I recommend them for), then your easiest option in tackling this task when it comes to managing backups on your Availability Group databases is simply to NOT use Maintenance Plans – as you’ll see they’re way more complex than they need to.
  • Interject SSIS IF/ELSE Logic into your SSIS Package. Otherwise, you’re left with the task of editing your SSIS packages to use conditional logic – something that’s pretty easy in itself, but something that does tend to add a tiny bit more complication to your packages. Not really too much of a concern for Maintenance Plans – but something that might be a problem/issue if you’ve got large numbers of SSIS packages being used for ‘batch jobs’ or other tasks.  

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:\SQLBackups\System',
		@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:\SQLBackups\User',
		@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:\SQLBackups\User',
		@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:\SQLBackups\User',
		@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:\SQLBackups\User',
		@OlderBackupDeletionTime = @olderThan;
	GO

*/


USE master;
GO

IF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULL
	DROP PROC dbo.dba_DatabaseBackups;
GO

CREATE PROC dbo.dba_DatabaseBackups
	@BackupType					sysname, 
	@DatabasesToBackup			nvarchar(1000), 
	@BackupDirectory			sysname, 
	@OlderBackupDeletionTime	datetime, 
	@PrintOnly					bit		= 0
AS
	SET 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);
	END

	IF @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 #Tally
		FROM 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 #Tally
		WHERE N < LEN(@SerializedDbs) 
			AND SUBSTRING(@SerializedDbs, N, 1) = ',';

		IF @BackupType = 'LOG' BEGIN
			DELETE FROM @targetDatabases 
			WHERE database_name NOT IN (
				SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL'
			);
		  END
		ELSE 
			DELETE FROM @targetDatabases
			WHERE 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 
		@targetDatabases
	ORDER 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 
		ELSE
			EXECUTE 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,');
			ELSE
				SET @backupStatement = REPLACE(@backupStatement, '{1}', '');
		  END
		ELSE BEGIN -- log file backup
			SET @backupStatement = REPLACE(@backupStatement, '{0}', 'LOG');
			SET @backupStatement = REPLACE(@backupStatement, '{1}', '');
		END

		SET @verifyStatement = 'RESTORE VERIFYONLY FROM DISK = N''' + @backupPath + '\' + @backupName + ''' WITH NOUNLOAD, NOREWIND;';

		BEGIN TRY
			IF @PrintOnly = 1 BEGIN 
				PRINT @backupStatement;
				PRINT @verifyStatement;
			  END
			ELSE BEGIN 
				EXEC sp_executesql @backupStatement;
				EXEC sp_executesql @verifyStatement;
			END

		END TRY 
		BEGIN CATCH
			SELECT @ErrorMessage = ERROR_MESSAGE();

			INSERT INTO @Errors ([Database], ErrorMessage)
			VALUES  (@currentDB, @ErrorMessage);
		END CATCH

FETCH 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 @deleteStatement
		ELSE
			EXEC sp_executesql @deleteStatement; 
	END TRY 
	BEGIN CATCH
		SELECT @ErrorMessage = ERROR_MESSAGE();

		INSERT INTO @Errors ([Database], ErrorMessage)
		VALUES  ('File Deletion', @ErrorMessage);
	END CATCH

	IF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN 
		PRINT 'The Following Errors were Detectected: ';

		DECLARE errors CURSOR FAST_FORWARD FOR 
		SELECT [Database],[ErrorMessage] 
		FROM @Errors
		ORDER 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);
	END

	RETURN 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.

  1. Add in a new Execute T-SQL Statement Task. And drop in an IF check that’ll raise or throw an error if the server in question isn’t your preferred target.
  2. Make sure you’re using an “on success” precedence operator from this task into the remaineder of your workflow. Because this’ll basically ensure that if you’re not on the server you want/need to execute on, that your T-SQL task will throw an error, and your logic won’t “branch” down to the rest of your package.

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

Hide comments

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.
Publish