question

user-470 avatar image
user-470 asked

Configuring Sql Server 2005 Integration Services for High Availability

We have an active-passive Sql Server 2005 database cluster. Since SSIS doesn't support clustering, we have installed it individually on both nodes. However, whenever the system fails over to the normally passive node, all of our SSIS packages fail. If we call the primary, typically active node "A" and the backup, typically passive node "B": Is it possible to setup SSIS to work under the following conditions, and if not, how close can we get? 1. If the "A" node is completely down (no power), the "B" node will automatically pick up all scheduled packages and automatically run them. 2. If the "A" node is running, but not active, have the "B" node run the packages automatically, but have the "A" SSIS installation sit idle. 3. If "A" is running packages and fails due to powering down or failing over, have "B" automatically restart the failed packages. (and vice-versa) 4. Preferably, only require setting up the SSIS packages on a single location, and have both nodes automatically pick up the configuration and package definitions when they become active. There is a tantalizing clue here, but I'm not sure I completely grasp how it works - and it seems that it wouldn't meet condition 1. [ http://msdn.microsoft.com/en-us/library/ms345193.aspx][1] "However, if high availability is your goal in establishing a clustered hardware environment, you can achieve this goal without configuring the Integration Services service as a cluster resource. To manage your packages on any node in the cluster from any other node in the cluster, modify the configuration file for the Integration Services service on each node in the cluster. You modify each of these configuration files to point to all available instances of SQL Server on which packages are stored. This solution provides the high availability that most customers need, without the potential problems encountered when the Integration Services service is configured as a cluster resource. For more information about how to change the configuration file, see Configuring the Integration Services Service." [1]: http://msdn.microsoft.com/en-us/library/ms345193.aspx
sql-server-2005ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
I'm not too sure on this, but I'll try and explain as I see it. You can store the SSIS packages in the SQL server itself (msdb I believe). This would be a part of your clustered instance and msdb would failover to the secondary when/if the primary should die. If SSIS is setup to run the packages as SQL Agent jobs, they would also failover and run as the agent will be moved to the newly active node. As I understand it, only the SSIS service is non-clusterable, the rest can be stored on the cluster resources and deal with a failover.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jason Cumberland avatar image Jason Cumberland commented ·
This is right on the money. In the SSIS configuration file on each node you would specify the clustered instance that has the packages stored in MSDB. If the packages are stored on the file system then the drive that they live on would need to be part of the cluster group that the instance lives on.
0 Likes 0 ·
user-470 avatar image user-470 commented ·
I do indeed have the packages being invoked via Sql Agent. So in this scenario, would I always leave the Integration Services service running on both the primary and secondary node?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.