What is the recommended way to install ssis in a sql server fci configuration?
I am planning a sql server installation with fci.
There will be 2 sql server 2019 instances in active/passive mode; they share the same SAN.
This question is about ssis.
Clustering Integration Services is not recommended because the Integration Services service is not a clustered or cluster-aware service, and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services should be installed and started as a stand-alone service on each node in the cluster.
Integration services is a shared feature that I usually (in a non-fci based installation) select from the features page when installing the sql server database engine.
This is the 1st time I am going to install database engine and integration services in a fci environment, so I want to ask: What does installed and started as a stand-alone service on each node in the above quote mean? Does it mean that I should first configure the fci and 2 sql server instances (I1 and I2, ofcourse on 2 nodes) without integration services, and then create 2 new instances (I3 on node1 and I4 on node2) of sql server with database engine and integration services?
If so then do I have to deploy all my ssis packages on both the ssis based instances?
This means the FCI is only targetting the I1 and I2 database engine for failover?
My preference is to install SSIS on a separate server from my main database instance. Here's why.
SSIS is, for the most part, a standalone application. As such, it also has its own resource needs, and consumes CPU and memory, as needed. There are going to be times when SSIS spikes CPU and memory, and limits SQL Server from getting the CPU and memory resources it would like. Similarly, SQL Server can spike CPU and memory, and limit SSIS from getting the CPU and memory it would like.
It's much more difficult to balance the CPU and memory needs between two applications on one server, than it is to isolate them to their own VM, and scale them appropriately. As such, to do so you would need to account for sizing your servers to support both and also set your max server memory settings in SQL Server, to ensure enough memory is available for SSIS. It's simply a head-ache I prefer to avoid, if at all possible.
Instead, size your SQL Server to support your SQL Server workload. For example, if under peak load, your SQL Server requires 8 CPUs and 64 GB of RAM, build a SQL VM with that as your baseline. Now, if SSIS needs 2 CPUs and 16 GB of RAM, those needs are a net addition to the 8 CPUs and 64 GB of RAM you've already decided you need for SQL Server.
Should you choose to run these two on the same server, you're looking at 10 CPUs and 78 GB for RAM. Typically, either way you slice, you need the same number or resources to run the two services. The question comes down to whether you want to try to manage them on the same server.
With Virtualization these days, it's almost a none issue. Simply slice up the CPU and memory resources you want for each service, and give each its own VM to run on.
This will make performance issues much easier to troubleshoot down the road. All too many times I have seen scenarios where a DBA struggles to figure out if it is SSIS or SQL that is the problem when performance issues arise on a single server.
Take for example CPU spike to 100% while an SSIS package runs. Is this related to bad queries pulling data out of your OLTP database, or instead inefficiencies within SSIS package and overall ETL process?
With the two services running on separate servers, you can easily see which server has its resources spike the most during your SSIS process, and tackle the component head on. You also prevent one service from impacting the other, since they no long share a server.
With that said, you can also refere to the https://docs.microsoft.com/en-us/sql/integration-services/scale-out/scale-out-support-for-high-availability?view=sql-server-ver15 on MS Docs to help make SSIS highly available when not installed along side SQL Server on the same cluster.