SSIS in cluster setup

  • Since SSIS is not cluster-aware, what is the best approach to configure SSIS in SQL 2008 Cluster.

    When the active goes down, how to make the SSIS to identify that and run in secondary node (once it becomes active). Changing config xml (shutdown paramerter or something like that) is the only option?

  • Modify the config file on both nodes so that they point to the same instances/packages/file store...

    I did this for my previous employer in a 2 node cluster and it worked fine...

    http://dougbert.com/blog/post/ssis-and-clustering-what-you-should-do-instead.aspx

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • Am going here and there reading everything that i am confused now

    Are the below talking about the same method?

    * Configuring Integration Services as a clustered resource

    * installing SSIS on each node of cluster

  • Maybe this will help instead http://support.microsoft.com/kb/942176

  • Yes anthony, I indeed had a look at that. My conclusion is that there are 2 ways

    * configure SSIS as a cluster resource

    or

    * change SSIS config (.ini/xml) file and make SSIS run even after failover

    And that "configure SSIS as a cluster resource" is not recommended by Microsoft and they are vouching for Config file change option. So i too would want to chose "config file change" option.

    went through http://dougbert.com/blog/post/ssis-and-clustering-what-you-should-do-instead.aspx

    The way i see it, in a 2 node cluster, installing SSIS on both nodes and making the ini/xml changes in both nodes (to add another set of <Folder xsi:type="SqlServerFolder"> in Top Folder in the MsDTSSrvr.ini.xml file in both nodes) would be enough to make SSIS to run even after faiover

  • How are you running your SSIS packages?

    If they are being run by SQL Agent job you dont actually need the SSIS service running.

    If you are running them manually via SSMS then yes you need the SSIS service running.

  • I would be running SSIS packages through SQL jobs. In such case, what should i have to do? Dont i need to make the .ini/xml changes.

  • If you are running them as jobs then you dont need to do anything to the configuration files. Just ensure that the SSIS service is started on both machines, no need to set them up as a clustered resource. The SSIS service needs to be on both so that you can save packages to MSDB, but once the job is setup it runs the package via DTSExec.exe which is independent on the SSIS service.

    The only time you would want to change the config file is if you wanted to connect to the SSIS service via SSMS to be able to see the stored packages.

  • A straight forward,precise to the point answer. Thanks Anthony.

    Now, i would install SSIS on all nodes and let the Agent run the SSIS packages through job.

    To manage the packages (if at all its needed), then .ini/xml in all the nodes would be modifed, so that a managing can be done from any machine.

    Normally, we would use management studio to connect to other SQL server's Integration services for monitoring, import/export etc. Can we do the same and connect to Cluster from another SQL

  • If you want to monitor the execution of packages or manually run the packages outside of SQL Agent then yeah you will need to change the config file on all of the cluster nodes. You can then use SSMS from any machine to connect to the virtual name of the Cluster and hey presto away you go as DNS will take care of the connecting for you just as if you where connecting to a stand alone machine.

  • Anthony, really appreciate your answers. you made it to easy to understand.

    Why the grumpy look? 🙂

    Mr/Ms "want a cool Sig"

    Thanks to you too. Your post gave me a starting point and made me to probe. Thanks for the link.

    Thanks mates

  • A strange doubt popped up now. Installing SSIS on each node is needed for importing packages to msdb (in addition to managing packages in msdb while failover).?

    If i dont have an need to manage packages from management studio, then i dont have to install SSIS on each node. Is this correct?

  • No, you can use the command line based tool instead to upload the package if you dont want to do it via SSMS, that way you wont need the SSIS service.

    Check what the service does and then decide do I really need to do any of them things, if yes you need the service, if no then you dont.

  • Anthony,

    SSIS packages wouldbe run from agent jobs. Now i dont need to run or monitor ssis packges from management studio. I wont be installating SSIS on each nodes.

    in case of failover, packages running would fail/cancel. After failing over to 2nd node, future scheduled packages would run. is this correct? If this is correct, then for running ssis packages only, we need to run as agent jobs, no need to install ssis or change xml or make ssis cluster resource. Am i right?

  • Yes on fail over if they are running as jobs then yes they will run as intended.

    If you fail over and want to upload a new SSIS package you will need to do it via the command line tool, not via SSMS.

    You just need to decide on if you will use the functionality of the SSIS service and if you do then install it or not.

Viewing 15 posts - 1 through 15 (of 39 total)

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