Migrating a Database from an On-Premises SQL Server to an Azure SQL Managed Instance

You can migrate a SQL database from an on-premises SQL server to an Azure SQL managed instance. To perform the migration, you use a restore procedure to restore the SQL database to the Azure SQL managed instance.

When you restore a database from an on-premises SQL Server to an Azure SQL managed instance, you need to use a staging SQL instance as an intermediate step. This is because an on-premises SQL database can have a combination of full, differential and log backups, and Azure SQL Managed Instance only allows the importing of a full backup. Therefore, the restore procedure first needs to restore the on-premises backups to a staging instance, and then the staged database is exported from the staging instance to a single full backup in .bak format.

The process involves the following steps:

Restoring one or more .bak files to a staging SQL Server instance on the access node.
Exporting the restored database from the staging instance to a single .bak file.
Importing the .bak file into the Azure SQL managed instance.

You must have a SQL Server instance installed on the access node being used for the intermediate restore step. This enables you to configure the Staging instance and Staging path on access node fields in the Restore options window.

Note

The access node instance (specified by the Staging instance box in the Restore options window) must be the same version or a more recent version of SQL than the source instance.

Procedure

If the source database is protected by Transparent Data Encryption (TDE), migrate the certificate from the on-premises SQL server to both the on-premises access node SQL server and the Azure SQL managed instance.
1. To migrate the certificate of a TDE-protected database to an on-premises access node SQL server, securely transfer the certificate (.cer) and private key (.pvk) files from the source server to the access node. Ensure you follow your organization's security protocols for transferring sensitive cryptographic materials.
  
  For information about migrating the certificate to the Azure SQL managed instance, see Migrate certificate of TDE protected database to Azure SQL Database Managed Instance in the Microsoft documentation.
2. Create the master key and the certificate on the access node in the same way as is done for the destination server of a regular TDE restore.
  
  For more information about creating the master key and creating the certificate, see the Microsoft documentation CREATE MASTER KEY and CREATE CERTIFICATE.
From the Command Center navigation pane, go to Protect > Databases.

The Overview page appears.
On the Instances tab, in the row for the on-premises SQL server instance, click the action button , and then click Restore.

The Backup content dialog box appears.
Select the content to restore, and then click Restore.

The Restore options dialog box appears.
Select Out of place.
From the Restore type list, select Cross instance restore.
From the Destination server list, select the database server that has SQL server instance.
From the Destination instance list, select the SQL instance to which you want to restore the data.
From the Access node list, select the access node.
From the Staging instance list, select the staging instance.
In the Staging path on access node box, enter the location where you want to stage the Azure BACPAC file.

Verify that you have sufficient space in the staging path for the restore operation.
To rename the database, or to specify a new path for the database files, under Data files you can change the names.

If you change the name of the database to a name of a database that already exists on the destination instance, the database will be overwritten.
Click SUBMIT.