July 28, 2016 at 11:41 am
We're in the process of upgrading / migrating to SQL2014 from SQL2012 and SQL2008R2. The "normal" databases are no problem (backup on old server, copy, restore on new, test.)
My question is related to the SSIS Catalog database (SSISDB.) Right now, I see three possible ways to do this, and I'm interested in what others see as the preferred option.
Option 1: Create a new SSIS Catalog on the new servers, then backup the existing SSISDB and key and restore over-top the "blank" DB on the new servers.
Option 2: Backup and restore the existing SSISDB, then follow the steps on MSDN to create the required stored procedures and such.
Option 3: Create a new SSIS Catalog on the new servers, and have the application owners transfer their packages / environments / etc over themselves (setting up the existing folders beforehand for them)
I'm leaning towards option 3, myself, as there's only a couple people currently using SSIS Catalog and they've only got a few packages. The rest of our customers are still using "classic" SSIS packages in MSDB, which I can transfer over fairly easily (then as-needed they go in to check them over.)
July 28, 2016 at 1:34 pm
jasona.work (7/28/2016)
We're in the process of upgrading / migrating to SQL2014 from SQL2012 and SQL2008R2. The "normal" databases are no problem (backup on old server, copy, restore on new, test.)My question is related to the SSIS Catalog database (SSISDB.) Right now, I see three possible ways to do this, and I'm interested in what others see as the preferred option.
Option 1: Create a new SSIS Catalog on the new servers, then backup the existing SSISDB and key and restore over-top the "blank" DB on the new servers.
Option 2: Backup and restore the existing SSISDB, then follow the steps on MSDN to create the required stored procedures and such.
Option 3: Create a new SSIS Catalog on the new servers, and have the application owners transfer their packages / environments / etc over themselves (setting up the existing folders beforehand for them)
I'm leaning towards option 3, myself, as there's only a couple people currently using SSIS Catalog and they've only got a few packages. The rest of our customers are still using "classic" SSIS packages in MSDB, which I can transfer over fairly easily (then as-needed they go in to check them over.)
I'd also lean towards Option 3 (remembering to 'fix' the 'Retention Period' and 'Maximum Number of Versions Per Project' attributes as needed).
If you've managed to script your environments and project configurations, so much the better.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 9, 2020 at 3:23 pm
Sorry All,
I didn't see any location where to post my question. So I'm posting my question here, please excuse me to change the topic in between. Blow is my question for you all.
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 data source 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 troughing below error.
Error for 1:-
it is showing incorrect password, tough I have entered right password, next to the database name in Wizard of second screen to check the DB for setting up of AG.
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.. I really apricate 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.
you reply back to me at sns_subash2002@yahoo.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply