Step by step guide to add SSISDB to SQL Server 2016 Always On Availability Group (AG)

Starting from SQL Server 2016, you can add SSISDB as Availability Database in Always On Availability Group (AG). This enables high availability and automated failover for SSISDB. This article provides step by step guide to add SSISDB to Availability Group.

Make sure, you have installed SQL Server Integration Services on both primary and secondary replicas.

Step 1: Create SSIDB on primary replica:

Right-click on the Integration Services Catalogs and click on Create Catalog Wizard option

Creating Integration Service (SSIS) Catalog
Creating Integration Service (SSIS) Catalog

Step 2: Configure Integration Service Catalog:

On Catalog Creation Wizard select the Enable CLR integration and Enable automatic execution of Integration Services stored procedure at SQL Server startup. Then enter the password on Password and Retype Password text boxes and click OK button.

Configuring Integration Service Catalog
Configuring Integration Service Catalog

Step 3: Adding SSISDB to Availability Group:

On primary replica, add SSISDB to an Availability Group (AG). Expand the Always On High Availability and then Availability Groups. Right-click on Availability Group and click on Add Database option.

Adding SSISDB to Availability Group
Adding SSISDB to Availability Group (AG)

Step 4: Select database and decryption password:

On Add Database to Availability Group wizard, click Next button. All the eligible databases will be displayed here. Against SSISDB you will see Password required. Enter the password on Password column and click on Refresh button.

Always On - SSISDB prerequisites
Always On - SSISDB prerequisites

Now you will see the Meets prerequisites on status column. Click on the Next button.

SSISDB Selection for AG and decryption password
SSISDB Selection for AG and decryption password

Step 5: Selecting Secondary Replicas:

On Connect to Existing Secondary Replicas page, you can either connect to selective secondary replicas by clicking on Connect button against each replica name or you can connect to all secondary replicas by clicking on Connect All button.

You will be prompted to enter username and password, now connected to secondary replica as mentioned user. Click on the Next button.

Choosing secondary replicas for SSIS Availability Database
Choosing secondary replicas for SSIS Availability Database

Step 6: Initial Data Synchronization:

On Select Initial Data Synchronization page, Select your data synchronization preference. Since, my SSISDB size is very small and my cluster nodes are in same data centre, I am selecting Automatic seeding. You may use Full database and log backup option or use other options as your requirement and click Next.
SSISDB - Always On - Initial Data Synchronization
SSISDB - Always On - Initial Data Synchronization

Step 7: Validation:

Validate and fix the failed requirements and click on Next button.
Adding SSISDB to Availability Group (AG) - Validation
Adding SSISDB to Availability Group (AG) - Validation

Step 8: Summary:

Verify and confirm the choices made in the adding SSISDB to Availability Group wizard and click on Finish button to continue.

Adding SSISDB to Availability Group (AG) - Summary
Adding SSISDB to Availability Group (AG) - Summary

Step 9: Results:

The Results page provide the status of adding SSISDB to Availability Group. Click on Close to exit the wizard.
Adding SSISDB to Availability Group (AG) - Results
Adding SSISDB to Availability Group (AG) - Results

Step 10: Enabling Always On Support:

Right-click on the Integration Service Catalogs and click on Enable Always On Support option.    

Enabling Always On Support for Integration Service Catalog
Enabling Always On Support for Integration Service Catalog

Step 11: Connecting to secondary replicas:

On Enable Support For AlwaysOn wizard, click Connect All to connect to all secondary replicas and click OK button to close the wizard.

Enable Support for Always On - Connecting secondary replicas
Enable Support for Always On - Connecting secondary replicas

Step 12: Verifying Always On Support:

Post completing the previous step, you can verify the Always On support by checking the SQL Server Agent jobs. The following 2 jobs are created for Failover Monitor and Server Maintenance on both primary and secondary replicas.

Verifying Always On Support for Integration Service Catalog
Verifying Always On Support for Integration Service Catalog

Step 13: Monitoring SSISDB Availability Database Health:

Open the Availability Group Dashboard and check the status of the SSISDB and Availability Group Database Synchronization status.

Monitoring SSIDB Availability Database Health
Monitoring SSIDB Availability Database Health

Great! You have successfully added SSISDB as Availability Database on an Availability Group (AG).
Note: When you are upgrading / patching, remove the SSISDB from Availability Group, apply the patches and add it back to Availability Group as mentioned here. SSISDB in Always On Availability Group rule checks the SSISDB upgradation or patching status. I hope this article helps you. Please share your comments below and if you have difficulty in following any of the above steps, write in comment section, I will get back to you as soon as possible.

0 thoughts:

Post a Comment