We have a 2-node clustered SQL Server 2014, Enterprise, environment for our data warehouse. Storage, network, and servers are all high performing (1tb mem, 32 cores CPU per node, all SSD)
We are trying to come up with where to put SSIS itself. Based on Microsoft documentation, it isn't recommended that it be clustered and, therefore, shouldn't be on the 2 nodes (though I did install it with the instances, we don't have to use if from there can could disable the services.) On the flip side, we don't really want to spend money on licensing of 8 vCPU's to put it on an available server in the same ecosystem (used for a proprietary ETL from vendor.)
What are the best practices and recommendations on how/where to install SSIS for a clustered SQL Server database environment?
Thank you,
Wes
EDIT: Microsoft discusses this here: https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster but does not go over the recommended approach as an alternative.
Best Answer
The SSIS Catalog and SQL Server Agent are both part of the Failover Cluster Instance, and failed-over when a Failover Cluster Instance fails-over. So my recommendation is to store all your packages in the SSIS catalog and schedule them all with SQL Agent. And simply ignore the SSIS Service completely.
See "The topics in this section discuss the Integration Services service, a Windows service for managing Integration Services packages. This service is not required to create, save, and run Integration Services packages. SQL Server 2012 supports the Integration Services service for backward compatibility with earlier releases of Integration Services. " https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service