Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    32

    Unanswered: Clustering SSIS , this SSIS the question !!!

    I am pretty much confused as to what to do; According to MS Configuring Integration Services in a Clustered Environment, the disadvantages in making SSIS cluster aware seem to be greater than the advantages; all I want to do is to be able to run my DTS packages every time the cluster fails over to the inactive node; what is the best and safest approach?

    This is my current environment:

    We are using SQL Server 2005, 64-bit, on a Windows 2003 clustered server with Active/Passive 'A' and 'B' nodes. Databases are on a shared equallogic iscsi SAN.

    Any help would be greatly appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SSIS is emphatically NOT cluster aware. This was a design decision at a very low level, and I think it was a very good choice. In SQL 2005, you can bludgeon SSIS into running on the cluster, but it is a bad idea from the git-go and nothing I've seen will improve it one iota.

    When a DTS or SSIS package dies due to a cluster failover, the package needs to be examined and restarted anyway. This is not a process that I'd try to automate unless I understood every possible way for the cluster to fail (which ain't none too likely).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I have to echo Pat's comments. According to an MS tech I met, the method used to cluster SSIS in SQL 2005 is not even supported in SQL 2008 (and I thought I was being so smart ).

    Consider offloading the SSIS workload to a different server (that is not in a cluster). It's not perfect, and there are licensing implications, network performance implications and a host of other issues to consider, but it is an alternative.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Jul 2007
    Location
    Belgium
    Posts
    15
    I'll toss in my 2 cents and ditto hmscott and PatP! If I have a failover situation, or need to exercise the drp, then I'll handle my SSIS packages manually.

  5. #5
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    If any packages running during the failover will not be completed, you have to manually restart them or add additional schedule to run the job after sql agent sql started and make sure your ssis package is restartable.

    For regular failover your packages can run when sql server fails over to other node. Check the BOL topic how to edit the xml file... Configuring the Integration Services Service

    Check the following thread for more details...
    Configure Integration Services on a Cluster
    MohammedU
    SQL Server MVP

  6. #6
    Join Date
    Jul 2007
    Location
    Belgium
    Posts
    15
    You might also want to look into checkpoints and transactions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •