Adding SSISDB to AG for SQL Server 2016

Hi Everyone,

I’ve setup a new SQL Server 2016 AG Environment for Developers where they will use it for deploying and testing code, which means that they also want to SQL Server Integration Services for deploying and executing SSIS Packages. From SQL Server 2012 and onwards we have the new deployment model for SSIS and also the addition of SSISDB.

One of the features highly requested was the ability to add SSISDB to AG, which was also added, but before SQL Server 2016 the failover of the SSISDB database has to be done manually, on which you end up having more work to do. In SQL Server 2016 it was added the possibility for automatic failover of SSISDB.

First thing you need to do is in the primary replica to create the SSISDB.



After you created the database in the primary replica, add it to the AG.


Here is something tricky you need to do. In your setup the option to select SSISDB is blocked, because firstly you need to specify the same password that you used to create the SSISDB, because this key will be used to create the SSISDB in the secondaries later.





After you fill up the password, you will need to click on REFRESH button in the bottom screen. Seriously, you need to do it!


Then, after the first refresh (let’s say that) , what you see is.



However you still cannot select the database, because for the second time you need to click in the REFRESH button. After that’s done you will be able to select and proceed with the setup.


Now, this is very important. Do not select the neither “Full” nor “Join only” synchronization methods.



All the initial pre-checks are skipped and the last validation mentions the necessity of later the Enable AlwaysOn support to be done for SSISDB.


Follow the steps to end and you will get something like that.


After done, make sure to refresh Management Studio (I had to do that). Then, right click on Integration Services Catalog and select the option “Enable AlwaysOn Support”


Connect to the secondary replicas and click ok.


Done! The SSISDB is now in the AG.


Something you should pay attention is the process to roll out uprades or patches to SQL Server when there is a SSISDB in the AG, which the whole explanation you will find it in this document below.

I hope you enjoyed the tip!

Marcos Freccia
Data Platform MVP
MCSE Data Platform SQL Server 2016

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on April 28, 2017, in Availability Groups, Dicas, Integration Services, SQL Server 2016, VirtualPass and tagged , , , , , , . Bookmark the permalink. 14 Comments.

  1. Hello Marcos,
    thanks you very much for sharing this information. It worked like a charm!
    Best regards

  2. You say that it’s very important to select “skip initial data synchronization,” but don’t explain why. Could you explain why this matters?

  3. Marcos,

    When I failover my instance, my new primary server complains about the master key.

    Could you help me? How the correct way to restore de ssisdb database?

  4. Hi Marco, Thanks for the article. I have a few questions on this task.

    Do I need to Install SSIS on secondary server before creating catalog on primary server?

    And Could please verify my steps here… I have AlwaysOn AG set up now I am installing SSIS .
    1. Install SSIS services on the primary
    2. Install ssis services on secondary
    3.Create catalog on primary
    4. and follow your steps to configure ssisdb into AG group
    5. END

    Is anything else I should do?

    • Hi eswar9,

      Thanks for commenting here and for your question. For your first question the answer is yes, you need to install the SSIS Services on both servers (Primary & Secondary). So, your step by step would work and it is correct.

      If you have any other questions, let me know!

  5. Hi Marcos
    Thanks for your blog, so appreciative of people sharing their knowledge! I followed all your steps but sadly it is not working for me. I go through all the steps and I can add SSISDB to the availability group, it says it’s in a syncrhonized state on the primary replica. But when i look at my secondary replica, SSISDB is not there??? I then check the AlwaysOn Dashboard on the primary and it has Warnings – if i expand into the AG, it says that SSISDB is not joined??
    What have I missed? How does SSISDB get created on the other side? You say you need to choose the ‘skip synchronization’ step but how does SSISDB get created on the replica? Do we not have to do it manually?

    • Hi Doodes,

      Thanks for commenting here. Would you be able to send me an e-mail with some print screens of your setup (please, hide server names and etc..)? My e-mail is freccia at

  1. Pingback: The most read posts of June – 2017 | Freccia's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: