Migrate Azure SQL DBMI to VMs using Backup and Restore

07 May 2019 Bill Ramos Microsoft Azure

I recently needed to migrate an Azure SQL Database Managed Instance to an Azure VM running SQL Server. There were several reasons for this which included:

Machine Learning Services was not available for processing R language libraries close to the database.
Bulk loading CSV files were slower than expected because Managed Instances only supports the Full Recovery Model.
PolyBase was not available for data mining data files located in Azure Data Lake Gen2 storage.

The challenge that I faced was that when you create a database on a SQL Server Managed Instance, the database is encrypted with Transparent Data Encryption (TDE). It also uses a service-managed key that handles tasks like managing key rotations. Unfortunately, you can’t transfer the key to another server.

Here are the steps that I used to migrate a SQL Server Managed Instance to an Azure VM running SQL Server 2019 CTP 2.5.

Step 1

Create credential for Shared Access Signature. I used the PowerShell example code from the help topic SQL Server Backup to URL to create the Shared Access Signature and the following T-SQL statement.

1
2
3

CREATE CREDENTIAL [https://mystorageaccountname.blob.core.windows.net/mi-backups] WITH
IDENTITY='Shared Access Signature'
, SECRET='sv=2018-03-28&sr=c&si=sql-policy&sig=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

Step 2

Turn off TDE. I used T-SQL to execute the following code to completely turn off TDE for the database. Check out these help topics to better understand the encryption commands used below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Turn off TDE encryption for the database
USE [master]
GO
ALTER DATABASE [AnalyticsDB] SET ENCRYPTION OFF
GO

-- Encryption is turned off when encryption_state is 1.
-- This can take a little bit of time.
SELECT d.name, k.database_id, encryption_state, encryptor_type
FROM sys.dm_database_encryption_keys k
INNER JOIN sys.databases d ON k.database_id = d.database_id

-- To complete the removal of TDE for the database,
-- you need to drop the database encryption key.
USE AnalyticsDB
GO
DROP DATABASE ENCRYPTION KEY
GO

Step 3

Check your server version. SQL Server only allows you to restore a database on a server that is the same or newer than the SQL Server installed. There is a catch with Azure SQL Database Managed Instances. Mainly, the version reported is not aligned to the traditional SQL Server installations. Here is what gets reported when you use the SERVERPROPERTY() functions.

1
2
3
4
5

-- Now you can make a backup of the database for the managed instance
-- Check your version first.
SELECT ProductMajorVersion= CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)
, ProductMinorVersion=CAST(SERVERPROPERTY('ProductMinorVersion') AS INT )
, ProductBuild=CAST(SERVERPROPERTY('ProductBuild') AS INT)

Here were the results as of May 6 2019:

ProductMajorVersion ProductMinorVersion ProductBuild
------------------- ------------------- ------------
12                  0                   1300

It looks like the managed instance is using a version of SQL Server 2014. Don’t be fooled.

Step 4

Backup the database to URL. Here is how I was able to back up the database to a URL using the COPY_ONLY option. Not using this option caused backups to fail.

1
2
3
4
5
6
7
8
9
10

-- Since the backup files are small, I did another backup with a single URL
BACKUP DATABASE AnalyticsDB
TO URL = 'https://accountname.blob.core.windows.net/mi-backups/AnalysisDB.bak'
WITH COPY_ONLY
, COMPRESSION
, STATS = 1
, BUFFERCOUNT = 8
, MAXTRANSFERSIZE = 4194304
, BLOCKSIZE = 65536;
GO

Step 5

Check for the real Managed Instance version. This is how I used the RESTORE HEADERONLY command to check the version recorded for the backup file.

1
2
3

RESTORE HEADERONLY
FROM URL='https://accountnameA.blob.core.windows.net/mi-backups/AnalysisDB.bak'
GO

Here are the results from the RESTORE HEADERONLY command:

SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild
-------------------- -------------------- --------------------
15                   0                    1300

Version 15 is associated with SQL Server 2019!

Fortunately, SQL Server 2019 CTP2.5 has the following version. This means that restoring the database from the managed instance to SQL Server 2019 CTP 2.5.

ProductMajorVersion ProductMinorVersion ProductBuild
------------------- ------------------- ------------
15                  0                   1500

Step 6

Get a version of SQL Server 2019 CTP 2.5. In order to restore the backup, I created a VM using Microsoft’s image with SQL Server 2019 CTP 2.5 on Windows Server 2016.

Step 7

Restore the database. Now it was a simple matter of restoring the database on my new SQL Server 2019 instance. I used the same CREATE CREDENTIAL command used on the managed instance.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

USE [master]
GO
CREATE CREDENTIAL [https://mystorageaccountname.blob.core.windows.net/mi-backups] WITH
IDENTITY='Shared Access Signature'
, SECRET='sv=2018-03-28&sr=c&si=sql-policy&sig=xxxxxxxxxxxxxxxxxxxxxx'
GO
RESTORE DATABASE [AnalyticsDB] FROM
URL = N'https://accountname.blob.core.windows.net/mi-backups/AnalysisDB.bak'
WITH FILE = 1
, MOVE N'data_0' TO N'F:\Data\AnalyticsDB2019.mdf'
, MOVE N'log' TO N'F:\Log\AnalyticsDB.ldf'
, MOVE N'XTP' TO N'F:\Data\79956cdb-7c38-434e-8fae-a9105cdf9b07.xtp'
, NOUNLOAD
, STATS = 1
GO

Using this pattern, you can migrate your Managed Instance databases to the latest version of SQL Server without having to resort to scripting the database and using BCP to export and import your data.

Blog: Migrate Azure SQL Database Manage Instances to Azure VMs using Backup and Restore