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. 10 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?

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: