Practical SQL Server

AlwaysOn Availability Groups and SQL Server Jobs, Part 24: Synchronizing SSIS Packages

In my previous post I covered options for adding “If-checks” into SSIS Packages – or SQL Server Maintenance Plans (which are, effectively, specialized SSIS packages). Once you’ve tackled that process, you’ll need to tackle something else when it comes to managing SSIS packages in conjunction with AlwaysOn Availability Groups. Or, more specifically, you’ll actually need to tackle two tasks. Specifically, what you’ll need to address is the process of synchronizing SSIS packages between the SQL Server instances that are hosting your Availability Groups and then making sure that those packages stay synchronized once deployed (to avoid any nasty surprises during updates or as changes occur).

Synchronizing SSIS Packages between Servers

In overly-simplified terms, SSIS Packages are really nothing more than ‘bundles’ of code, configuration, and directives. As such, if you can copy them from one server and ‘paste’ them over on to another server – you’ll be able to synchronize them. Again, that’s a hideously over-simplified view of the task of synchronization – simply because SSIS packages are complex and can have dependencies upon a wide assortment of environment ‘variables’. But, the general idea or concept of synchronization is pretty straight-forward in that one of the best ways to synchronize packages across multiple servers is to export them from one server, and then import that exported package up and ‘into’ any target servers where you need said package to exist as well.

An example of how to do this can be found here – in a post that shows how to copy, for example, Maintenance Plans from one server to another.

Another option – that can help with larger numbers of packages, is to use dtutil – which you can find documentation on if you search for it and/or something along the lines of “transfer SSIS packages between servers”.

Of course, please note that the options mentioned above only show how to synchronize or copy packages from one server to another. If those packages, in turn, are run via scheduled tasks – or as SQL Server Agent Jobs, you’ll have to (of course) synchronize the jobs as well. This, in turn, can be done pretty easily by scripting the jobs out from/on the source server and then deploying said scripts over to your target servers – as outlined in post #x of this series. (The point being, though, that SSIS based jobs need both the SQL Server Agent Job AND the underlying package being targeted by the job in order to run – meaning that you’ll have to synchronize or manage BOTH parts whenever you set up SSIS packages with AG databases and/or whenever changes are made either to jobs, the underlying packages, or both.)

Keeping Packages in Sync

