In this article, we will discuss how to configure the SSISDB (Integration Services Catalog Database) in SQL Server Always On Availability Groups. The SSISDB is a repository for all your integration service projects, parameters, environments, and runtime history. By configuring the SSISDB in a highly available AG group, you ensure that your SSIS packages are always accessible and protected from failures.

Introduction

The SSISDB holds integration service projects, parameters, environments, and runtime history. To configure the SSISDB in an AG group, you need to follow a few steps:

1. Enable CLR integration
2. Create the SSISDB
3. Add the SSISDB to the AG group
4. Enable Always On Support for the SSISDB

Step 1: Enable CLR Integration

Before creating the SSISDB, you need to enable CLR integration in your SQL Server instance. This can be done by following these steps:

1. Connect to the database engine in SQL Server Management Studio (SSMS)
2. Right-click on the Integration Services Catalog and select “Create Catalog”
3. In the Catalog Creation Wizard, enable CLR integration
4. Specify a password for encryption that protects your sensitive data
5. Click “OK” to create the integration service catalog and the corresponding SSISDB

Step 2: Add the SSISDB to the AG Group

Once you have created the SSISDB, you can add it to the existing SQL Server Always On Availability Group. Follow these steps:

1. Connect to the primary replica instance in SSMS
2. Expand the availability groups and launch the “Add database” wizard
3. Select the SSISDB from the user database list
4. Enter the SSISDB password for encryption
5. Click “Refresh” and verify that the SSISDB meets prerequisites

Note: Even though the SSISDB does not have a full database backup, it still meets prerequisites for adding it to the AG group. This is because SQL Server restores the SSISDB from a full backup on the primary replica.

Step 3: Enable Always On Support for the SSISDB

After adding the SSISDB to the AG group, you need to enable Always On Support for the integration service catalog. Follow these steps:

1. Connect to the primary replica instance in SSMS with sysadmin permissions
2. Right-click on the Integration Services Catalog folder and select “Enable Always On Support”
3. Follow the wizard to connect all new secondary replicas

Enabling Always On Support for the SSISDB creates two SQL Agent jobs on both primary and secondary replicas: SSIS Failover Monitor Job and SSIS Server Maintenance Job. These jobs ensure the failover readiness and maintenance of the SSISDB.

Step 4: Perform AG Failover for the SSISDB Database

Once you have configured the SSISDB in the AG group, you can perform a failover to test its availability. Follow these steps:

1. Perform an AG failover from the primary replica to the secondary replica
2. Verify that the SSISDB is synchronized on the new primary replica

After the failover, you might encounter error 15581 on the new primary replica. This error can be resolved by decrypting the master key and adding the encryption key using the ALTER MASTER KEY statement.

Conclusion

In this article, we have discussed how to configure the SSISDB in SQL Server Always On Availability Groups. By following the steps outlined above, you can ensure that your SSIS packages are highly available and protected from failures. The SSISDB plays a crucial role in managing and executing your integration service projects, and by configuring it in an AG group, you can ensure its continuous availability.