Migrating TDE Databases to Azure SQL Database Managed Instance – Part 2

In the previous post we talked about how to migrate a database protected with TDE to Azure SQL Database Managed Instance when the certificates and keys were on the server. Here we are going to look at the process involved if we are using the Azure Key Vault EKM provider in SQL Server.

Assumptions

Before we begin, I just want to make sure that we are starting from the same place. This post is working on the following assumptions.

  1. Azure Key Vault is provisioned and we have access to it.
  2. TDE is already in place for the source database.
  3. The Azure SQL Database Managed Instance has been provisioned and is ready for us to work with.

Scripts

All of the scripts used in this post can be downloaded from GitHub here.

As we discussed in part one, we have the option of a manual backup & restore or using Database Migration Service (DMS) to move the database from source to destination. However, when we are using Key Vault with the EKM provider for SQL Server, rather, we need to configure the Bring Your Own Key feature in the Managed Instance.

Once we have configured Managed Instance with the appropriate key then we can migrate the database.

Configuring Managed Instance

The first step in this process is to identify the key which we need to configure on our Managed Instance. Because we are using the EKM provider for Azure Key Vault we are not able to retrieve the data from SQL Server about which key is needed. So, we will need to review our documentation about the configuration of the system to understand which key was used. If you have not got this information then there is a useful post on SQL Server Central by Doug Lutz here which will help walk through the options to find it.

With the key name we can lookup the ID for the key in Azure Key Vault as we will need this when we configure Managed Instance. This can be obtained by using the following PowerShell script.

$getKeyVaultKeyParams = @{
    VaultName = "kv-migtesting"
    Name = "Adventureworks2019-TDEKV"
}
$tdeKey = Get-AzKeyVaultKey @getKeyVaultKeyParams

Once we have this information we need to configure Managed Instance so that it will use “Customer-Managed Key” rather than “Service-Managed Key”. This can be done using the following PowerShell script.

$setManagedInstanceByokParams = @{
    ResourceGroupName = "rg-migrations"
    InstanceName = "mi-migrations"
    Type = "AzureKeyVault"
    KeyId = $tdeKey.Id
    Force = $true
}
Set-AzSqlInstanceTransparentDataEncryptionProtector @setManagedInstanceByokParams

Now that we have configured the Managed Instance with the key used by our SQL Server database we can move it.

Migrating the Database

Again we will use an offline migration from SQL Server to Managed Instance using Backup & Restore.

Step 1 – Configure Azure Storage & Credentials

Before we can perform the database migration we need to configure Azure Blob Storage to act as a staging area for our backup file. Ideally, we should make sure that the storage account we create is in the same Azure region as the Managed Instance that we will be restoring the database to. We can create the storage account with PowerShell using the following script.

Create storage account for us to backup to.
 $newStorageAccountParams = @{
     ResourceGroupName = "rg-migrations" 
     Name = "stmigblogpost" 
     SkuName = "Standard_LRS"
     Location = 'UK South' 
     Kind = "StorageV2" 
     AccessTier = "Hot"
     MinimumTlsVersion = "TLS1_2"
     AllowBlobPublicAccess = $false
 }
 $storageAccount = New-AzStorageAccount @newStorageAccountParams
 Create a container in the storage account for the backup files.
 $newStorageAccountContainerParams = @{
     Name = "dbbackups"
     Context = $storageAccount.Context
 }
 $container = New-AzStorageContainer @newStorageAccountContainerParams

This has created the account and a container for us to place the backups into. Once this is in place we need a sas token which will be used to grant access to this container by the source and destination systems via credential objects. The following PowerShell will create and store the sas token in a variable for us.

$newSasTokenParams = @{
     Context = $storageAccount.Context
     Service = "Blob"
     ResourceType = "Container","Object"
     Permission = "racwdlup"
     StartTime = ((Get-Date).AddDays(-1))
     ExpiryTime = ((Get-Date).AddDays(7))
 }
 $sasToken = New-AzStorageAccountSASToken @newSasTokenParams

In this we are creating a token which will be valid as soon as we create it and for seven days after. this time span will vary depending on the time we expect to take for performing the migration activities.

Now we have all of the Azure storage elements in place and a sas token for access we can create credentials on the SQL Server and Managed Instance. These credentials will allow the database engine to backup to URL and restore from URL. DBATools has a couple of great commands which make this process a lot easier which are in the script below.

$newDbaCredentialParams = @{
     SqlInstance = "SQL-01"
     Name = $container.CloudBlobContainer.Uri.AbsoluteUri
     Identity = "Shared Access Signature"
     SecurePassword = (ConvertTo-SecureString -AsPlainText -Force ($sasToken.Replace("?","")))
 }
 $credential = New-DbaCredential @newDbaCredentialParams
 $copyCredentialParams = @{
     Source = "SQL-01"
     Destination = "mi-migrations.public.000000000000.database.windows.net,3342"
     DestinationSqlCredential = (Get-Credential) 
     Name = $credential.Name
 }
 Copy-DbaCredential @copyCredentialParams

Note: When the sas token is returned it will have a leading “?” which needs to be removed for the credential objects to work properly.

First of all we create the credential on the source system, then copy that to the destination. This way we know that the credential objects are the same and access is valid on both systems. Additionally, we am using the public endpoint for the Managed Instance here. If we have hybrid network connectivity setup for our workstation we would be able to use the private endpoint in this command.

Now we are all set for doing the backup and restore.

Step 2 – Migrate the Database

Again we will work with DBATools here to allow us to build on the scripts we used to setup the storage and credentials. This time we will first of all take the backup with Backup-DbaDatabase.

$backupDatabaseParams = @{
     SqlInstance = "sql-01"
     Database = "Adventureworks2019_TDEKV"
     AzureBaseUrl = $container.CloudBlobContainer.Uri.AbsoluteUri
     Type = "Full"
}
$backup = Backup-DbaDatabase @backupDatabaseParams

This will create the backup file in the storage container we created via the backup to URL process. We are also creating a variable and storing the backup object in this so that we can easily obtain properties needed for the restore.

Now that we have a backup in place we can restore it to our Managed Instance. This time we will use Restore-DbaDatabase from the DBATools module.

$restoreDatabaseParams = @{
     SqlInstance = "mi-migrations.public.000000000000.database.windows.net,3342"
     SqlCredential = (Get-Credential)
     DatabaseName = "Adventureworks2019_TDEKV"
     Path = $backup.BackupPath
}
Restore-DbaDatabase @restoreDatabaseParams

Once this command completes we will have migrated the database to the new Managed Instance. All that remains now is to migrate the supporting objects such as jobs, logins, etc. and redirect the applications and the migration is complete. More information on how to go about migrating these objects with DBATools can be found here.

Finally, we just need to clear up the credentials, backup files and storage account and then decommission the old on-premises SQL Server.

Summary

Migration of the TDE protected database from SQL Server to Managed Instance can be less complex than if we use a configuration which is wholly managed by SQL Server. However, it should be noted that there are some limitations with the BYOK configuration in Managed Instance. Primarily that there is only provision for one key to be configured at a time for the whole instance. This means that any existing databases will have their encryption keys altered to whichever is set at the instance level. Obviously this is not always going to be desirable and could result in a 1:1 mapping between database and instance which could prove costly.

One thought on “Migrating TDE Databases to Azure SQL Database Managed Instance – Part 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.