· Uwe Janke
Commands / Invoke-sqmSsisCatalogMigration

Invoke-sqmSsisCatalogMigration

SSIS MigrationsqmSQLTool v1.9.132+ · Migrate⚠ sysadmin on both instances  ✓ source stays untouched
Moving an SSIS catalog is not a database move. SSISDB carries a database master key, and every sensitive value in it - connection manager passwords, environment variables marked sensitive, project parameters - is encrypted with that key. A plain backup/restore lands a database on the new server whose master key the new server cannot open, and SSIS then fails at execution time, not at restore time. On top of that, the restore turns TRUSTWORTHY off, sets the owner to whoever ran the restore, and leaves the catalog at the schema version of the old server.

This command runs the whole documented sequence once and reports every step: assessment of both sides, master key and database backup, catalog creation on the destination, restore, post-restore fix-up, and the schema comparison. Nothing on the source is changed at any point - the source catalog stays online and usable, and the only source-side operations are reads plus two backups.

The catalog is created on the destination before the restore, deliberately: creating it installs the CLR assemblies and the server-level SSIS objects that live outside SSISDB (sp_ssis_startup in master, the SSIS Server Maintenance Job in the Agent), and a database restore does not bring those along.

Every hit on the master key is verified by state, not by return code. After re-encrypting, is_master_key_encrypted_by_server is read back; statements that ran without error but left the flag at 0 are reported as a failure, because SSIS would then ask for the password on every access.

Parameters

ParameterTypeRequiredDefaultNotes
-SourceSqlInstancestringRequired—Instance holding the catalog to migrate.
-SourceSqlCredentialPSCredentialOptional—Credential for the source connection.
-DestinationSqlInstancestringRequired—Instance that receives the catalog. Must be the same or a higher SQL Server major version - a backup cannot be restored downlevel, and such a run is refused.
-DestinationSqlCredentialPSCredentialOptional—Credential for the destination connection.
-CatalogPasswordSecureStringOptional—The SSISDB master key password. Required for everything except -AssessOnly; without it the encrypted content of the catalog cannot be carried over.
-KeyFilePasswordSecureStringOptional-CatalogPasswordPassword protecting the exported master key file. Set it when the key backup is handed over separately from the catalog password.
-SharedPathstringOptionalsource default backup dirDirectory for the SSISDB backup and the key file, reachable by both instances. Both files are written by the respective SQL Server service account, so that account needs write access.
-DestinationDataPathstringOptionaldestination defaultTarget directory for the SSISDB data file.
-DestinationLogPathstringOptionaldestination defaultTarget directory for the SSISDB log file.
-AssessOnlyswitchSwitch$falseAssess both sides and report what would happen, including blockers. Changes nothing anywhere.
-UseKeyBackupRestoreswitchSwitch$falseRe-key with RESTORE MASTER KEY FROM FILE instead of OPEN + ADD ENCRYPTION BY SERVICE MASTER KEY. Only needed when the restored key cannot be opened with -CatalogPassword. Restoring over an existing key requires FORCE, so this additionally requires -Force.
-UpgradeCatalogswitchSwitch$falseRun the schema upgrade when the restored catalog is older than the destination server. Without it, the need is reported but nothing is run.
-UpgradeWizardPathstringOptionalauto-detectedPath to ISDBUpgradeWizard.exe. Without it, the highest-numbered copy under <ProgramFiles>\Microsoft SQL Server\<nnn>\DTS\Binn\ on the machine running the command is used.
-UpgradeWizardArgumentstring[]Optional-S <destination> -qArgument list for the wizard. Override it when your build expects a different syntax - the result is verified by re-reading the schema version, so a wrong argument list shows up as "upgrade did not take effect".
-SkipAgentJobScanswitchSwitch$falseSkip the scan for Agent jobs that start packages from the catalog. That scan does not touch the migration; it lists the jobs on the source that will have to be repointed.
-ForceswitchSwitch$falseAllow an existing destination SSISDB to be overwritten, and allow the FORCE variant of RESTORE MASTER KEY.
-OutputPathstringOptional<config>\SsisCatalogMigrationDirectory for the HTML report.
-NoOpen / -NoReportswitchSwitch$falseDo not open the report / do not write one at all.
-EnableExceptionswitchSwitch$falseThrow immediately instead of returning a result object with Status 'Failed'.
Supports -WhatIf and -Confirm (ConfirmImpact High). Every change on either instance goes through ShouldProcess, so a -WhatIf run prints the complete ordered list of changes it would make and performs none of them.

