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.

7 comments:

  1. Awesome blog. I enjoyed reading your articles. This is truly a great read for me.


    data analytics course

    data science course

    business analytics course

    ReplyDelete
    Replies
    1. LOVE SPELL CASTER (DR. GBOJIE) THAT HELPED ME RESTORE MY RELATIONSHIP 
       I was absolutely desperate to get my husband back. Life without my husband was a real mess for me and my children. i wanted a dramatic change and I thought love spell could be the solution. After discussing the resolution with Him, he gave me hope that he will restore my marriage. I felt confident that he will actually make my husband to return home and he did! It’s fantastic what this great spell caster has done for me, his help is priceless! I don’t know what I would have done without his help, He does his job so well he is organized and highly functional, i believe he is the best service to use i can count on when it comes to restoring relationship, I was floored that his worked was perfect, if you need help, ️ ️ ️ ️ ️ ️ ️ ️ gbojiespiritualtemple@gmail.com or whatsapp :  +2349066410185 check out his website :http://gbojiespiritualtemple.website2.me    

      Delete
  2. Very detailed and well written article!

    ReplyDelete
  3. By today, thousands of people from all over the world spend hours per day playing online games. Internet gaming once were very simple, consisting mostly of classic games like gambling internet and the many popular casino games men and women would normally play at a true casino. best ark survival server hosting

    ReplyDelete
  4. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information... bestarkserverhosting.com

    ReplyDelete
  5. LOVE SPELL CASTER (DR. GBOJIE) THAT HELPED ME RESTORE MY RELATIONSHIP 
     I was absolutely desperate to get my husband back. Life without my husband was a real mess for me and my children. i wanted a dramatic change and I thought love spell could be the solution. After discussing the resolution with Him, he gave me hope that he will restore my marriage. I felt confident that he will actually make my husband to return home and he did! It’s fantastic what this great spell caster has done for me, his help is priceless! I don’t know what I would have done without his help, He does his job so well he is organized and highly functional, i believe he is the best service to use i can count on when it comes to restoring relationship, I was floored that his worked was perfect, if you need help, ️ ️ ️ ️ ️ ️ ️ ️ gbojiespiritualtemple@gmail.com or whatsapp :  +2349066410185 check out his website :http://gbojiespiritualtemple.website2.me    

    ReplyDelete
  6. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. https://serverbrowse.com/

    ReplyDelete