August 10, 2020 at 2:31 am
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 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.
August 11, 2020 at 3:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 20, 2020 at 3:44 pm
This was removed by the editor as SPAM
September 5, 2020 at 7:45 pm
Hi Srinivas,
This could be because of different master keys on Server A(source) and Server B (target).
You have to create a master key on the target instance that you have restored the SSISDB. After that you should be able to add SSISDB to AG.
=======================================================================
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply