SQL Server Integration Services has one part worth putting in an Availability Group: the SSISDB catalog. It holds every deployed project, package, environment, parameter value, and execution log, everything an operator needs to see what ran, what failed, and why. If that database is unreachable, your packages may still technically be able to run, but nothing gets logged, no environment references resolve, and nobody can see execution history. Since SQL Server 2016, SSISDB can join an AG like any other database, with a couple of extra steps because of how it protects sensitive data internally.
Two Deployment Models, Only One Has a Catalog
This only applies if you're using the Project Deployment Model. The older Package Deployment Model, packages stored as loose .dtsx files or in msdb, executed via dtexec or the standalone Integration Services Windows service, has no catalog database at all. There's nothing to add to an AG; HA for that model means clustering the file share or msdb yourself.
| Deployment model | Where packages live | AG-protectable? |
|---|---|---|
| Package Deployment Model | File system / SSIS Package Store / msdb |
No catalog database exists to protect |
| Project Deployment Model | SSISDB catalog |
Yes, since SQL Server 2016 |
Architecture at a Glance
The detail that surprises people coming from SSRS: catalog-based SSIS execution runs inside the SQL Server Database Engine process that hosts SSISDB. There's no separate web/rendering tier to scale out. Whatever calls catalog.start_execution, a SQL Agent job step, dtexec /ISServer, or a T-SQL script, executes wherever that call lands. Point every caller at the AG listener, and failover handles itself: the next execution simply lands on whichever node is now primary.
SSISDB's stored procedures rely on CLR integration, and its sensitive data (connection manager passwords, sensitive parameters) is protected by a database master key and certificate. Both are instance/database-level settings that must be configured identically on every replica before the database can join, they don't ride along automatically like schema and data do.
Requirements
- SQL Server 2016 or later on every replica, matching Integration Services feature installation.
- CLR integration enabled (
sp_configure 'clr enabled', 1) on every replica. This is an instance-level setting; the AG does not replicate it. - The
SSISDBcatalog already created on the primary (via SSMS's "Integration Services Catalogs" node), with a known catalog password. - A working AG listener, since every job step,
dtexeccall, and connection manager should target it instead of an instance name. - SQL Server Agent running on every replica that could become primary, so scheduled SSIS jobs actually have somewhere to run after failover.
Configuration Sequence
SSMS's "Add Database to Availability Group" wizard will detect that SSISDB is encryption-protected and refuse to proceed silently, it generates the exact backup/restore script you need, including the real certificate name it created for you. Don't hand-roll the certificate name yourself; let the wizard hand you the script and just run it.
- Create
SSISDBon the primary if it doesn't exist yet, and take a full backup (required before any database can join an AG). - Enable CLR integration on every replica that will host
SSISDB, this has to be done manually on each node, it's not part of what the AG synchronizes. - Start the "Add Database to Availability Group" wizard on the primary and select
SSISDB. When it flags the master key/certificate dependency, use the script it generates. - Run the generated backup script on the primary, it backs up the database master key and the certificate protecting it to files, each encrypted with a password you choose.
- Run the generated restore script on every secondary, it recreates the master key and restores the certificate from those files, using the same passwords.
- Let the wizard seed/join
SSISDBon every secondary, same as any other database. - Repoint every caller at the listener name: SQL Agent job step "Server" fields,
dtexec /ISServertargets, and any connection managers with a hard-coded catalog server name.
What Happens on Failover
A package execution already in progress when the AG fails over is not migrated, it fails or stalls, the same as it would with any application mid-transaction against a database that just lost its primary. What matters is the next execution: if the caller (Agent job, dtexec, or your own scheduler) is pointed at the listener, that next call simply connects to the new primary and runs there, no reconfiguration needed. Execution history for the failed run appears in the catalog once the new primary is reachable and logging resumes.
catalog.executions on the new primary.
Common Gotchas
- CLR not enabled on a secondary. The database fails over fine, the moment it becomes primary and a package tries to execute, catalog stored procedures that depend on CLR objects fail.
- Master key/certificate skipped on a secondary. Same failure pattern as the SSRS encryption key: the database is there, but sensitive parameter values and stored connection manager passwords can't be decrypted after failover.
- Maintenance job runs on the wrong node. The built-in "SSIS Server Maintenance Job" that cleans up old execution logs needs to run against the writable primary. If SQL Agent fires it locally on a secondary, it fails outright since that replica's copy isn't writable. Gate it on the replica's current role, or only enable the job on the node that's primary.
- Hard-coded instance names in job steps or connection managers. Anything still pointing at a specific server name instead of the listener won't follow the AG anywhere.
- Package Deployment Model packages left unprotected. If part of your estate still runs the older deployment model, this entire setup doesn't cover it, plan a separate HA story (or migrate those packages to the catalog).
Monitoring Once It's Running
The catalog's execution history, parameter values, and project/package inventory are exactly what a documentation and analysis tool needs to reason about your SSIS estate. SSIS Analyzer reads the SSISDB catalog directly to document packages, compare project versions, and trace data lineage, all of which keeps working against the AG listener the same way your Agent jobs do.
The Bottom Line
Adding SSISDB to an Availability Group is, at the T-SQL level, the same ALTER AVAILABILITY GROUP ... ADD DATABASE as any other database. What makes it different from a plain user database is everything that lives outside the database itself: CLR has to be turned on everywhere by hand, and the master key/certificate pair has to be deliberately copied to every secondary before the wizard will even let you add the database. Get those two right, point your Agent jobs and dtexec calls at the listener, and SSIS failover becomes exactly as boring as it should be.