Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SSIS in cluster setup Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 12:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 389, Visits: 959
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?
Post #1379163
Posted Wednesday, October 31, 2012 8:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1379348
Posted Friday, November 2, 2012 3:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 389, Visits: 959
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

Post #1380212
Posted Friday, November 2, 2012 3:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 5,142, Visits: 4,930
Maybe this will help instead http://support.microsoft.com/kb/942176





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1380218
Posted Friday, November 2, 2012 4:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 389, Visits: 959
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
Post #1380251
Posted Friday, November 2, 2012 4:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 5,142, Visits: 4,930
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1380257
Posted Friday, November 2, 2012 4:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 389, Visits: 959
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.

Post #1380261
Posted Friday, November 2, 2012 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 5,142, Visits: 4,930
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1380268
Posted Friday, November 2, 2012 7:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 389, Visits: 959
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
Post #1380353
Posted Friday, November 2, 2012 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 5,142, Visits: 4,930
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1380355
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse