Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
The short answer is there is no way to configure SSIS for Always-On with SQL Server 2014 and below.
SSIS, just like SSRS, SSAS, jobs, logins, alerts and other minor things that operate at the Instance level are not Always-On aware...Always On only keeps databases in sync. There are strategies for mitigating all of these items however. For SSIS and business-logic jobs (everything except backup/DBCC, etc...) we have a totally separate instance for running those jobs (generally referred to as a central job server). It does not have any high-availability built in but generally jobs and SSIS packages are periodic so they have more tolerance for down times and could even be run from a test server in a pinch with connection changes.
SQL Server Failover Clustering operates at the Instance level and is the High-Availability solution that supports SSIS/SSRS/SSAS/Jobs,etc... Always-On High Availability is closer to Database Mirroring and can use local storage but there is a cost, keeping all these other things in sync.
I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it?
You can add SSISDB to Availability Group, same as other user database through Add Database to Availability Group wizard.
Can someone guide me on basic setup of SSIS in an Availability Group and explain how sync up happens between nodes?
How sync happens:
In simple terms, the transactions are written to log and it is transferred to secondary servers as blocks, these transaction blocks are executed on secondary servers and committed to disk. Synchronisation can be immediate (synchronous) or delayed(asynchronous), based on the availability mode Check the official page for clear understanding.
Guide me on basic setup of SSIS in an Availability Group:
On SQL Server 2016, you can add SSISDB to Availability Group and enable the Always On Support. Check my article for step by step guide on adding the SSISDB to SQL Server Always On Availability Group.
Can I use listener in my connection managers?
Yes, you can use Availability Group listener (link to my blog) name on connection manager.
