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. 26 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

    • Hi, it was the same for me: SSISDB not synchronized in both sencondary replicas.

      I’ve found that the reason is that the wizard, when you add the SSISDB to the AG (before enabling AWON support), executes a script that set the seeding mode of the replicas in MANUAL MODE, in this way

      USE [master]



      So, if you want SSISDB get added and synchronized in all the other secondary replicas, you have to re-set the seeding mode to AUTOMATIC manually (on the primary replica):

      USE [master]



      Tested the failover to both secondary replicas and returned to the original configuration : it was all OK.

      My MS SQL SERVER version is :
      Microsoft SQL Server 2016 (SP2-CU7-GDR) (KB4505222) – 13.0.5366.0 (X64)

  6. Thanks for the post, I do have SSIDB that is encrypted and now I am having issue to fail over to the secondary. what might be the issue? Thank you for the help!

    • Rashid,

      You need to re-encrypt the master key after the failover to the secondary. There’s Microsoft blog post explaining that.

      I’ll also try to post something on my blog.

      • Hi Marcos,
        I can’t find any info about re-encrypting SSISDB mater key after failover anywhere in MS docs.
        Are you able to provide the link?

  7. Hi Marcos,
    Do you know if this is supported in a Distributed Availability Group?

    • Hi Jonathan! Unfortunately, I don’t have such an environment witg distributed AG, so I can’t tell for sure..

      I’d say that your best chance would be the official docs for SSIS and see if there’s anything in there

  8. Hi Marcos,
    Most of my SSIS packages are triggered by SQL Agent Jobs. When I create a new SQL Agent Job, should I create it on both primary and secondary instances?

  9. Kerry Bossingham

    Hi Marcos,
    Thanks for the article! Question though, it’s set up as you have laid out and an SSIS package kicks off on the primary replica. In the middle of its processing, the AG fails over to a secondary replica. What happens to the SSIS job? Does it abend on the primary replica or does SQL in essence, fail it over too and it keeps executing (seems unlikely)?

    • Hi Kerry. If a failover happens in the middle of the execution, the package then stops it’s execution. To control that, you would need to create certain mechanisms in the package itself to identify when a failover happens..

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

  2. Pingback: Kartor, SSISDB, … |

  3. Pingback: /* — */ |

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: