When upgrade SSISDB, a few steps need to be considered when moving the SSISDB to a higher version on a new SQL instance.
1; Backup currently database master key
BACKUP MASTER KEY TO FILE = 'file location/name' ENCRYPTION BY PASSWORD = 'your password for the backup key file'
If you forgot the current password for the master key, you can reset the password via backup the master key (if not already have one) and then restore it with a new password
BACKUP MASTER KEY TO FILE = 'file location/name' ENCRYPTION BY PASSWORD = 'your password for the backup key file'
RESTORE MASTER KEY FROM FILE = 'file location/name'
DECRYPTION BY PASSWORD = 'the password used for backup'
ENCRYPTION BY PASSWORD = 'your new master key password'
FORCE
--Then backup the master key again
2; Backup the SSISDB on the old instance.
3; On the new instance, before restore the SSISDB from backup in above step2, right click Integration Services Catalogs and select Create Catalogs. This will create a new SSISDB and register required objects and assign all required permissions etc. If you didn’t do Create Catalogs first but just restored the SSISDB from backup, the Create Catalogs option will be grayed out, even if you removed the SSISDB. If that’s the case, you can use the following Powershell script to re-initial the SSISDB again.
#This script is to fix the Integration Service Catalog if restored the SSISDB without first create the catalog
#Before run this script, backup the SSISDB if needed and then delete the SSISDB
#https://lazysqlwriter.wordpress.com/2019/04/13/how-to-restore-ssisdb-to-another-server-migrating-ssis-catalog/
#https://docs.microsoft.com/en-us/answers/questions/677655/ssis-migration-from-2016-to-2019.html
# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”)
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
Write-Host “Connecting to server …”
# Create a connection to the server
$sqlConnectionString = “Data Source=hostedsql,1435;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
# Create the Integration Services object
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection
# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace”.Catalog” ($integrationServices, “SSISDB”, “M@xG30SSISDB”) #REPLACE THE PASSWORD
$catalog.Create()
4; After SSISDB restored, run the following SQL commands
OPEN MASTER KEY DECRIPTION BY PASSWORD = 'your master key password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
EXEC sp_change_users_login 'update_one', '##MS_SSISServerCleanupJobUser##','##MS_SSISServerCleanupJobLogin##'
5; Upgrade SSISDB to the right version
6; Verify all are good
EXEC catalog.check_schema_version @use32bitruntime = 1