This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
How to configure SSIS in SQL Server AlwaysOn
Question
I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it? Below link provides some information but since I am not DBA it’s difficult to understand this article:
Can someone guide me on basic setup of SSIS in an Availability Group and explain how synch up happens between nodes?
Update
I am referring to the SSIS Catalog database (SSISDB)
Can I use listener in my connection managers?
asked 2016-03-30 by kudlatiger
Answer
In SQL Server 2014 & 2012, adding the SSISDB as an Availability Database in an Availability Group is not supported. My understanding is that with some workarounds, adding the SSISDB to an AG is possible, but not supported. The workarounds sound error-prone, and I’ve not attempted it personally.
Rather than adding the SSISDB to the AG, I’d suggest evaluating the business needs (High Availability and/or Disaster Recovery), and see what other options are the best choice.
Depending on your HA/DR needs, consider maintaining two parallel SSISDB catalogs, one on each AG server. From an operational perspective, you will need to ensure that SSIS packages are deployed to both servers, and that they are kept identical (packages, permissions, etc). This would give you the ability to use the locally-installed SSIS service on either machine to run packages for HA/DR. You would also need to make sure that you do not have SSIS packages running on both servers.
In SQL Server 2016, adding the SSISDB to an Availability Group is supported & documented. If upgrading is an option, you may consider that as well.
Within your SSIS package connections, you can use the AG listener to connect to the active node of the AG for data access. SSIS does not need to run on the same server that is hosting your data.
answered 2016-03-30 by Andy Mallon