Introduction
Incorporating SQL Server Integration Services (SSIS) with Always On Availability Groups (AGs) is not just a routine task but a necessary one to ensure the robustness of your data integration workflows. While SSISDB plays a central role in managing SSIS projects, it doesn’t inherently align with the high availability features provided by AGs. This guide aims to bridge that gap, offering a detailed and practical approach to integrating SSISDB with Always On Availability Groups, tailored to the unique demands of this database.
Step 1: Understand SSISDB and Always On Availability Groups
SSISDB is the database that stores deployment and execution information for SSIS projects. By default, SSISDB is tightly coupled with the SQL Server instance where it’s created. Unlike typical user databases, SSISDB does not natively support being part of an Always On Availability Group, requiring additional configuration steps to achieve high availability.
Step 2: Ensure Prerequisites are Met
Before proceeding, ensure that your environment meets all prerequisites for Always On Availability Groups. This includes verifying that your SQL Server instances are properly configured and that the necessary Always On features are enabled.
Step 3: Create the SSISDB Catalog on All Nodes
Create the SSISDB catalog on every node that will participate in the Availability Group. This step is essential as it generates the required server-level objects, such as cleanup jobs, keys, and accounts, necessary for SSIS functionality on each node. Connect to each node individually and create the SSISDB catalog.
Step 4: Remove SSISDB from Secondary Nodes
After the SSISDB catalog is created on all nodes, delete the SSISDB databases on the secondary nodes. This ensures there are no conflicts when adding SSISDB to the Availability Group.
Step 5: Create the Availability Group
Create an Availability Group and include SSISDB as a user database in the group. This step links SSISDB to the Availability Group, enabling it to benefit from the high availability features of the AG.
Step 6: Configure Secondary Replicas
When creating the Availability Group, configure the secondary replicas that will host the SSISDB database. Ensure that configurations such as failover mode and synchronization type are appropriately set according to your environment’s requirements.
Step 7: Enable Always On Support for SSISDB
On the primary node, right-click the Integration Services Catalog and enable Always On support. This step ensures that SSISDB is fully integrated with the Availability Group, allowing it to function within a high availability environment.
Monitoring SSISDB After Integration
SSIS Failover Monitor Job
After enabling Always On support for SSISDB, a new job called SSIS Failover Monitor is created. This job monitors the failover status of SSISDB within the Availability Group, ensuring that SSIS operations continue without interruption during failover events.
AlwaysOn_Support_State Table
The alwayson_support_state table in the SSISDB is automatically updated when a failover occurs, reflecting the new state of the server nodes. This table includes:
| server_name | state |
|---|---|
| VM2 | 1 |
| VM3 | 2 |
In this example, VM2 is the primary node (state = 1), and VM3 is the secondary node (state = 2). After a failover, the table might look like this:
| server_name | state |
|---|---|
| VM2 | 2 |
| VM3 | 1 |
Handling Failovers and Error (15581)
During a failover, you may encounter error 15581 when attempting to run a package on the new primary node:
“Please create a master key in the database or open the master key in the session before performing this operation.”
This error occurs due to SSISDB’s encryption of sensitive information. To resolve it, the database master key needs to be re-encrypted by the service master key after the failover. Run the following T-SQL script to re-encrypt the master key:
USE SSISDB;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your_password'; -- Replace 'your_password' with the password used when creating SSISDB
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;Once re-encrypted, the new primary node will be able to run SSIS packages.
Recommendations: Avoid Auto-Seeding
When adding SSISDB to an Availability Group during the “Select Initial Data Synchronization” step, choose “Full database and log backup” as the data synchronization preference instead of Automatic Seeding to avoid potential issues.
Key Considerations: Handling Updates and Patches
Using SSISDB with Always On introduces complexities, particularly when applying cumulative updates or service packs that modify the SSISDB schema. Consider the following:
- SSISDB Patching Process: SSISDB patches are installed when the SQL Server instance starts up. However, if SSISDB is part of an Availability Group, it may be in an OFFLINE state at startup, causing the patching process to fail.
- Mitigation Strategy: To successfully apply patches, first remove SSISDB from the Availability Group. After patching each node, recreate the Availability Group.
Conclusion
Successfully integrating SSISDB with SQL Server Always On Availability Groups involves more than just following standard procedures; it requires careful attention to the specific requirements and nuances of SSISDB. From setting up catalogs across nodes to handling the intricacies of failover scenarios, each step is crucial in maintaining the continuity and reliability of your SSIS operations. By implementing these strategies, you’ll not only achieve high availability but also ensure that your integration processes remain resilient in the face of system changes and updates.