Geoff N. Hiten Blog

SQL Server thoughts, observations, and comments

To Cluster or Not to Cluster, That SSIS the Question

Clustering and SSIS have a stormy relationship at best. What you think you get after installing SQL and SSIS on a cluster is not always what you expected. For SQL 2000, the installer was cluster-aware on every component. Tools, code, everything got loaded on all nodes. Now, we aren't so lucky. Only SQL and SSAS install cluster-wide (or at least to all specified nodes). Client tools and most importantly SSIS only install to the local node. In practice, this often means a successful cluster install can leave a DBA with only one node capable of running SSIS packages. What is worse is that the first time this gets noticed is when a production system fails over and SSIS is nowhere to be found. So the first good reason to cluster SSIS is to make sure it actually works on all nodes correctly.

Another good reason to cluster SSIS is if any of your SSIS packages use a clustered resource such as a file share on one of the SQL data drives. You either have to create a clustered file share OR cluster SSIS. I prefer clustering SSIS since it keeps things simpler from a developer and a DBA perspective. Plus, the SQL installer tends to dislike anything "extra" on the core SQL cluster resources.

Microsoft warns that clustering SSIS is not recommended and not supported, but then tells exactly how to do it. Personally, I think they just didn't have time to build this logic into the installer. I have had no problems running clustered SSIS. I have had several issues running SSIS on a cluster without clustering SSIS itself. Either way, here is the link to the TechNet article:

http://technet.microsoft.com/en-us/library/ms345193.aspx

There is one warning about clustering SSIS that I do need to emphasize. Since the SSIS install routines don't support clustering directly, neither do the Service Pack or Hotfix installers. You have to run them independently on each node, preferably with the SSIS service active on that node. This does increase the downtime for applying updates. The alternative is to "uncluster" SSIS, apply the updates, then "recluster" SSIS. Dunno about you, but I just had an ugly flashback to SQL 7.0.