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.

29 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
  7. 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
  8. This comment has been removed by the author.

    ReplyDelete
  9. 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
  10. 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
  11. 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
  12. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  13. 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
  14. There are not a ton of databases that can contrast with the security that MySQL offers.Change Site URL Using MySQL

    ReplyDelete
  15. The Pittsburgh Steelers Steelers Football Live are a professional NFL team based in Pittsburgh. You can watch Pittsburgh Steelers game live streaming online. Get the latest Pittsburgh Steelers schedule, TV channel, news update here.Steelers NFL Game Live
    Steelers Game Today
    Pittsburgh Steelers Game Live
    Live Stream Steelers Game Today

    ReplyDelete
  16. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  17. 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
  18. I want to thank Dr Emu a very powerful spell caster who help me to bring my husband back to me, few month ago i have a serious problem with my husband, to the extend that he left the house, and he started dating another woman and he stayed with the woman, i tried all i can to bring him back, but all my effort was useless until the day my friend came to my house and i told her every thing that had happened between me and my husband, then she told me of a powerful spell caster who help her when she was in the same problem I then contact Dr Emu and told him every thing and he told me not to worry my self again that my husband will come back to me after he has cast a spell on him, i thought it was a joke, after he had finish casting the spell, he told me that he had just finish casting the spell, to my greatest surprise within 48 hours, my husband really came back begging me to forgive him, if you need his help you can contact him with via email: Emutemple@gmail.com or add him up on his whatsapp +2347012841542 is willing to help any body that need his help.

    ReplyDelete
  19. i never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful.
    Check out my Gig on Fiverr: do manually 101 high trust flow backlinks on high da pa
    buy it free

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

    ReplyDelete
  21. 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
  22. 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
  23. This comment has been removed by the author.

    ReplyDelete
  24. 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