By: Alejandro Cobar | Updated: 2021-03-10 | Comments | Related: > Availability Groups
Problem
With SQL Server 2016, Microsoft released full support for the SSISDB inside an Availability Group. However, even if it seems something relatively straight forward, I encountered a particular (rather curious) scenario where the execution of .dtsx packages can fail after a failover of the SSISDB database to another available replica.
Solution
In this tip I will be presenting a test scenario to demonstrate how to reproduce the error (specifically for SQL Server 2016), as well as the steps to fix it, which should save you an enormous headache (one that I particularly already went through).
Initial considerations for test scenario
- I have deployed a test Availability Group, using SQL Server 2016 that consists of 2 replicas.
- The Availability Group has a listener configured.
- To make this work, Integration Services must be installed in each server.
- I have created a dummy test .dtsx file that connects to the listener and executes a simple SQL task. Which you can do using SSDT or Visual Studio, but this article will not cover that in any shape or form.
Steps to reproduce the issue
In the Primary Replica of the Availability Group, proceed to create the Integration Services Catalog.
Make sure that you type the password that will be used to encrypt the SSISDB database.
After going through the wizard, you should have the Integration Services Catalog successfully created.
Now, I will create the folder that will house the dummy .dtsx package I mentioned earlier. I will also create a simple SQL Agent Job that will execute the .dtsx package; the name of the job is "SSIS Test", which must be created in the Secondary Replica as well so that it can run from there after the respective failover.
With the Integration Services Catalog and the SQL Agent Job created, I will proceed to add the SSISDB database to the Availability Group. Remember to specify the same password that you used in the wizard to create the Catalog.
Starting with SQL Server 2016, Microsoft introduced "Automatic Seeding" as a way to add databases to an Availability Group, I’m going to use it.
To fully enable support for the SSISDB, there’s an extra step that has to be performed.
You need to go to "Integration Services Catalog", then right-click and select "Enable Always On Support..."
How can I confirm that Always On Support is enabled for my SSISDB? Well, the process will automatically create a new SQL Agent Job called "SSIS Failover Monitor Job", in both replicas as shown below.
Now, after successfully enabling Always On Support for my SSISDB, I will proceed to run the "SSIS Test" job from my current Primary Replica (DB1).
Without any surprise whatsoever, the execution was successful.
Now, I will manually failover the Availability Group to the replica DB2.
II will trigger the execution of the "SSIS Test" job from DB2, and it should be fine since it is pointing to the listener instead of the replica itself, so there’s no need to go into the job and modify any connection parameters.
We can see above, the execution of the job fails because it attempts to open the encryption key before doing anything, and it simply cannot find it. If it is a critical job and nobody is watching for failures, then you might run into some serious issues when you think it is working fine.
Another thing I have experienced is that even if I manually create the key to make the job succeed, if I failover and failback again, the key is somehow lost and the execution fails again (pretty weird if you ask me).
The solution for this particular case is to avoid using "Automatic Seeding" to deploy the SSISDB in the Secondary Replica(s), because it seems that the encryption key doesn’t travel to the Secondary Replica. Instead, choose the "Full database and log backup" option and you should be good to go.
After re-adding the SSISDB to the Availability Group using a full database and log backup, let’s try to failover to DB2 again and execute the job one more time.
AAs you can see, we have a successful job execution from DB2 because the encryption key travels with the database backup. It seems like something trivial, but if it hasn’t happened to you before, you might get a bit stuck if you decide to use "Automatic Seeding" for cases like this one.
Next Steps
- I’m not sure if Microsoft has already addressed this in newer versions, like 2017 or 2019, but if you’re dealing with 2016, then by all means don’t choose "Automatic Seeding" for your SSISDB database.
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-03-10
