SQL Server Integration Services (SSIS) High Availability (some ideas I got from Steve Howard - MSFT PFE)

Recently a topic came up on SSIS High Availability. This is not a typical SSIS configuration. Steve Howard (Microsoft PFE) has done a lot of work on this topic and has come up with some interesting ideas. I am going to try to pass on what I learned. It may not be perfect (OK, it is probably a total hackjob), but hopefully it captures something you can consider in trying HA SSIS.

Here is an interesting way to make SSIS and clustering work together in a kind of HA setup. The goal is to have the SSIS package running under SQL Server Agent create a checkpoint so that if it fails on node a, it will pick it up on node b, since the SQL Agent is cluster aware. There should be a common folder on both nodes (ie C:\SSIS) that has the SSIS package. The strategy will be to have 2 SSIS packages. One will be called SkedAtStartup and one Will be Called MainPkg. The main goal of the SkedAtStartup package is to check for the checkpoint file. If it exists, it runs the MainPkg package thru an Execute Package task. If the checkpoint file does not exist, it does not run the MainPkg thru the ExecutePackage task - it just terminates. The precedence constraint below is designed to implement the logic to fire off the ExecutePackage task if the checkpoint file is found. The MainPkg will use checkpoints for all the control flow tasks and will have the config for the CheckPointPath var, the ServerName in the connection, and the InitialCatalog (not tempdb). The SkedAtStartup will be called when the cluster fails over to node b. The strategy is to leverage the fact that the SQL Agent will failover and run the SkedAtStartUp job, and the fact that you are logging the steps that were completed so that when the SkedAtStartup runs, it will pick up where the first node left off.

1. Setup a Windows and SQL Server Cluster
2. Make sure that the SQL Agent job is running
3. Setup a common SSIS package on both nodes to run under the context of a SQL Server Agent Job
4. The package should use checkpoints. It should pick up where the node a one left off if a checkpoint file exists.
5. Here are some screenshots from Steve's email:

 

 

 

Also, Steve pointed out that you can manage multiple SSIS instances from a singe SQL Server Instance by updating the MsDtsSrvr.ini.xml file per the below:

 

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>SQL 2008 MSDB</Name>

      <ServerName>.\SQL2K8</ServerName>

    </Folder>

    <Folder xsi:type="SqlServerFolder">

      <Name>SQL 2005 MSDB</Name>

      <ServerName>.</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

 

Here are some other posts on the topic:

https://dougbert.com/blogs/dougbert/archive/2009/04/09/ssis-and-clustering-what-you-should-do-instead.aspx

https://msdn.microsoft.com/en-us/library/bb522568.aspx

https://msdn.microsoft.com/en-us/library/ms345193.aspx