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.
In case you do not want to watch the video, below is the process I did to add the SSISDB into the availability group.
- On the Primary Replica, open SQL Server Management Studio
- Right click on Integration Services Catalog, click Create Catalog
- 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.
- 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.
- 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
- On the Add Database to Availability Group dialog, ensure that Automatic Seeding is selected. Click Next.
- You will be presented with a list of the secondary replicas, connect to each one listed. Then click Next
- The information will be validated. As long as there are no failures, click Next.
- On the summary dialog, click Finish. SSMS will add the database to the availability group
- Click Close
- 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)
- 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.