Seamless SSISDB Migration: A Detailed Walkthrough

Blogs

Understanding Recovery Models: How They Influence SQL Server Performance
August 24, 2024
Cross Database Querying in Postgres SQL
August 25, 2024

Seamless SSISDB Migration: A Detailed Walkthrough

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

  1. File System Deployment:
    • Package Storage: SSIS packages were often stored in the file system. This required manual management and lacked centralized storage.
    • Deployment and Execution: To execute packages, users had to use SQL Server Management Studio (SSMS), SQL Server Agent, or command-line tools. There was no built-in centralized repository for managing these packages.
  2. MSDB Deployment:
    • Package Storage: Another method involved storing packages in the msdb database. This provided a central location but lacked the advanced management and monitoring features.
    • Deployment and Execution: Packages stored in msdb could be executed using SQL Server Agent, but again, there were limited options for managing execution history, logging, and security.
  3. Manual Management:
    • Deployment: Manual methods for deploying and managing packages included copying files or scripts between environments, leading to potential errors and inefficiencies.
    • Monitoring and Logging: Monitoring and logging were less sophisticated, relying on external logging mechanisms or basic SQL Server Agent job logging.

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?

  • Take full database backup on Instance A
  • Copy the backup file onto the instance B server
  • Restore backup on Instance B SQL Server

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.

  • During the package execution if there are any assembly error, execute the below code

  • If there is a error related to DB owner( DB owner of both master and SSISDB is different) then use the following code.

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

Leave a Reply

Your email address will not be published. Required fields are marked *