Post reply

SSIS in Clustered Environment

  • October 2, 2007 at 9:56 am

    #153870

    Hi,

    I have SQL 2005 in a clustered environment (two nodes). SSIS is NOT configured as a cluster resource since Microsoft recommends that it not be. Instead, SSIS has been installed on each node in the cluster and I've modified the SSIS configuration file on each node ot reference the proper SQL Server Instance.

    When Node A is "online" I can use SQL Management Studio to view/manage the SSIS packages in the "msdb" folder. When there is a failover to Node B, the SSIS service is running and I can run jobs that run SSIS packages. However I cannot see the contents of the "msdb" folder in SSIS nor can I import packages to it.

    I'm already aware of the following documents from msdn which I've read and which served as the basise for configuring the SSIS configuration file:

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

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

    Is there any particular configuration or other change I need to make? I'll appreciate any information any one can offer.

    Thanks!

  • October 2, 2007 at 10:43 am

    #738272

    I'm assuming you've already modified the following line in the MsDtsSrvr.ini.xml file on both nodes?

    Tommy

    Follow @sqlscribe

  • October 2, 2007 at 3:24 pm

    #738396

    Hi,

    Thank you for responding. What line are you referring to?

    I did modify the files on both nodes to refer to the msdb folder and I also referenced the server\instance.

  • October 3, 2007 at 8:04 am

    #738624

    My apologies - seems as though the rest of my post was stripped out - possibly due to the XML tags.....

    In the MsDtsSrvr.ini.xml file there is a tag called ServerName. The default represents a "." Have you replaced this with SERVERNAME\INSTANCENAME on both nodes?

    Tommy

    Follow @sqlscribe

  • October 4, 2007 at 3:56 am

    #738980

    what a palaver! One wonders why SSIS is not cluster aware when DTS was. More work for Microsoft to do surely.

    They give with one hand by making Analysis services cluster aware (great) but then do this to us!

    This is the sort of thing that makes production DBAs look bad because developers and clients cannot see what the problem is!

    ---------------------------------------------------------------------

  • October 4, 2007 at 11:31 am

    #739236

    Hi,

    Yes, I edited the MsDtsSrvr.ini.xml file to include ServerName\Instance. It's really maddening. The documentation that my research has turned up makes it sound so simple ... "just edit the configuration file".

    I've had other people here look at my file and they agree that I've edited it correctly. Very strange...and very frustrating.

  • October 4, 2007 at 11:32 am

    #739237

    ... forgot to mention, - I edited the configuration file on both nodes.

  • October 5, 2007 at 2:55 pm

    #739738

    Hi,

    If you want to cluster SSIS then you can do the following:

    1. Add Integration Services (must be installed on all nodes) as a Generic Service in the cluster group and make the Sql Server resource a dependancy (I know that MS does not recommend this but it works just fine for us.)

    2. Edit this reg key on all nodes (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile) to point to a folder on one of the shared cluster disks.

    3. Place the MsDtsSrvr.ini.xml file there. in the config file you can also specify the StorePath element of the SSIS Package Store to be on a shared disk as well.

    This works really well if you are running an Active\Passive cluster that only has one Sql Server instance that will be running packages via Sql Agent.

    Andy

  • October 5, 2007 at 5:14 pm

    #739755

    Hi,

    Thanks very much for the information. What if we're running an active/active cluster? Do you happen to know what the impact of that would be?

  • October 9, 2007 at 9:36 am

    #740421

    I have not tested this but I think that in an Active/Active cluster scenario the instance in the cluster group that has the Integration Service would be fine. The instance in the other cluster group may be able to run SSIS packages via SQL Agent if that instance is running the same node as the cluster group running Integration Services.

    But since you will most likely be running one instance on one node and the other instance on the other node you will have one node only that can run SSIS packages on it.

    Andy

  • October 9, 2007 at 10:41 am

    #740446

    as you have noticed, SSIS itself is not instance aware.

    So you can only have one active SSIS on a cluster :crying:

    On the other hand, IMO MS is trying to push SSIS to a dedicated SSIS instance to handle all ETL, etc. in stead of one per instance.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 11 posts - 1 through 10 (of 10 total)

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