Adding SSISDB to AG for SQL Server 2016
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!
Data Platform MVP
MCSE Data Platform SQL Server 2016
Posted on April 28, 2017, in Availability Groups, Dicas, Integration Services, SQL Server 2016, VirtualPass and tagged Add SSISDB to AG, Add SSISDB to AlwaysOn, Integration Services, SQL Server 2016, SSISDB Always On, SSISDB AlwaysON, SSISDB with AlwaysON. Bookmark the permalink. 16 Comments.