SSIS in cluster setup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SSIS in cluster setup

40 posts, Page 1 of 4

1 2 3 4 »»»

SSIS in cluster setup

Rate Topic

Display Mode

Topic Options

Author

Message

balasach82

Posted Wednesday, October 31, 2012 12:42 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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

Want a cool Sig

Posted Wednesday, October 31, 2012 8:24 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221, Visits: 452

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 Smile

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 Cool

I want to win the lotto BigGrin

I want a gf like Tiffa w00t

Post #1379348

balasach82

Posted Friday, November 02, 2012 3:08 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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

anthony.green

Posted Friday, November 02, 2012 3:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

balasach82

Posted Friday, November 02, 2012 4:26 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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

anthony.green

Posted Friday, November 02, 2012 4:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1380257

balasach82

Posted Friday, November 02, 2012 4:34 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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

anthony.green

Posted Friday, November 02, 2012 4:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1380268

balasach82

Posted Friday, November 02, 2012 7:24 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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

anthony.green

Posted Friday, November 02, 2012 7:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1380355

« Prev Topic | Next Topic »

40 posts, Page 1 of 4

1 2 3 4 »»»

Permissions