SSISDB and SQL Server 2016 Availability Group Issue with Automatic Seeding


By:   |   Updated: 2021-03-10   |   Comments   |   Related: More > 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.

create catalog

Make sure that you type the password that will be used to encrypt the SSISDB database.

catalog creation wizard

After going through the wizard, you should have the Integration Services Catalog successfully created.

ssisdb

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.

sql agent jobs
job steps

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.

add database to availability group

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.

add database to availability group

To fully enable support for the SSISDB, there’s an extra step that has to be performed.

add database to availability group

You need to go to "Integration Services Catalog", then right-click and select "Enable Always On Support..."

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.

sql jobssql jobs

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).

log file viewer

Without any surprise whatsoever, the execution was successful.

Now, I will manually failover the Availability Group to the replica DB2.

fail over availability group

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.

log file viewer

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.

add database to availability group

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.

log file viewer

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.





get scripts

next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips


Article Last Updated: 2021-03-10

Comments For This Article





download














get free sql tips
agree to terms