Enabling AlwaysOn for SSISDB

I am at client where I get to do some fun work. I get to work on a brand new environment that is dedicated to SQL Server. It has some pretty cool specs. Biggest thing is that they are going all 2016. Both the OS and SQL Server will be 2016. This has let to a lot of fun and interesting discussions. One of those being how do we place the SSIS Catalog into an availability group.

This led me to ponder the possibilities. When AlwaysOn first came out, this was not supported. Sure, you could find clever work-arounds for this, but at the end of the day, they were not sanctioned by Microsoft. This would then leave the client in a bit of a bind if they ran into issues. However, starting in SQL Server 2016, this is now supported. Microsoft has made it possible to officially place the SSISDB into an availability group.

Which group you put the SSISDB in, will be a design question that you will need to answer for yourself. In short, it should go into a group that makes sense. If all your databases are in one group, then placing this in DB into the same group makes sense. If you have multiple groups, you may need to make a choice that best suits your application needs.

In this example, I will assume you already have an availability group set up with a listener. In my lab, I have a very simple setup. my availability group is called Group1 and my listener is called Listener1.

This video shows the steps I took to add the SSISDB into an availability group. I even show publishing a SSIS package to the listener name instead of a SQL Server Name.

This video doesn’t exist

In case you do not want to watch the video, below is the process I did to add the SSISDB into the availability group.

  1. On the Primary Replica, open SQL Server Management Studio
  2. Right click on Integration Services Catalog, click Create Catalog
  3. On the Create Catalog Dialog,
    Check the box to enable automatic execution of Integration Services stored procedures at SQL Server startup. Then enter a strong password.
  4. Open the Availability Group folder and navigate to the group you want to add the SSISDB to. Right click on Availability Database and click Add Database.
  5. Due to the encryption that the SSISDB uses, you will need to provide the password you used when creating the catalog. Put that password into the password field, click refresh and then click the check box next to SSISDB. Click Next
  6. On the Add Database to Availability Group dialog, ensure that Automatic Seeding is selected. Click Next.
  7. You will be presented with a list of the secondary replicas, connect to each one listed. Then click Next
  8. The information will be validated. As long as there are no failures, click Next.
  9. On the summary dialog, click Finish. SSMS will add the database to the availability group
  10. Click Close
  11. Once the database has been added to the availability group, go to the Integration Services Catalog folder, right click and click Enable AlwaysOn Support. (you may need to refresh this folder before the option is available to you)
  12. Click connect next to each secondary replica. Then click OK.

The SSISDB is now established in an availability group and will failover based on the group rules.


  1. I am doing exactly what you are doing but I can not get the SSISDB created on the secondary replica. My question to you is do you need to first Create a catalog on the secondary replica as well?


    1. By using the Automatic Seeding option when adding the database to the availability group, you should not need to create the database on the replica server. Automatic Seeding will take care of the database creation for you. Are you using SQL Server 2016?


  2. Can packages be executed from either node? Or will they only run from whichever node is the primary replica for the AG that SSISDB resides in?

    E.g. sql2016n1 is your Primary Availability Replica. Can you go to sql2016n2 and execute a package?


    1. You would have to test this to be sure. I don’t think you could execute a package on N2 because that database is read only. So you would not be able to log any of the output of the package.


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 )

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.