Execution Flow

START Assess source and destination Folders, projects, packages, environments, schema version, master key, TRUSTWORTHY Owner, CLR, SQL version, maintenance job, sp_ssis_startup Master key opens with -CatalogPassword? NO Blocked - nothing is changed Checked before the first write YES -AssessOnly or blockers found? YES Report the plan and stop Every blocker with its reason NO SOURCE: back up the master key, then back up SSISDB BACKUP MASTER KEY ... ENCRYPTION BY PASSWORD · full backup, read-only for the source DESTINATION: enable CLR, create the catalog Brings the CLR assemblies, sp_ssis_startup and the maintenance job - a restore does not Its schema version is recorded here as the reference for the upgrade check Restore SSISDB over the fresh catalog Via Invoke-sqmRestoreDatabase: single user, kill foreign sessions, retry TRUSTWORTHY ON, owner back to sa Both are lost in every restore - SSISDB needs them for its CLR calls Re-encrypt the master key for this server OPEN MASTER KEY ... / ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY On failure: DROP the stale old encryption first, then add again is_master_key_encrypted _by_server = 1 now? NO Reported as a failure Ran clean but had no effect YES EXEC catalog.startup, re-map orphaned SSISDB users Clears operations left "running" when the old instance went away Restored schema older than this server's? NO No upgrade needed Versions already match YES -UpgradeCatalog: run ISDBUpgradeWizard.exe, then re-read the version Without the switch: reported only, with the manual SSMS step named A wizard run that changed nothing is a failure, not a success DONE

Examples

The planning run: what is in the catalog, what would block the move
Invoke-sqmSsisCatalogMigration -SourceSqlInstance "SQLOLD" -DestinationSqlInstance "SQLNEW" -AssessOnly
Show every change in order, make none of them
$pw = Read-Host "SSISDB master key password" -AsSecureString
Invoke-sqmSsisCatalogMigration -SourceSqlInstance "SQLOLD" -DestinationSqlInstance "SQLNEW" `
    -CatalogPassword $pw -SharedPath "\\fileserver\sqlmove" -WhatIf
The migration itself, including the schema upgrade on the newer server
Invoke-sqmSsisCatalogMigration -SourceSqlInstance "SQLOLD" -DestinationSqlInstance "SQLNEW" `
    -CatalogPassword $pw -SharedPath "\\fileserver\sqlmove" -UpgradeCatalog
Which Agent jobs still point at the old server afterwards?
$r = Invoke-sqmSsisCatalogMigration -SourceSqlInstance "SQLOLD" -DestinationSqlInstance "SQLNEW" -AssessOnly
$r.AgentJobsToRepoint | Format-Table JobName, StepName, Subsystem, LineText
Read the step log of a finished run
$r = Invoke-sqmSsisCatalogMigration -SourceSqlInstance "SQLOLD" -DestinationSqlInstance "SQLNEW" `
    -CatalogPassword $pw -SharedPath "\\fileserver\sqlmove"
$r.Status
$r.Steps | Format-Table Timestamp, Step, Status, Detail -AutoSize
Destination already has a catalog that has to give way
Invoke-sqmSsisCatalogMigration -SourceSqlInstance "SQLOLD" -DestinationSqlInstance "SQLNEW" `
    -CatalogPassword $pw -SharedPath "\\fileserver\sqlmove" -Force

Notes

What this does not do. It does not touch the source catalog, it does not repoint SQL Agent jobs (it lists them), and it does not move the SSIS service configuration or file system packages - only the catalog. Packages stored in the file system or in MSDB are a different migration.

RESTORE MASTER KEY alone is not enough. Measured on a real instance: after RESTORE MASTER KEY FROM FILE the key is protected by its password only - is_master_key_encrypted_by_server stays 0 - so the service master key encryption has to be added afterwards. Restoring over an existing key additionally requires FORCE, which discards whatever the old key still protects; that is why -UseKeyBackupRestore refuses to run without -Force instead of quietly forcing.

The upgrade wizard is a Windows tool, not T-SQL. It has to run on a machine that can reach the destination instance. If it is not found, the required manual step (SSMS → Integration Services Catalogs → SSISDB → Upgrade...) is named in the result instead of failing silently.

← Back
Command Index
Related
Invoke-sqmSsisConfiguration
Related
Invoke-sqmRestoreDatabase