Rajendra Gupta
AG dashboard

Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups

December 14, 2020 by

In this 39th article for SQL Server Always On Availability Group series, we will learn to configure SSISDB in the highly available AG groups.

Introduction

The SSIDB is the repository for all your packages and their configurations for integration service projects. You can configure multiple environments such as test, production, execute package, view progress and troubleshoot failures. The SSISDB holds integration service projects, parameters, environment and runtime history.

Once you have installed the integration services, you can connect to the database engine in the SQL Server Management Studio (SSMS). Right-click on the Integration Services Catalog -> Create Catalog.

Create an integration service catalog

In the Catalog Creation Wizard, do the following tasks.

  • Enable CLR integrations
  • It displays the catalog database name. We cannot modify the catalog DB, i.e. SSISDB name
  • Specify a password for encryption that protects your sensitive data

Enable CLR integrations

Click Ok. It creates an integration service catalog and its corresponding SSISDB in your database instance, as shown below.

SSISDB database

Right-click on the integration service catalog to view its properties. Here, you see the SSISDB schema version, build, encryption algorithm, operation log retention period, a maximum number of project versions.

integration service catalog

To understand these properties in detail, refer to the Introduction to the SSIS Catalog database (SSISDB) article on SQLShack.

We can start deploying our integration services projects using this SSIDB. In this article, we do not deploy any projects. You can use existing articles from Integration Services (SSIS) for this purpose. Now, we have the requirement to make this database highly available. Let’s explore how you can get this database in the SQL Server Always On Availability Group.

Environment details

In this article, I use the following AG environment:

  • Total Nodes: 2
  • Primary SQL instance SQLNode2\INST1
  • Secondary SQL instance: SQLNode1\INST1
  • Availability mode: Synchronous
  • AG database: MyNewDB
  • Failover Mode: Automatic
  • Failover Readiness: No Data Loss

Install SQL Server integration service on all primary and secondary replicas. You can verify the service status using the SQL Server Configuration Manager. As shown below, both the database engine and integration service is in a running state.

Failover Readiness

You can use the previous articles (TOC at the bottom) for preparing a similar environment. We already have an SQL Server Always On Availability Group, so we add the SSISDB into the existing AG. You can also create a new availability group for the SSISDB as per your requirements.

As shown below, the AG dashboard is in healthy condition.

AG dashboard

Add the SSISDB into the existing availability Group

To add the SSISDB into the existing SQL Server Always On Availability Group, connect to the primary replica instance in SSMS, expand the availability groups, and launch the Add database wizard.

Add the SSISDB into the existing availability Group

In the wizard, you get the SSISDB into the user database list. Its status shows as Password Required.

Password Required for SSISDB

Enter the SSISDB password that you specified for the encryption purpose.

encryption requirement

Click on Refresh and its status changes to Meets prerequisites.

Meets prerequisites

You might have a question here: Why the SSISDB meets prerequisites without having a full database backup that is an eligibility criterion for adding a database into the SQL Server Always On Availability Group?

For the availability group eligibility, it must have a full database backup. However, we did not take any database backups, but still, it shows you Meets prerequisites status.

In the SSMS, right-click on the SSISDB and open the database properties. It gives you the last database backup timestamp. Look at it carefully, and the last backup date is 24th September 2019.

Last database backup

To verify the backup timestamp, open your instance directory C:\Program Files\Microsoft SQL Server\150\DTS\Binn. Here, you see the SSISDBBackup.bak file. Verify the backup file timestamp (9/24/2019 2:21:13) and the last backup timestamp in SSMS (9/24/2019 2:21:13PM).

Once you configure the SSISDB catalog first time, SQL Server restores this database backup for a copy of the SSISDB in your SQL instance.

open your instance directory

SQL Server created the SSISDB from a full back up on the primary replica, that’s why AG does not complain about no full backups once you try to add into the Availability group.

Switch back to your Add database to the availability group wizard and connect to your secondary replicas.

Add database to the availability group wizard

On the next page, we configure the Availability database initial synchronization. Here, you can use the following options.

  • Automatic seeding: It automatically creates the SSISDB copy on your secondary replica and synchronizes with the primary
  • Full and log backup: Specify the network share for SQL Server to take the backup and restore it automatically
  • Join Only: You can restore the existing SSISDB backup or take a new full backup, restore it manually on the secondary instance in NORECOVERY mode

Let’s use the automatic seeding for the SSISDB.

database initial synchronization

In the next validation step, you get a warning for the SSISDB.

warning for the SSISDB

Click on the Warning to get the details. It asks you to enable Always On Support under the integration services once the database is part of the availability group.

enable Always On Support

Ignore the Warning as of now. In the summary page, verify the AG database configuration.

AG database configuration

It added the SSISDB successfully into the availability group.

availability group SSISDB database

Launch the AG dashboard. The SSISDB is in the synchronized status, as shown below.

AG dashboard synchronized status

Enable Always On Support for Integration Service Catalog

Previously, we observed a warning message for the SSISDB database while adding it to the availability group. The database is a member of the availability group; however, you need to enable the AG support for the integration service catalog. Connect with the login having sysadmin permissions in your primary replica.

Right-click on the Integration Services Catalog folder and click on Enable Always On Support.

Integration Service Catalog

It opens the following Enable Support for AlwaysOn wizard, and it asks you to connect all new secondary replicas.

Enable Support for AlwaysOn wizard,

It enables the integration service catalog for the availability group. Once you enable the Always On Support, the SSISDB is ready for the failover.

It creates two SQL agent jobs on both primary and secondary replicas:

  • SSIS Failover Monitor Job
  • SSIS Server Maintenance Job

SSIS Failover Monitor Job:

The SSIS failover monitor job executes every 2 minutes. It detects failover using the connected replica states.

SSIS Server Maintenance Job:

The SSIS Server Maintenance job runs daily at 00:00:00. It removes the operation records from the SSISDB that are outside the retention window.

SSIS Jobs

  • Note: The integration service should be running in the primary and secondary replicas else you get the following error while enabling the Always On Support for the integration service catalog. This error occurs because the SSISDB on the secondary replica is not available for reading access

SSISDB error while adding into the SQL Server Always On Availability Group

Perform AG failover for the SSISDB database

In the previous step, we configured the SSISDB in the SQL Server Always On Availability Group. It automatically creates the SSISDB on the secondary replica and synchronizes it. Let’s perform an AG failover.

  • Before failover: Primary replica: SQLNode2\INST1
  • After failover: Primary replica: SQLNode1\INST1

As shown below, the synchronous replica has no data loss under the failover readiness column.

Select New replica for failover

After the availability group failover, both databases [MyNewDB] and SSISDB in the SQL Server Always On Availability Group dashboard.

availability group failover

On AG failover, on the new primary replica, you might get the error 15581. This error asks to create the master key in the database. In this case, you can do the following steps on the new primary replica:

  • Decrypt the master key using the SSISDB encryption password
  • Use the Alter master key on the new primary to add the encryption key
  • Use SSISDB
  • OPEN master Key decryption by password = ‘India@123’
  • ALTER Master Key ADD encryption by Service Master Key

Conclusion

In this article, we configured the integration service catalog database SSIS into the SQL Server Always On Availability Group. You can deploy your SSIS packages using the SQL Listener or the primary replica instance name.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Groups
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups

Rajendra Gupta
Always On Availability Groups, Integration Services (SSIS)

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

7,668 Views