0

I have SSIS packages in msdb database as well as in the ssis catalog (ssis db).

What is the recommended approach to migrate SSIS and related packages from sql server 2016 to sql server 2019?

For example -

  1. Do I need to backup and restore the ssisdb?
  2. or do I need to re-create the ssis catalog that will auto create the new ssisdb followed by manual deployment of the ssis packages?
  3. How to handle ssis packages in the msdb?
Improve this question
1

2 Answers 2

Reset to default
0

Wow...sorry but so much bad advice here. First don't upgrade in place as a best practice due to carrying over old versions of software...rather create a new fresh server and deploy and test there. Second, the upgrade wizard hasn't been supported for years since 2016 unless you're upgrading an earlier version than that. Why are people still saying to use it is beyond me (it'll just show list of no packages to upgrade - have fun with that). Third, upgrading takes place in VS/SSDT and happens twice, once when you change deployment to 2019 version and then when you open every package. And every package you open takes a millenium to open. So far I've found no workaround for that.

There is an upgrade SSISDB wizard - I wouldn't do that because again you're upgrading existing rather than starting new which inherits any legacy debt on your server.

Improve this answer
1
  • 2
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Dec 16, 2022 at 21:37
-1

Option 1 and 2 are good options for ssisdb.

About the dtsx in the msdb, those usually come from wizards like import/export features in ssms or maintenace plans.

If you need them for sure it's quite complicated.

here you can find a good guide: link

Improve this answer
7
  • Does backup and restore of the ssisdb auto create the SSIS catalog?
    – variable
    Commented Apr 13, 2022 at 11:06
  • @MBuschi You cannot restore msdb backup of sql server 2016 on SQL Server 2019, so how is option 1 good ?
    – Shanky
    Commented Apr 13, 2022 at 11:22
  • updated. for ssisdb
    – MBuschi
    Commented Apr 13, 2022 at 11:46
  • 1
    @MBuschi But option 1 is NOT possible at all. So you need to completely remove that because as per OP he is moving from 2016 to 2019.
    – Shanky
    Commented Apr 13, 2022 at 12:06
  • The database will be updated by the engine to the last version if you run the SSISDB Upgrade Wizard. Be sure to backup/restore encryption key too. Otherwise go for the option 2.
    – MBuschi
    Commented Apr 13, 2022 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.