How SSIS works with cluster SQL and non cluster SSIS service? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

According to microsoft, we can cluster SSIS service but it is NOT RECOMMENDED.
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.

Being that SSIS is not cluster aware, but the config file is pointing to the Virtual SQL Server, by installing SSIS on both nodes and making similar configuration files, they should work as a passive-active combination. MSDB will be the same for both nodes, so it should see the same stores and recognize itself as the Virtual SQL Server.
Also one of the technet article defines that SSIS is not clusterable, so when the install took place, SSIS was configured on the first node like a non cluster install. This involves some configuration files that SSIS uses on startup. The one in question resides at :
%SQL Install Directory%90DTSBinnMsDtsSrvr.ini.xml
We can see that in this file the MSDB is pointing at an instance, and the instance is “.”. This tells SSIS to look at the default instance on the machine that it is currently running on. In a clustered environment, we need for SSIS to look at the cluster name to find the MSDB database and to find our packages. To fix this, just follow these steps.
1. Stop SSIS
2. Edit the file above, replacing “.” with your cluster server name
3. Start SSIS

Once SSIS comes back online, you will be able to expand MSDB and see all the packages that are stored on the cluster.

So satya, can SSIS supports multiple MSDB databases? I see what you are saying but in secnario I am talking when my Nd1 crashed, I am forced to use Nd2 SSIS. Nd2 SSIS already connetction to SQL server cs-nd2IN02 and I need to add cs-nd1IN01 as failed over to Node 2 now.
Is that possible?
Thank you for your quick response.

Hi amu,
i’ve a similar requirement.. do you have an answer or workaround to your question yet?
Would appreciate if you could share?
Hi RabinG. Welcome to the forums!.
I suggest to start a new thread. This one is 5 years old.:)

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |