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.
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
Click Ok. It creates an integration service catalog and its corresponding SSISDB in your database instance, as shown below.
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.
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.
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.
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.
In the wizard, you get the SSISDB into the user database list. Its status shows as Password Required.
Enter the SSISDB password that you specified for the encryption purpose.
Click on Refresh and its status changes to 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.
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.
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.
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.
In the next validation step, you get a 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.
Ignore the Warning as of now. In the summary page, verify the AG database configuration.
It added the SSISDB successfully into the availability group.
Launch the AG dashboard. The SSISDB is in the synchronized status, as shown below.
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.
It opens the following Enable Support for AlwaysOn wizard, and it asks you to connect all new secondary replicas.
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.
- 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
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.
After the availability group failover, both databases [MyNewDB] and SSISDB in the SQL Server Always On Availability Group dashboard.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023