Packvane

Migrating the SSISDB Catalog to SQL Managed Instance

The SSISDB catalog is the part of an SSIS estate people forget to plan for. The packages are in source control, or at least on a file share. The catalog is where the deployed projects, the environments, the parameter values and every execution you have ever run actually live — and it is a database with a database master key, which is what makes moving it more interesting than a backup and a restore.

There are two routes onto Azure SQL Managed Instance. They are not interchangeable.

Route 1: redeploy the projects

Create the catalog on the Managed Instance, then deploy each .ispac again.

You keep: the projects, cleanly, at whatever version is in source control.

You lose: execution history, environments and environment references, catalog-level parameter values, and any permissions granted inside the catalog.

This is the right route when the estate is small, when the .ispac files are reliably rebuildable from source, or when the execution history is not used by anyone. It is also the only honest route when nobody can tell you which deployed project version matches which branch — a redeploy from source at least gives you a known state.

Budget for rebuilding environments. In most estates the environment variables are the real configuration of the system and nobody has written them down anywhere else. Script them out of the source catalog first:

SELECT e.name AS environment, v.name AS variable, v.type, v.sensitive, v.value
FROM catalog.environments e
JOIN catalog.environment_variables v ON v.environment_id = e.environment_id
ORDER BY e.name, v.name;

Sensitive values come back NULL. Those are the ones you will be chasing for the rest of the week, so start the list early.

Route 2: restore the SSISDB backup

Back up SSISDB from the source instance and restore it onto the Managed Instance.

You keep: everything — projects, environments, references, parameter values, execution history, and the operational reports your team already reads.

You pay for it with the master key.

The master key step

SSISDB has a database master key. On the source instance that key is encrypted by the instance's service master key. On the target it is not, because it is a different instance — so after the restore, the catalog is there and nothing can decrypt a sensitive parameter.

The fix is to open the key with the password that was set when the catalog was created, and re-encrypt it against the new instance:

USE SSISDB;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<the catalog password>';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Which means: find the catalog password before you plan the migration, not during it. If the person who created the catalog in 2015 has left and the password is not in the password manager, route 2 is closed and you are on route 1 whether you like it or not. We ask for this on day one of every SSIS migration for exactly this reason.

After either route

Four things are not in the catalog and do not travel with it:

  • The SSIS Server Maintenance Job. The catalog's cleanup job is a SQL Agent job, not a catalog object. Recreate it on the target, or the operations and version tables grow without limit — a large, busy catalog can reach uncomfortable sizes surprisingly fast.
  • SQL Agent jobs. Every job that had an SSIS step needs recreating, pointed at the new catalog. This is also the natural moment to delete the ones that have been failing silently for two years.
  • Logins and catalog permissions. ssis_admin membership, folder permissions, and the proxy accounts used by job steps.
  • Retention settings. catalog.catalog_properties carries the retention window and the logging level. Check them rather than assuming the defaults moved with you.

Where the packages run

The catalog on Managed Instance is only half of it. The packages themselves execute on an Azure-SSIS integration runtime, which you provision in Azure Data Factory and point at the Managed Instance catalog. Two consequences worth knowing before you cost the project:

  • Any package that reads an on-premises source needs a self-hosted integration runtime configured as a proxy, or a network path from the Azure-SSIS IR into your network.
  • Any package that depends on a third-party component or a driver — a commercial connector, the Access Database Engine for an Excel source, an ODBC driver — needs that component installed on the IR nodes through a custom setup. Plan this before the cutover; discovering it during one is a bad evening.

What to do first

Run the environment query above and find the catalog master key password. Those two answers decide which route you are on, and they take an afternoon rather than a sprint.

If the answers come back badly, or the estate is larger than anyone wants to inventory by hand, we will do the inventory and tell you what the migration actually involves.