Tutorial: Migrate TDE-enabled databases (preview) to Azure SQL in Azure Data Studio
For securing a SQL Server database, you can take precautions like designing a secure system, encrypting confidential assets, and building a firewall. However, physical theft of media like drives or tapes can still compromise the data.
TDE provides a solution to this problem, with real-time I/O encryption/decryption of data at rest (data and log files) by using a symmetric database encryption key (DEK) secured by a certificate. For more information about migrating TDE certificates manually, see Move a TDE Protected Database to Another SQL Server.
When you migrate a TDE-protected database, the certificate (asymmetric key) used to open the database encryption key (DEK) must also be moved along with the source database. Therefore, you need to recreate the server certificate in the master database of the target SQL Server for that instance to access the database files.
You can use the Azure SQL Migration extension for Azure Data Studio to help you migrate TDE-enabled databases (preview) from an on-premises instance of SQL Server to Azure SQL.
The TDE-enabled database migration process automates manual tasks such as backing up the database certificate keys (DEK), copying the certificate files from the on-premises SQL Server to the Azure SQL target, and then reconfiguring TDE for the target database again.
Important
Currently, only Azure SQL Managed Instance targets are supported. Encrypted Backups aren't supported.
In this tutorial, you learn how to migrate the example AdventureWorksTDE encrypted database from an on-premises instance of SQL Server to an Azure SQL managed instance.
- Open the Migrate to Azure SQL wizard in Azure Data Studio
- Run an assessment of your source SQL Server databases
- Configure your TDE certificates migration
- Connect to your Azure SQL target
- Start your TDE certificate migration and monitor progress to completion
Prerequisites
Before you begin the tutorial:
Install the Azure SQL Migration extension from Azure Data Studio Marketplace.
Run Azure Data Studio as Administrator.
Have an Azure account that is assigned to one of the following built-in roles:
- Contributor for the target managed instance (and Storage Account to upload your backups of the TDE certificate files from SMB network share).
- Reader role for the Azure Resource Groups containing the target managed instance or the Azure storage account.
- Owner or Contributor role for the Azure subscription (required if creating a new DMS service).
- As an alternative to using the above built-in roles, you can assign a custom role. For more information, see Custom roles: Online SQL Server to SQL Managed Instance migrations using ADS.
Create a target instance of Azure SQL Managed Instance.
Ensure that the login that you use to connect to the SQL Server source is a member of the sysadmin server role.
The machine in which Azure Data Studio runs the TDE-enabled database migration should have connectivity to both sources and target SQL servers.
Open the Migrate to Azure SQL wizard in Azure Data Studio
To open the Migrate to Azure SQL wizard:
In Azure Data Studio, go to Connections. Connect to your on-premises instance of SQL Server. You also can connect to SQL Server on an Azure virtual machine.
Right-click the server connection and select Manage.
In the server menu under General, select Azure SQL Migration.
In the Azure SQL Migration dashboard, select Migrate to Azure SQL to open the migration wizard.
On the first page of the wizard, start a new session or resume a previously saved session.
Run database assessment
In Step 1: Databases for assessment in the Migrate to Azure SQL wizard, select the databases you want to assess. Then, select Next.
In Step 2: Assessment results, complete the following steps:
In Choose your Azure SQL target, select Azure SQL Managed Instance.
Select View/Select to view the assessment results.
In the assessment results, select the database, and then review the assessment findings. In this example, you can see the
AdventureWorksTDEdatabase is protected with transparent data encryption (TDE). The assessment is recommending to migrate the TDE certificate before migrating the source database to the managed instance target.
Choose Select to open the TDE migration configuration panel.
Configure TDE migration settings
In the Encrypted database selected section, select Export my certificates and private key to the target.
The Info box section describes the required permissions to export the DEK certificates.
You must ensure the SQL Server service account has write access to network share path you use to backup the DEK certificates. Also, the current user should have administrator privileges on the computer where this network path exists.
Enter the network path.
Then check I give consent to use my credentials for accessing the certificates. With this action, you're allowing the database migration wizard to back up your DEK certificate into the network share.
If you don't want the migration wizard, help you migrate TDE-enabled databases. Select I don't want Azure Data Studio to export the certificates. to skip this step.
Important
You must migrate the certificates before proceeding with the migration otherwise the migration will fail. For more information about migrating TDE certificates manually, see Move a TDE Protected Database to Another SQL Server.
If you want to proceed with the TDE certification migration, select Apply.
The TDE migration configuration panel will close, but you can select Edit to modify your network share configuration at any time. Select Next to continue the migration process.
Configure migration settings
In Step 3: Azure SQL target in the Migrate to Azure SQL wizard, complete these steps for your target managed instance:
Select your Azure account, Azure subscription, the Azure region or location, and the resource group that contains the managed instance.
When you're ready, select Migrate certificates to start the TDE certificates migration.
Start and monitor the TDE certificate migration
In Step 3: Migration Status, the Certificates Migration panel will open. The TDE certificates migration progress details are shown on the screen.
Once the TDE migration is completed (or if it has failures), the page displays the relevant updates.
In case you need to retry the migration, select Retry migration.
When you're ready, select Done to continue the migration wizard.
You can monitor the process for each TDE certificate by selecting Migrate certificates.
Select Next to continue the migration wizard until you complete the database migration.
Check the following step-by-step tutorials for more information about migrating databases online or offline to Azure SQL Managed Instance targets:
Post-migration steps
Your target managed instance should now have the databases, and their respective certificates migrated. To verify the current status of the recently migrated database, copy and paste the following example into a new query window on Azure Data Studio while connected to your managed instance target. Then, select Run.
USE master;
GO
SELECT db_name(database_id),
key_algorithm,
encryption_state_desc,
encryption_scan_state_desc,
percent_complete
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID('Your database name');
GO
The query returns the information about the database, the encryption status and the pending percent complete. In this case, it's zero because the TDE certificate has been already completed.
For more information about encryption with SQL Server, see Transparent data encryption (TDE).
Limitations
The following table describes the current status of the TDE-enabled database migrations support by Azure SQL target:
| Target | Support | Status |
|---|---|---|
| Azure SQL Database | No | |
| Azure SQL Managed Instance | Yes | Preview |
| SQL Server on Azure VM | No |