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;
GOSince 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 passwordThen 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
FORCEThen 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 KEYNext, 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 32It 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
Andrea Allred presents RoyalSQL 