In this blog, I would like to address one of the issues that I came across recently with the SQL Server Integration services package execution in SQL always on environment.
In this scenario, to use SSIS with Always On, you have added the SSISDB catalog in the Availability Group and deployed the SSIS packages to the SSISDB catalog and created agent job which executes the package in the catalog.
The primary reason to use SSIS with Always On is to leverage the failover capabilities. When a failover occurs, one of your secondary nodes automatically becomes the new primary node.
Unfortunately, when this happens with SSIS, you will get an error when the system tries to run a package on the new primary node:
Error:
Please create a master key in the database or open the master key in the session before performing this operation.
Work Around:
To resolve this, the database master key needs to be re-encrypted by the service master key after failover occurs a mentioned in the below article.
https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/
Having to manually run a script every time failover occurs isn’t ideal. To fix this issue, you can follow the below process.
Solution: While adding the SSISDB database to an Always on Availability Group on the “Select Initial Data Synchronization” step, you have to choose “Full database and log backup” as the data synchronization preference instead of the Automatic seeding as shown below.
Once you added the SSISDB catalog to the Availability group successfully ,then you can Enable SSIS support for Always on as per the article : https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017#...
Now you can test by deploying the SSIS package to the SSISDB catalog and execute the SSIS package by performing a failover to the secondary replica. Job execution was successful, no need to re-encrypt the database master key by the service master key after every failover occurs.
Author: SatyaSai K – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Krishnakumar Rukmangathan - Support Escalation Engineer, SQL Server BI Developer team, Microsoft