Migrate SQL Server with TDE to Azure SQL Managed Instance Using DMS Online
You are designing a database migration from on-premises SQL Server 2019 to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local certificate store. The target is a General Purpose SQL Managed Instance. Which migration method should you use to minimize downtime and preserve TDE?
Quick Answer
The correct choice is to use Azure Database Migration Service (DMS) with online mode when migrating a SQL Server database with Transparent Data Encryption (TDE) to Azure SQL Managed Instance with minimal downtime. DMS online mode leverages transactional replication to continuously capture and apply changes from the source to the target, allowing you to switch over with only a brief interruption. It also handles TDE preservation by automatically transferring the TDE certificate and key from the on-premises certificate store to the managed instance, so the database remains encrypted throughout the migration. On the DP-300 exam, this scenario tests your ability to select the right migration method for encrypted databases under uptime constraints—a common trap is choosing an offline method like the Azure SQL Migration extension or using a backup/restore approach, which would require the database to be decrypted or cause significant downtime. Remember: TDE certificates are not automatically migrated in a standard backup/restore, but DMS online handles them for you. A helpful memory tip is “Online DMS keeps TDE and downtime low.”
⚠ Common exam trap
Many exam-takers assume transaction log shipping (Option C) is viable for Azure SQL Managed Instance, but it is not supported; DMS online mode is the correct minimal-downtime method that also handles TDE preservation.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use Azure Database Migration Service (DMS) with online mode.
Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations by continuously replicating changes from the source SQL Server to the target Azure SQL Managed Instance using the transactional replication technology. DMS also preserves Transparent Data Encryption (TDE) by migrating the TDE certificate and key to the managed instance, ensuring the database remains encrypted throughout the process.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use the SQL Server Import/Export Wizard to copy data.
Why it's wrong here
The wizard is a one-time copy, not suitable for minimal downtime.
- ✗
Export the database as a BACPAC file and import it into the managed instance.
Why it's wrong here
BACPAC does not preserve TDE settings.
- ✗
Use transaction log shipping from the source to the target.
Why it's wrong here
Transaction log shipping is not supported for TDE-enabled databases to SQL Managed Instance.
- ✓
Use Azure Database Migration Service (DMS) with online mode.
Why this is correct
DMS online mode supports TDE-enabled databases and minimizes downtime.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
Key term
Azure SQL Managed Instance
Azure SQL Managed Instance is a fully managed cloud database service that gives you nearly all the features of Microsoft SQL Server on your own server, without you having to manage the hardware or operating system.
Key term
Transparent Data Encryption
Transparent Data Encryption is a security feature that automatically encrypts data written to a database and decrypts it when read, without requiring any changes to the application.
About these practice questions
Courseiva writes every DP-300 question from scratch — 906 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?
medium- A.Use Azure Data Studio to import the certificate directly from the local machine store during migration.
- B.Disable TDE on the source database, migrate the backup, then enable TDE on the target.
- ✓ C.Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.
- D.Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.
Why C: TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.