Edit

Tutorial: Migrate TDE-enabled databases (preview) to Azure SQL in Azure Data Studio

  • Article

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

  1. Currently, only Azure SQL Managed Instance targets are supported.
  2. And Encrypted Backups are not 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:

  • Download and install Azure Data Studio.

  • 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:

  1. 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.

  2. Right-click the server connection and select Manage.

    Screenshot that shows a server connection and the Manage option in Azure Data Studio.

  3. In the server menu under General, select Azure SQL Migration.

    Screenshot that shows the Azure Data Studio server menu.

  4. In the Azure SQL Migration dashboard, select Migrate to Azure SQL to open the migration wizard.

    Screenshot that shows the Migrate to Azure SQL wizard.

  5. On the first page of the wizard, start a new session or resume a previously saved session.

Run database assessment

  1. In Step 1: Databases for assessment in the Migrate to Azure SQL wizard, select the databases you want to assess. Then, select Next.

    Screenshot that shows selecting a database for assessment.

  2. In Step 2: Assessment results, complete the following steps:

    1. In Choose your Azure SQL target, select Azure SQL Managed Instance.

      Screenshot that shows selecting the Azure SQL Managed Instance target.

    2. Select View/Select to view the assessment results.

      Screenshot that shows view/select assessment results.

    3. In the assessment results, select the database, and then review the assessment findings. In this example, you can see the AdventureWorksTDE database 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.

      Screenshot that shows assessment findings report.

    4. Choose Select to open the TDE migration configuration panel.

Configure TDE migration settings

  1. In the Encrypted database selected section, select Export my certificates and private key to the target.

    Screenshot that shows the TDE migration configuration.

    Important

    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 will use to backup the DEK certificates. Also, the current user should have administrator privileges on the computer where this network path exists.

  2. Enter the network path.

    Screenshot that shows the TDE migration configuration for a network share.

    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.

  3. 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.

    Screenshot that shows how to decline the TDE migration.

    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.

  4. If you want to proceed with the TDE certification migration, select Apply.

    Screenshot that shows how to apply the TDE migration configuration.

    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.

    Screenshot that shows how to edit the TDE migration configuration.

Configure migration settings

In Step 3: Azure SQL target in the Migrate to Azure SQL wizard, complete these steps for your target managed instance:

  1. Select your Azure account, Azure subscription, the Azure region or location, and the resource group that contains the managed instance.

    Screenshot that shows Azure account details.

  2. When you're ready, select Migrate certificates to start the TDE certificates migration.

Start and monitor the TDE certificate migration

  1. In Step 3: Migration Status, the Certificates Migration panel will open. The TDE certificates migration progress details are shown on the screen.

    Screenshot that shows how the TDE migration process starts.

  2. Once the TDE migration is completed (or if it has failures), the page displays the relevant updates.

    Screenshot that shows how the TDE migration process continues.

  3. In case you need to retry the migration, select Retry migration.

    Screenshot that shows how to retry the TDE migration.

  4. When you're ready, select Done to continue the migration wizard.

    Screenshot that shows how to complete the TDE migration.

  5. You can monitor the process for each TDE certificate by selecting Migrate certificates.

  6. Select Next to continue the migration wizard until you complete the database migration.

    Screenshot that shows how to continue 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.

Screenshot that shows the results returned by the TDE query provided in this section.

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

Related content