6

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://learn.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.

Improve this question
4
  • We use SSIS on the same server with no issues. Just leave enough memory for OS - sql server max memory includes SSIS?. SO based on my experience, it should be fine (and you have plenty of memory as per your question).
    – Kin Shah
    Commented Oct 16, 2017 at 20:36
  • @Kin, are you on a clustered instance?
    – Wes
    Commented Oct 16, 2017 at 21:46
  • Yes .. we do have clustered environment.
    – Kin Shah
    Commented Oct 17, 2017 at 2:27
  • @Kin Thanks. so, is SSIS in the SAME cluster on on a separate cluster from your SQL Server Database Engine cluster?
    – Wes
    Commented Oct 17, 2017 at 14:30

4 Answers 4

Reset to default
3

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://learn.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service

Improve this answer
1

We were able to get SSIS working in the cluster. We put the SSIS resource in the same cluster as SQL Server, added DCOM permissions, and were good to go. Only remaining item is, when in SSMS, we cannot expand MSDB. It appears to be the double-hop authentication issue stating 'Login failed for user 'NT Authroity\Anonymous Logon'

Improve this answer
1

While not officially supported, Matt Masson published a great method for utilizing SSIS in a SQL 2012 or 2014 alwayson environment.

https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/

To sum up his points: The SSISDB database can failover just fine with no modifications, but once on a new node will fail to run packages due to the encryption being set by the wrong service master key.

Using a job to autodetect the failovers and then re-encrypting the database works very well to keep the SSISDB with the working primary.

There are some caveats to this approach. Namely, when patching you MUST remove SSISDB from the AG and fully remove it from the secondary nodes (do not just leave it in a restoring state or the system database upgrade scripts will fail and leave your instance in a tricky place to recover). Once patching is complete, you can then re-add it to the AG.

I have been using this method for the last 3 years in an multi-subnet alwayson availability group and had no issues.

Improve this answer
-1

SQL Server 2016 allows you to put the SSISDB into an AlwaysOn Availability Group.

Improve this answer
4
  • 1
    Hi Joshua, this doesn't really answer the question about best practices and the question is concerning SQL 2014. Try to add more relevant information and avoid one-line answers that may not fully address the issue at hand.
    – LowlyDBA - John M
    Commented Oct 16, 2017 at 20:22
  • The only way to get it to reliably work is to upgrade to 2016. It's no big deal to go from 2014 to 2016.
    – Joshua Guttman
    Commented Oct 16, 2017 at 22:08
  • 1
    Upgrading is far easier said than done in most business environments.
    – LowlyDBA - John M
    Commented Oct 17, 2017 at 13:25
  • @LowlyDBA especially when you have to be on (large) vendor target platforms.
    – Wes
    Commented Oct 18, 2017 at 15:30

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.