Recently I needed to move a dev instance from a physical server to a virtual one and needed to do SQL upgrades. We decided it was time to spin up a new dev instance and that required moving SSISDB. I will be honest, I was not prepared for all the things. I also did not have a password for the encryption so I had to force things to work. Because this was a dev server, I wasn’t as worried about what I would lose, I just wanted it to work. Here is what I did.

I spun up my new instance and restored SSISDB, which was wrong. So I deleted it. I found that I had missed the SSIS install piece during the install process, so I ran that again and installed SSIS.

Next I configured CLR on the new server:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO

Since I didn’t have the password from the old server, I ran this on the old server:

Backup master key to file = 'D:\Backup\SSISDB_key' --Replace with the location you want to save it.
Encryption by password = 'StrongPassword' --Replace with your password

Then I created a new backup of the SSISDB on the old server.

Because I had restored my SSISDB when I shouldn’t have, I ran this powershell code on the new server to adjust all the little things I was going to miss:

# 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=localhost;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”, “StrongPassword”)  #REPLACE THE PASSWORD
$catalog.Create()

I restored the SSISDB to to the new server from the backup I took above. Then I restored the SSISDB_key that I created:

RESTORE MASTER KEY FROM FILE = 'D:\Install\SSISDB_key' --Change to the Location of your MasterKey Backup
DECRYPTION BY PASSWORD = 'StrongPassword' --Password used to encrypt. 
ENCRYPTION BY PASSWORD = 'StrongPassword' --New Password
FORCE

Then I opened and altered the master key:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword'--–‘Password used when creating SSISDB in Powershell Script’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Next, I verified my keys were working:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';

Finally, I synchronized logins and made sure my new version was compatible by watching the compatibility status (0 means all is good)

EXEC catalog.check_schema_version 0 --0 for 64, 1 for 32

It was a lot, but I was so thrilled when it all started working.

The song for this post is Dancing with My Eyes Closed – Performed by Wind Walkers