How to install SSIS Packages on Availability Always On?

To run SSIS packages in a SQL Server Always On Availability Group, configure high availability for the SSIS Catalog database (SSISDB) and then deploy packages to that catalog. The high availability is at the database level; SSIS packages themselves are stored in SSISDB.

Steps are:

1. Prepare the Always On environment
   1. Set up a Windows Failover Cluster on all nodes.
   2. Install SQL Server (with the Integration Services feature) on each node.
   3. Enable Always On Availability Groups on each SQL Server instance.
   These are required before SSISDB can participate in an availability group.
2. Create the SSIS Catalog (SSISDB) on the primary node
   1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance that will be the primary replica.
   2. In Object Explorer, expand the server node, right‑click Integration Services Catalogs, and select Create Catalog.
   3. Select Enable CLR Integration.
   4. Select Enable automatic execution of Integration Services stored procedure at SQL Server startup so that catalog.startup runs at startup.
   5. Enter and save a password for the database master key (and back up the master key as recommended).
3. Add SSISDB to the Always On Availability Group
   1. On the primary replica, use the New Availability Group wizard (for example, Use the Availability Group Wizard in SSMS).
   2. On the Select Databases page, include SSISDB and provide the SSISDB master key password when prompted.
   3. Use the Full database and log backup method when adding SSISDB to the availability group. This avoids master key issues after failover.
4. Enable SSIS support for Always On
   1. Still connected to the primary replica in SSMS, right‑click Integration Services Catalogs and select Enable Always On Support.
   2. In the Enable Support for Always On dialog, connect to each secondary replica listed. The login used must be a member of the sysadmin server role on each replica.
   3. After successfully connecting to all replicas, click OK to enable SSIS support for Always On.
   Notes:
   • These steps must be done on the primary node.
   • Auto‑failover of SSISDB is not supported until SSIS support for Always On is enabled.
   • If the Enable Always On support menu item is disabled, refresh the node, verify that the connection is to the primary replica, and confirm the SQL Server version is 13.0 or higher (SQL Server 2016+).
5. Deploy SSIS packages to the SSIS Catalog (SSISDB) After SSISDB is in the availability group and Always On support is enabled, deploy packages as usual to SSISDB on the primary replica. The catalog contents are then protected by the availability group. Common deployment options include:
   • Integration Services Deployment Wizard (isdeploymentwizard.exe), using the Package Deployment model.
   • SSMS: under Integration Services Catalogs > SSISDB > Projects, right‑click Projects and select Deploy Projects.
   • Visual Studio / SQL Server Data Tools: right‑click packages in an SSIS project and select Deploy Package.
   • T‑SQL: call [catalog].[deploy_packages] to deploy packages programmatically.
6. Understand behavior during failover
   • SSISDB and all deployed projects, packages, parameters, and logs fail over with the availability group.
   • Packages that were running at the time of failover do not restart or resume automatically. They must be restarted after failover.
7. Upgrades and patching with SSISDB in an availability group
   • When upgrading SQL Server, the upgrade may be blocked by the "SSISDB in Always On Availability Group" rule because upgrade runs in single‑user mode.
   • To upgrade:
     1. Remove SSISDB from the availability group.
     2. Upgrade or patch each node.
     3. Add SSISDB back into the availability group.
     4. Re‑enable SSIS support for Always On if required.

Once these steps are complete, SSIS packages are effectively "installed" on a highly available SSIS Catalog (SSISDB) protected by Always On Availability Groups.

References:

• SSIS Catalog
• Integration Services (SSIS) Server and Catalog
• Deploy Integration Services (SSIS) Projects and Packages
• Deploy Packages with SSIS
• Legacy Package Deployment (SSIS)