How SSIS works with cluster SQL and non cluster SSIS service?

5 pts.
Tags:
SQL Server 2005
SSIS
According to microsoft, we can cluster SSIS service but it is NOT RECOMMENDED. http://msdn2.microsoft.com/en-us/library/ms345193.aspx Now this is the situation that I have where I need to understand how SSIS works? Enviornment: Active Active cluster enviornment for SQL server with SSIS server installed as stand alone as default on both node. Name: Node 1 Node 2 --------- -------------- --------------------- Server name: Nd1 Nd2 SQL server name: cs-nd1in01 cs-nd2in02 SSIS server name: Nd1 Nd2 BTW, this is cosolidated enviornment so there are more than one application expected and resides on each instance of SQL server. The question is around SSIS, what would be the best practice to develop SSIS package that can work with above envoinrment. Secnario: What if my Nd1 fails. SQL server cs-nd1IN01 will be failover to Nd2 and it will be available. But How about SSIS packages? How that understands to use Nd2 SSIS as Nd1 SSIS is not available. Is anyone has similar experience to setup SSIS in cluster envionrment but as non-cluster service? Please help. Thanks in adavance.
ASKED: March 14, 2008  12:10 PM
UPDATED: March 14, 2008  5:14 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Before I start, let me say that I’ve clustered SSIS and it works just fine.

Assuming that you decide to continue to leave SSIS un-clustered what you’ll want to do is.

Edit the xml config file for the SSIS service and add in nodes for each SQL instance so that SSIS can access each instances msdb database directly. You’ll want to do this on both nodes, then restart SSIS on both nodes. (I recommend editing one file, then coping the file to the other node.)

The section in the file you are looking for is this part.
<pre> <Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder></pre>

You’ll want to change it to something like this.
<pre> <Folder xsi:type=”SqlServerFolder”>
<Name>MSDB-CLUSTER-Default</Name>
<ServerName>SQLVIRTUALSERVERNAME</ServerName>
</Folder>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB-CLUSTER-Instance1</Name>
<ServerName>SQL:VIRTUALSERVERNAME\INSTANCE1</ServerName>
</Folder></pre>

By doing this no matter which node the SQL Server is running on when it connects to the local SSIS instance, the local SSIS instance will be able to find the correct MSDB database for that instance.

Now when you setup your SSIS packages to be run as jobs, be sure the setup the job step to connect to the SSIS server on localhost.. That way you are hitting the local SSIS instance.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following