Of course, the entire purpose of synchronization in the first place it to help ensure that if you’re using SSIS packages to manage backups or if you’ve got SSIS packages running as batch jobs (see post #3 – Defining Batch Jobs), then you’ll want to ensure that not only are jobs identical across all servers hosting your Availability Groups (so that when run, the EXACT same logic, outcomes, or operations are run), but you’ll also want to make sure that these jobs or operations ONLY run on a targeted or preferred replica. In my last post we looked at how you can easily add logic to existing SSIS packages to run if-checks to ensure that things only run on the appropriate server. And, above, I’ve provided some links and info on how to synchronize packages initially – or manually – whenever changes are made to a specific package.

A big problem, however, when you’ve got core logic or operations spanning multiple servers/hosts is to ensure that those packages or jobs stay synchronized. Or, in other words, if we assume that you’ve set up Availability Groups to manage high-availability and/or disaster recovery needs, but you’ve also got a data warehouse group that regularly runs ETL processes against key databases, then you not only need to ensure that they’re targeting the ideal/preferred replicas for their extraction operations (i.e., offloading this process to read-only replicas might initially make perfect sense – other than licensing and any kind of ‘station keeping’ logic (writes) that MIGHT end up being utilized), but you also have to help safeguard against scenarios where, say, a Junior ETL developer might make some changes to a package and upload those change to ONLY the server hosting, say, the primary replica for one of your AG databases. If such a thing happened, and wasn’t noticed, then if/when failover to another host occurred, either the newly added job uploaded by this developer wouldn’t run on the new server – or, if the SSIS package ‘change’ was a modification to an existing package, then the OLD package or logic would run against the new replica host at run time.

Neither outcome, of course, is even remotely close to desired. True, you could set up a policy or checklist to follow whenever SSIS packages are pushed, but you’ll forgive me if I’m a bit skeptical in assuming that such a process would be followed perfectly – every time. As such, the following script is something you can use to regularly query (or check) upon SSIS packages (be they regular SSIS batch jobs or be they SSIS packages used for Maintenance Plans) and report on any synchronization issues:

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

CREATE PROC dbo.dba_SyncCheckMaintenancePlans
	@MailProfileName	sysname = NULL,	-- e.g., 'General'
	@OperatorName		sysname = NULL,	-- e.g., 'Alerts', 
	@PrimaryAGAfinity	sysname = NULL, -- see comments above. 
	@IgnoredPlans		nvarchar(MAX) = '',  -- names of any maint-plans to ignore
	@ConsoleOnly		bit	= 0		-- output only to console - don't email alerts (for debugging/manual execution, etc.)
AS
	SET NOCOUNT ON;

	IF @PrimaryAGAfinity IS NOT NULL BEGIN 
		IF (SELECT dbo.fn_hadr_group_is_primary(@PrimaryAGAfinity)) = 0 BEGIN 
			PRINT 'Server is Not Primary.'
			RETURN 0;
		END
	END

	-- if we're not manually running this, make sure the server is the primary:
	IF @ConsoleOnly = 0 BEGIN -- if we're not running a 'manual' execution - make sure we have all parameters:
		IF ISNULL(@MailProfileName, '') = '' BEGIN
			RAISERROR('Procedure expects parameter ''@MailProfileName'' which was not provided.', 16, 4);
			RETURN -2;
		END

		IF ISNULL(@OperatorName, '') = '' BEGIN
			RAISERROR('Procedure expects parameter ''@OperatorName'' which was not provided.', 16, 4);
			RETURN -3;
		END
	END;

	CREATE TABLE #IgnoredPlans (
		plan_name sysname
	);

	-- single-line / in-line 'split' function:
	DECLARE @DeserializedPlans nvarchar(MAX) = N'SELECT ' + REPLACE(REPLACE(REPLACE(N'''{0}''','{0}',@IgnoredPlans), ',', ''','''), ',', ' UNION SELECT ');

	INSERT INTO #IgnoredPlans 
	EXEC(@DeserializedPlans);

	DECLARE @LocalServerName sysname = @@SERVERNAME;
	DECLARE @RemoteServerName sysname; 
	SET @RemoteServerName = (SELECT TOP 1 name FROM PARTNER.master.sys.servers WHERE server_id = 0);

	----------------------------------------------------------------------------
	-- start by getting a list of all plans called by ANY job on the server:
	DECLARE @MaintenancePlanJobs TABLE ( 
		job_name sysname NOT NULL, 
		plan_name sysname NOT NULL
	);

	-- find maint plan jobs by the following: 
	DECLARE @MaintPlanStartPattern nvarchar(100) = N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\';
	DECLARE @MaintPlanEndPattern nvarchar(100) = N'" /set "\Package\Full Backups.Disable;false"';

	WITH core AS ( 
		SELECT 
			j.name [job_name],
			REPLACE(js.command, @MaintPlanStartPattern, '') [name_plus]
		FROM msdb.dbo.sysjobsteps js
		INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
		WHERE command LIKE @MaintPlanStartPattern + '%'
	)

	INSERT INTO @MaintenancePlanJobs
	SELECT 
		job_name, 
		LEFT(name_plus, CHARINDEX('"', name_plus) - 1) [plan_name]
	FROM
		core;


	-- Now grab local/remote info about packages/plans:
	CREATE TABLE #LocalPlans (
		id uniqueidentifier, 
		name sysname, 
		createdate datetime, 
		ownersid nvarchar(128),
		vermajor int,
		verminor int, 
		verbuild int, 
		verid uniqueidentifier
	);

	CREATE TABLE #RemotePlans (
		id uniqueidentifier, 
		name sysname, 
		createdate datetime, 
		ownersid varbinary(128),
		vermajor int,
		verminor int, 
		verbuild int, 
		verid uniqueidentifier
	);

	INSERT INTO #LocalPlans
	SELECT 
		id, 
		name, 
		createdate, 
		ownersid,
		vermajor, 
		verminor, 
		verbuild, 
		verid
	FROM 
		msdb.dbo.sysssispackages
	WHERE 
		packagetype = 6; -- maintenance plan via designer/wizard

	INSERT INTO #RemotePlans
	SELECT 
		id, 
		name, 
		createdate, 
		ownersid,
		vermajor, 
		verminor, 
		verbuild, 
		verid
	FROM 
		PARTNER.msdb.dbo.sysssispackages
	WHERE 
		packagetype = 6; -- maintenance plan via designer/wizard

	-- Run comparison checks:
	CREATE TABLE #Divergence (
		name sysname, 
		diff_type sysname
	);

	INSERT INTO #Divergence
	SELECT 
		name,
		'Maintenance Plan Exists on ' + @LocalServerName + ' Only.'
	FROM 
		#LocalPlans
	WHERE
		name NOT IN (SELECT name FROM #RemotePlans);

	INSERT INTO #Divergence
	SELECT 
		name, 
		'Maintenance Plan Exists on ' + @RemoteServerName + ' Only.'
	FROM 
		#RemotePlans
	WHERE
		name NOT IN (SELECT name FROM #LocalPlans);

	INSERT INTO #Divergence
	SELECT 
		lp.name, 
		'Maintenance Plan Differences (owner, create date, version, SID, etc) between servers.'
	FROM 
		#LocalPlans lp
		INNER JOIN #RemotePlans rp ON rp.name = lp.name
	WHERE
		lp.createdate != rp.createdate 
		OR lp.ownersid != rp.ownersid
		-- OR lp.verid != rp.verid -- MKC: These'll always be different across servers
		OR lp.vermajor != rp.vermajor
		OR lp.verminor != rp.verminor
		OR lp.verbuild != rp.verbuild;

	-- Report on any errors:
	IF (SELECT COUNT(*) FROM #Divergence WHERE name NOT IN (SELECT name FROM #IgnoredPlans)) > 0 BEGIN 
		
		DECLARE @subject sysname = 'SQL Server Maintenance Plan Synchronization Problems';
		DECLARE @crlf char(2) = CHAR(13) + CHAR(10);
		DECLARE @tab char(1) = CHAR(9);
		DECLARE @message nvarchar(MAX) = 'Problems detected with the following Maintenance Plans: '
		+ @crlf;

		SELECT 
			@message = @message + @tab + name + ': ' + @crlf + @tab + @tab + diff_type + @crlf
		FROM 
			#Divergence
		WHERE
			name NOT IN (SELECT plan_name FROM #IgnoredPlans WHERE plan_name != '')
		ORDER BY 
			NAME;

		SELECT @message += @crlf + @crlf + 'Maintenance Plans must be synchronized by Exporting them from SSIS as File System packages and then re-importing them to the target server.'
			+ @crlf + @tab + 'Once Maintenance Plans have been synchronized, you should synchronize corresponding Jobs derived from the plans as well.'
			+ @crlf + @tab + 'Please consult documentation for additional help with synchronizing Maintenance Plans.';

		IF @ConsoleOnly = 1 BEGIN 
			-- just Print out details:
			PRINT 'SUBJECT: ' + @subject;
			PRINT 'BODY: ' + @crlf + @message;

		  END
		ELSE BEGIN
			-- send a message:
			EXEC msdb..sp_notify_operator 
				@profile_name = @MailProfileName, 
				@name = @OperatorName, 
				@subject = @subject,
				@body = @message;
		END;

	END

	DROP TABLE #LocalPlans;
	DROP TABLE #RemotePlans;
	DROP TABLE #Divergence;
	DROP TABLE #IgnoredPlans;

	RETURN 0;

Importantly, the code listed above is geared SOLELY towards checking up on Maintenance Plans – hence the name. (And, as I mentioned in my previous post, if you’re using Maintenance Plans for anything other than backups, you really need to reconsider what you’re doing – and if you are using Maintenance Plans for backups, then you’re probably better off using the script I provided in my last post.)

But, the point is that the code above can serve as a reference for some of the types of logic you can use to target either specific types of SSIS Packages (based on names or locations) or, even, how you could checkup on ALL SSIS packages if you so needed. And, to get a sense for HOW you’d use the logic above for regular synchronization checks, you’ll want to skim/review Posts 14 – 17 in this series – where I outline the basics of sync-checks along with some concrete examples of implementation options as well.

Otherwise, do be aware that I’m only scratching the surface with SSIS Synchronization tasks here; providing, if you will, a high-level framework for how to tackle this topic. I’ve used this pattern or approach in production with decent success. But there have also been headaches and hiccups along the way. SSIS Packages can be absurdly complex (and fickle/brittle) in many cases – meaning that EVEN if package level details are IDENTICAL between servers, that’s not always going to mean that an SSIS package will actually run on a server where it hasn’t been tested (simply because connection details, paths to files/folders (or security governing access to those paths) may be totally different or not 100% synchronized). So, in short: make sure that if you’re using SSIS packages in conjunction with Availability Group databases that you, of course, make sure that connections into your AG databases are handled via your Availability Group Listeners, and then (the point of this and related posts) make sure that your packages have proper if/else logic as needed (or that you simply turn jobs on/off as needed) and that your jobs/packages will actually run from one server to another. And the only way to ensure that last point is by testing – something you should have become very comfortable with once AGs have been put into your environment.

Up Next: We’ll look at some more advanced concerns and considerations for things outside ‘simple 2 node’ AG topologies and look at other ways to address some of the topics and tasks I’ve covered to date.

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
Part 23: Maintenance Plan Backups and SSIS Packages

 

 

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