I know you are here to understand the detailed procedure of migrating SSISDB but before getting into the steps to migrate the SSISDB let us understand how SSISDB came into existence and how SSIS packages were handled before.
How SSISDB Came into Existence
SSISDB was introduced with SQL Server 2012 as a significant improvement in managing and deploying SQL Server Integration Services (SSIS) packages. Prior to SSISDB, SSIS packages were managed differently, and the introduction of SSISDB addressed several limitations and challenges faced by developers and administrators.
Before SSISDB
Suppose you have a user database TESTDB and you want to move it from Instance A to Instance B. Let’s assume both instances are running on SQL Server 2019. How do you move the database from instance A to instance B?
Now, think of the above steps from the perspective of the SSISDB. Is it sufficient for you to follow backup and restore method for migrating SSISDB?
Let us follow the steps for SSISDB migration in this article
There are multiple ways to migrate the SSISDB this step guide Assumes that you have the SSISDB encryption key handy with you.
Step1: Verify SSISDB password
Every time SSIDB is created it asks for the user to put in an encryption password this password has to be carefully stored in a place. Verify you have the correct password for SSISDB encryption. You can use the following SQL script for verifying the password. If the password is correct, it returns command successfully executed.
Step2: Take a full backup of SSISDB SQL Database
The backup can be done through the GUI or the below code can be used.
BACKUP DATABASE [SSISDB] TO DISK = N’c:sqlSSISDB.bak’ WITH NOFORMAT, NOINIT, NAME = N’SSISDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
Step3: Verify whether integration services is installed in the new server.
The SSIS integration services should be running in the new instance if not install it as an addition feature in the existing SQL server installation center.
Step4: Restore the backup file in the new instance where you want the SSISDB to be migrated to
Restoration of the backup file can be done using the GUI or by using the below code
USE [master]
RESTORE DATABASE [SSISDB] FROM DISK = N’C:BackupSSISDB.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5
Step5: Enable CLR
If clr is not enabled in the new server and if it throws a error then use the following code
EXEC sp_configure ‘clr enabled’, 1
RECONFIGURE
Step6: Decrypt the master key by using the password
open master key decryption by password = N'<Password>’
Alter Master Key Add encryption by Service Master Key
Step7: Validation
Once the backup restore is done validate the existence of SSISDB in the new instance and also do a trial run of the packages.
In Summary, this article explored the steps for migrating an SSIS catalog SQL database and database to another instance. As discussed in this article, we require a few additional backup and restoration steps for the SSIS package database than a regular user database. You should note these things and plan the backups accordingly to avoid any last-minute failures.
Thanks for your time, keep learning…
Lochan R