How SSIS works with cluster SQL and non cluster SSIS service?
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 14, 2008 12:10 PM
UPDATED:
March 14, 2008 5:14 PM
RELATED QUESTIONS
- SSIS 2008 R2 performance and Windows Server 2008 R2 with a clustered SQL instance is slower than when connecting to a non-clustered instance?
- Running SSIS on a clustered server with SQL Server 2005
- SQL Server Failover Clustering or DB Mirroring
- SQL Server Fails to Start When Moved to Different Node
- SQL Server active/active node clustering
Answer Wiki:
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:VIRTUALSERVERNAMEINSTANCE1</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.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.