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 NULLDROP PROC dbo.dba_SyncCheckMaintenancePlansGOCREATE PROC dbo.dba_SyncCheckMaintenancePlans@MailProfileNamesysname = NULL,-- e.g., 'General'@OperatorNamesysname = NULL,-- e.g., 'Alerts', @PrimaryAGAfinitysysname = NULL, -- see comments above. @IgnoredPlansnvarchar(MAX) = '',  -- names of any maint-plans to ignore@ConsoleOnlybit= 0-- output only to console - don't email alerts (for debugging/manual execution, etc.)ASSET 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;ENDEND-- 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, '') = '' BEGINRAISERROR('Procedure expects parameter ''@MailProfileName'' which was not provided.', 16, 4);RETURN -2;ENDIF ISNULL(@OperatorName, '') = '' BEGINRAISERROR('Procedure expects parameter ''@OperatorName'' which was not provided.', 16, 4);RETURN -3;ENDEND;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 "PackageFull Backups.Disable;false"';WITH core AS ( SELECT j.name [job_name],REPLACE(js.command, @MaintPlanStartPattern, '') [name_plus]FROM msdb.dbo.sysjobsteps jsINNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_idWHERE command LIKE @MaintPlanStartPattern + '%')INSERT INTO @MaintenancePlanJobsSELECT job_name, LEFT(name_plus, CHARINDEX('"', name_plus) - 1) [plan_name]FROMcore;-- 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 #LocalPlansSELECT id, name, createdate, ownersid,vermajor, verminor, verbuild, veridFROM msdb.dbo.sysssispackagesWHERE packagetype = 6; -- maintenance plan via designer/wizardINSERT INTO #RemotePlansSELECT id, name, createdate, ownersid,vermajor, verminor, verbuild, veridFROM PARTNER.msdb.dbo.sysssispackagesWHERE packagetype = 6; -- maintenance plan via designer/wizard-- Run comparison checks:CREATE TABLE #Divergence (name sysname, diff_type sysname);INSERT INTO #DivergenceSELECT name,'Maintenance Plan Exists on ' + @LocalServerName + ' Only.'FROM #LocalPlansWHEREname NOT IN (SELECT name FROM #RemotePlans);INSERT INTO #DivergenceSELECT name, 'Maintenance Plan Exists on ' + @RemoteServerName + ' Only.'FROM #RemotePlansWHEREname NOT IN (SELECT name FROM #LocalPlans);INSERT INTO #DivergenceSELECT lp.name, 'Maintenance Plan Differences (owner, create date, version, SID, etc) between servers.'FROM #LocalPlans lpINNER JOIN #RemotePlans rp ON rp.name = lp.nameWHERElp.createdate != rp.createdate OR lp.ownersid != rp.ownersid-- OR lp.verid != rp.verid -- MKC: These'll always be different across serversOR lp.vermajor != rp.vermajorOR lp.verminor != rp.verminorOR 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 + @crlfFROM #DivergenceWHEREname 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;  ENDELSE BEGIN-- send a message:EXEC msdb..sp_notify_operator @profile_name = @MailProfileName, @name = @OperatorName, @subject = @subject,@body = @message;END;ENDDROP 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 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
Part 23: Maintenance Plan Backups and SSIS Packages