Today I wanted to share with you my “painful” experience with having SQL Server Integration Services (SSIS) installed on a cluster. I just got done installing and configuring a 4 node (3 active 1 passive) cluster. It has Windows 2003 and SQL Server 2005 Enterprise edition installed on it. I got the database engine, reporting services, analysis services and integration services installed and configured properly.
After installing and configuring everything we did a fail-over test. Everything had failed over except for SSIS. Come to find out that SSIS IS NOT cluster aware. You can view the article here that I came across on Microsoft’s website. There are work arounds or “hacks” as I like to call them. Because I am supporting a large production environment and need to minimize downtime as much as possible, we created a separate stand-alone SSIS server. From the SQL Server agent jobs on the production SQL Servers we do RPCs to the SSIS server that kicks off the packages. This has worked great! If anyone has succeeded in the “work arounds” or the “hacks” let us know!