2

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.

Link: https://learn.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster?view=sql-server-ver15

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?

Improve this question

2 Answers 2

Reset to default
1

To install SSIS on a FCI, just use the SSIS Catalog. The SQL Server instance, SQL Server Agent, and the SSIS Catalog will all fail over together and you don't even need the old SSIS Service.

Improve this answer
8
  • Please can you expand on just use ssis catalog. Do I need to install integration services on the same or separate instance?
    – variable
    Commented Mar 9, 2022 at 14:39
  • The SSIS catalog is used to store, execute, and manage your packages, and it's part of your SQL Server instance. So if you fail over to another node, everything you need for SSIS comes along. You install SSIS as part of the instance installation, and then create the catalog.
    – David Browne - Microsoft
    Commented Mar 9, 2022 at 14:40
  • To get the ssis catalog I first need to install integration services feature. Should I do this on both instances?
    – variable
    Commented Mar 9, 2022 at 14:42
  • Yes. But there's only one instance. It's installed on multiple cluster nodes.
    – David Browne - Microsoft
    Commented Mar 9, 2022 at 14:42
  • 1
    It just needs to be installed on each node. I just don't remember whether the installer supports doing that at the same time you're installing the Failover Cluster Instance or you need to install it seperately on each node. Remember in an FCI you must install SQL Server on each node; only the databases fail over, not the program bits.
    – David Browne - Microsoft
    Commented Mar 9, 2022 at 14:56
1

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 Scale Out support for high availability on MS Docs to help make SSIS highly available when not installed along side SQL Server on the same cluster.

Improve this answer
6
  • yes but this way requires more SQL Server licenses to cover the separate server.
    – MBuschi
    Commented Mar 9, 2022 at 16:33
  • No it wouldn't. SQL Server is licensed by cores, not servers. If you run one larger VM with 12 cores vs. 2 separate VMs with 8 and 4 respectively, it's the same licensing cost.
    – Brendan McCaffrey
    Commented Mar 9, 2022 at 16:36
  • ah but in this way you reduce the CPU for SQL engine.... at the end you remove some license from sql engine for ssis
    – MBuschi
    Commented Mar 9, 2022 at 16:59
  • 1
    No, you're still not affecting CPU count for the SQL Engine. Let's say SQL Server truly needs 12 CPUs. If that's the case, 12 CPUs is not enough for one VM to run SQL and something else, such as SSIS. You'd need to provision the VM with enough CPU and memory to support all applications. A sizing expercise needs to be completed to determine how many CPUs and memory each component would require. At that point, if you combine the required resources on one VM or 2, it's still the same number of combined resources. Minus a small amount of overhead for an additional instance of Windows.
    – Brendan McCaffrey
    Commented Mar 9, 2022 at 17:10
  • This way of working is good to manage average cpu needs but for spikes like high ETL load you will suffer low cpu and lower execution time. Anyway it's a good strategy for most scenarios
    – MBuschi
    Commented Mar 10, 2022 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.