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.

image

image

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

image

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.

image

 

 

 

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

image

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

image

 

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.

image

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

image

 

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.

image

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

image

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”

image

Connect to the secondary replicas and click ok.

image

Done! The SSISDB is now in the AG.

image

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.

https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

I hope you enjoyed the tip!

Marcos Freccia
Data Platform MVP
MCSE Data Platform SQL Server 2016

Advertisements

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. 3 Comments.

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

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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: