SSIS and clustering

Question

Post reply

SSIS and clustering

stephen.lappe

SSChasing Mays

Points: 658
More actions
April 6, 2010 at 9:34 am

#220988

I have read many articles about work arounds for making SSIS work in a clustered environments.
So, would it not be easier just to run SSIS on a single node?
Looking for comments and input as to why or why not.
Personally, I would prefer to keep services like SSIS and SSRS off of the boxes that are storing and providing user's data if I had the resouces to do so.
Just looking for some analysis and feedback from the esteemed community.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 1

We run SSIS on an active/passive cluster and have no issues related to it being on a cluster.

What are these work arounds you mention? What issues do you think will cause problems.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 2

We're running an ACTIVE/ACTIVE cluster with SSIS as a clustered resource.

We set it up like this:

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

We store all of the packages in the MSDB database. This seems to work best when the cluster group fails over to the other node as it retains the connection passwords. When storing as a "package store", the login passwords for the connection strings seem to get erased when there is a failover and the jobs fail.

P Jones SSChampion Points: 12361 More actions · Answer 3

Thanks Steve-3_5_7_9. I'll check that out. I'd given up with SSIS on the cluster as it was set up by someone who doesn't usually install our SQL Servers and SSIS just wouldn't work.

I currently update the databases on it using packages stored on other non cluster servers.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 4

One more thing. When you modify the registry entries, you may actually need to search for the key depending on the server OS. 64bit OS has a different path than what is listed in the KB article.

You will need to change 2 registry entries.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTS\ServiceConfigFile

Make sure the registry entry change replicates to the other node(s) in the cluster.

If I think of anything else, I'll let you know.

Steve