Summary:
We had migrated SSISDB from SQL Server version 11.0.6260 to SQL Server version 13.0.5830.85. we noticed that SSISDB on SQL Server 2016 is at healthy state later after we restore and had converted the compatibility to new version. We have all the projects/packages/environment variables came as ese. Even in SQL agent job, when we open the steps and see the package configuration/and datadource they also turned up.
How did we get the backup from the old server SQL Server version 11.0.6260 For SSISDB: We had taken native backup and have backed up master key with encrypted password.
How did we restore on new server SQL Server version 13.0.5830.85 on SSISDB: we restored using move with, decrypted the password (for the above master key) and encrypted with new password.
How did we create SSISDB catalog: We created ssisdb integration catalog with normal standard have a password to it.
Note:- We are also trying to set up AG for SSISDB.
Issue: We are having 2 issues.
- While we setting up for AG for SSISDB, as always we need to give the password of SSISDB catalog at the time to creation, while adding to AG. Now when we give the password, it is complaining as incorrect password.
- When we run the schedule SSISDB jobs it is toughing below error.
Error for 1:-
Error for 2:-
Please create a master key in the database or open the master key in the session before performing this operation. Source: .Net SqlClient Data Provider Started: 10:38:07 AM Finished: 10:38:07 AM Elapsed: 0.187 seconds. The package execution failed.
I need step by step resolution for both above issues, as i have tried multiple methods to get rid off error2. Working on masterkey and all..
We really appricate if you turnup quickly, so that users/down stream system are not getting effected. If you have any questions feel free to send email/ call us.
