SSIS on a cluster
I figured this was worth a quick post: while many of SQL Server’s aspects are cluster aware, SSIS is not. Why? My guess is that there is not a pressing business need for this to be a cluster aware service – while many companies have vital ETL processes to help run company business, the need for failover in this scenario is not as pressing as in transactional systems.
With that said, I had a customer that was testing the failover of one of their main SQL clusters and suddenly noticed that a more than several jobs failed to run. In helping on the issues, I noticed that they had SSIS steps in the job pointing to the cluster name for pulling out the packages form MSDB, and that, more importantly, someone had forgotten to install the SSIS service on the failover node.
Someone had configured SSIS to point to the cluster on the primary node, but must have forgotten to install the service and do the same config change on the failover node – easy enough fix; but also a good education opportunity for the client as they were not familiar with SSIS at all (transitioning to a new environment for them this past year, so most of this is new for them).
SSIS is not recommend to be configured as a cluster resource, but that does not mean it cannot be used on a SQL Server that is clustered – you just have to be aware of what you are doing and the consequences. In my current scenario, we have SSIS configured to work on the cluster environment, but not as a cluster resource (the second link below will show you how).
Here are the references:
- – http://msdn.microsoft.com/en-us/library/ms345193.aspx
- – http://msdn.microsoft.com/en-us/library/ms137789.aspx
Just be sure to weigh the pros and cons as even the simple config change has its own consequences, in particular the section in the first link on disadvantages of SSIS as a cluster resource.