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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.