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.

25 comments:

  1. Very detailed and well written article!

    ReplyDelete
  2. 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
  3. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information... bestarkserverhosting.com

    ReplyDelete
  4. 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
  5. Just pure classic stuff from you here. I have never seen such a brilliantly written article in a long time. I am thankful to you that you produced this! https://serverbrowse.com/

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thanks for the nice article. Is this tested?
    I have couple of questions or concerns...
    Most of the packages are scheduled by jobs.
    The jobs will be run on Primary. But if failover happened then how this mechanism control the schedule of jobs. Also if the failover happened in the middle of job execution, how the setup will fix that scenario. Awaiting your answers.

    ReplyDelete
    Replies
    1. Hi Krishna,

      Generally, in AG we sync all agent jobs and schedules, however, at the time of execution checking, whether the server is in primary role or not, if primary it will go to next step and run further.

      Check dbatools ag utilities for syncing objects on AG.

      When fail-over happens, the server will wait for either transaction to complete or terminate. In case of terminating, the client has to restart the transaction, most of the times default driver settings will take care of this, you can explicitly handle this server unavailability.

      I hope it helps, thank you.

      Delete
  8. First of all THANK you very much for your prompt response Rathish ji...
    "we sync all agent jobs and schedules" what does it mean... can you kindly shred some light? You mean Always On do that automatically or do we do it manually? If we need to do it manually then can you share your excellent knowledge and experience how to do that?

    Can you please take a few minutes for this because our company want to implement this in HA for SSIS in a big project. really it helps your guidance for us sir.

    ReplyDelete
  9. Krishna,
    AG does not sync automatically, we have to sync manually or schedule it in regular intervals or deploy changes in all the replica instances.

    I regularly use dbatools availability group utilities to perform this task:

    https://docs.dbatools.io/#Sync-DbaAvailabilityGroup

    Now, jobs will be available in all the instances, to avoid multiple execution of same job, you have to verify whether is it try to execute on primary instance, if not stop execution, refer the below article to implement this checking:

    https://stuart-moore.com/making-sql-agent-jobs-availability-group-aware-with-dbatools/

    If you have further queries, please connect me through LinkedIn.

    ReplyDelete
  10. They are scalable, accommodating, understanding of ultimate goals, ux studio design team requires minimal oversight. It excels both technically and creatively, and is highly proactive, competent, and responsive to feedback.

    ReplyDelete
  11. There are not a ton of databases that can contrast with the security that MySQL offers.Change Site URL Using MySQL

    ReplyDelete
  12. What a post and it is absolutely much helpful for everyone. This post is really neat and clean. I love your explanation way. I will often visit your blog for knowledge. Keep it up.

    ReplyDelete
  13. I really appreciate this wonderful post that you have provided for us. I assure this would be beneficial for most of the people. שרת וירטואלי

    ReplyDelete
  14. Very interesting blog. Alot of blogs I see these days don't really provide anything that I'm interested in, but I'm most definately interested in this one. Just thought that I would post and let you know. raklapos szállítmányozás Europa-Road Kft

    ReplyDelete
  15. Hello,
    Enable Always On Support for SSIS Not Working in SSMS. No server found to add on "Connecting to secondary replicas". Could you help me?

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. That appears to be excellent however i am still not too sure that I like it. At any rate will look far more into it and decide personally! theplaynews

    ReplyDelete
  18. Omg I Finally Got Helped  !! I'm so excited right now, I just have to share my testimony on this Forum.. The feeling of being loved takes away so much burden from our shoulders. I had all this but I made a big mistake when I cheated on my wife with another woman  and my wife left me for over 4 months after she found out..  I was lonely, sad and devastated. Luckily I was directed to a very powerful spell caster Dr Emu who helped me cast a spell of reconciliation on our Relationship and he brought back my wife and now she loves me far more than ever.. I'm so happy with life now. Thank you so much Dr Emu, kindly Contact  Dr Emu Today and get any kind of help you want.. Via Email emutemple@gmail.com or Call/WhatsApp +2347012841542

    ReplyDelete
  19. Some truly interesting info , well written and broadly user friendly.
    chocolate day messages
    valentines day wishes

    ReplyDelete
  20. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. dr-wall

    ReplyDelete
  21. Hi, Firstly thanks for this article but i have one question. Can we use secondary role for ETL solution? I mean Can we use only secondary roles for SSIS because ı don't want to use primary role. Thanks.

    ReplyDelete
  22. Thank you for excellent article.You made an article that is interesting.
    data science training in noida

    ReplyDelete