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 db to AG. Now when we give the password, it is complaining as incorrect password (I have given the password which I have given at th the time of creation of catalog).When we run the schedule SSISDB jobs it is toughing below error.
Error for 1:- while setting up SSISDB as a part of Ag, it is toughing error.: in correct password.
Error for 2:- while running the job this below is the error.
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 appreciate if you turn up quickly, so that users/down stream system are not getting effected.