High Availability Integration Services in SQL server

I see a lot of people asking about how to make integration services highly available and there isn’t an easy answer, so let me explain why.

Integration services (SSIS) is designed to bulk move a set of data from a source to a target while performing some sort of translation on the way.  The most often causes of failure for SSIS packages I have seen are, in order:

1. The source is not available. This can because a process upstream of the SSIS package has not completed to create the target, a change in credentials or an actual issue with the source

2. The target is not available.  Credentials or networking issues are the main culprits

3. There has been some change to the structure of the source or the data in it , not anticipated in the design of the package.

No SSIS high availability solution is going to be able to mitigate these risks, so you end up protecting what you can by making the source and targets highly available and then try and figure out some way of doing the same for SSIS which doesn’t have any inherent high availability built in e.g. it is not cluster aware.

If this is something you do want to do then I would run SSIS on two separate servers and put tests and logging in the packages such that if a package runs on server B it checks to see it has not already been run on server A.  In this scenario it’s important to ensure each package can be rerun from the beginning without corrupting any data (something which is good practice anyway).  It is also possible to set up load balancing for SSIS(https://technet.microsoft.com/en-us/library/ms345184.aspx), and store logging information centrally (which could be on a cluster).

Another thing to consider if this flow of data is mission critical and not too large is to use a completely different approach like BizTalk.

Technorati Tags: Integratoin services,high availability,sql server