Study guide
4.3 Migrating Between Azure SQL Services and Troubleshooting
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
4.3 Migrating Between Azure SQL Services and Troubleshooting
Key Takeaways Explain This Topic AI
Copying a database within Azure SQL Database uses CREATE DATABASE ... AS COPY OF on the same server; cross-server copies require backup-restore, geo-restore, or copy to a managed instance via backup to URL
Moving a database to SQL Managed Instance from Azure SQL Database typically uses export to a BACPAC via SqlPackage or the portal, then import on the MI, because direct DMS does not support this direction
The most common migration failures are orphaned database users (server logins that did not migrate), collation mismatches between the source server and target MI, and unsupported features silently dropped during BACPAC export
DMS agent failures usually trace to service account permissions on the source, blocked ports (445 for SMB, 5022 and 56000-65535 for MI), or an Integration Runtime that cannot reach the source
Troubleshooting starts with the DMA assessment report and the DMS/MI link monitor logs; the cutover blocker most often missed is the missing CHECKSUM option on the source backup
Last updated: August 2026
Migration Paths Between Azure SQL Services
The DP-300 exam expects you to know the supported directions and the right tool for each. The paths are not symmetric - SQL Server-to-Azure tools often do not work between Azure services, and the tool that works in one direction may not work in the reverse.
- From To Supported path
- Azure SQL Database (same server) Azure SQL Database (same server) CREATE DATABASE ... AS COPY OF
Azure SQL Database (server A) Azure SQL Database (server B) Backup-restore, geo-restore, or copy to MI then back
Azure SQL Database SQL Managed Instance Export BACPAC (SqlPackage or portal), import on MI; or backup to URL and restore
SQL Managed Instance Azure SQL Database Export BACPAC, import on Azure SQL Database; direct restore is not supported
Azure SQL Database / MI SQL Server on Azure VM Backup to URL, restore on VM; or BACPAC export/import
SQL Managed Instance (instance A) SQL Managed Instance (instance B) Backup to URL ( COPY_ONLY ), restore on target instance; or geo-restore for cross-region
Azure SQL Database Copy Operations
The simplest in-Cloud move is a database copy on the same logical server. The T-SQL syntax is: CREATE DATABASE [SalesDB_Copy] AS COPY OF [server_A].[SalesDB];
The copy is a transactionally consistent snapshot of the source at the moment the copy starts, created on the same server (cross-server copies require additional setup). The copy operation is asynchronous; query sys.dm_database_copies to monitor progress. The destination database inherits the service objective and pricing model of the source unless you override it. Use cases: deploying a production database to a test environment, creating a point-in-time snapshot for analysis, or moving a tenant database between logical servers within the same subscription.
For cross-server and cross-region scenarios the paths multiply. Geo-restore restores from automatic geo-replicated backups to any region, useful for disaster recovery or for moving a database to a new region with up to 1 hour of RPO (the point in time is limited by the backup retention). Long-term retention backups can be restored to a new database on the same logical server. Active geo-replication lets you fail over to a readable secondary in another region - this is a DR and read-scale feature, but failing over to a secondary is also a valid way to move a database to a new region with minimal downtime.
Copying and Moving Databases on Managed Instance
SQL Managed Instance does not support CREATE DATABASE ... AS COPY OF the way Azure SQL Database does. The supported methods for moving a database between managed instances are:
Backup to URL with COPY_ONLY : take a BACKUP DATABASE ... TO URL WITH COPY_ONLY, CHECKSUM to an Azure Storage container, then RESTORE DATABASE ... FROM URL on the target instance. This is the standard cross-instance copy. The COPY_ONLY flag avoids disrupting the managed instance's automated backup chain.
Geo-restore : every managed instance database has automatic geo-replicated backups; restore to a different managed instance in a paired region for disaster recovery or migration.
Failover groups : at the instance level, a failover group replicates all databases in the group to a secondary instance in another region, with a read-write listener endpoint that applications connect to. Failing over the group is the zero-data-loss DR and region-move path.
Log replay service (LRS) : for migrations from SQL Server to MI that need more control than the link provides, LRS continuously replays log backups taken on the source and restored to the MI target, completing with a cutover when the source is taken offline.
A subtle exam point: you cannot restore a SQL Server backup directly to Azure SQL Database (single database) - the single-database PaaS does not accept file-level restores. You must use BACPAC import (schema + data in a portable package) via SqlPackage or the portal. The reverse - restoring an Azure SQL Database automated backup to a SQL Server on-premises - is also not supported; export-to-BACPAC is the path.
Troubleshooting Common Migration Failures
Failures cluster into five categories, and the exam tests the diagnostic path for each.
1. Compatibility and Unsupported Features
A migration to Azure SQL Database fails or silently drops features that exist on SQL Server but not on the PaaS target. The DMA assessment report should surface these before migration, but BACPAC export/import can still drop unsupported objects (for example, SQL Agent job metadata stored in msdb , which is not part of a user database and therefore not exported). Remediation: run DMA before migration, classify each blocker, and decide between redesign (replace SQL Agent with Elastic Jobs), retarget (move to Managed Instance), or accept the gap.
2. Logins and Users (Orphaned Users)
The most common post-migration issue is an orphaned database user : a user in the database maps to a server login that did not migrate. After restoring or importing the database on the new target, the user exists but cannot connect because its SID does not match a login on the target server. Two remediation paths:
Create the missing logins on the target server and run ALTER USER [UserName] WITH LOGIN = [LoginName]; to relink. This is the classic fix.
Use contained database users (created with CREATE USER ... WITH PASSWORD = ... ), which live entirely inside the database and have no server-login dependency. Contained users are the recommended pattern for Azure SQL Database because they travel with the database across servers and regions.
Server-level principals on SQL Managed Instance are stored in the master database of the instance; the MI link and DMS do not migrate master logins automatically, so a post-cutover login sync step is required. Run sp_helpuser or query sys.database_principals joined to sys.server_principals to find broken mappings.
3. Collation Mismatches
SQL Managed Instance has a server (instance) collation set at provisioning time and cannot be changed later; the default is SQL_Latin1_General_CP1_CI_AS . Azure SQL Database logical servers use SQL_Latin1_General_CP1_CI_AS as the server collation, but each database can have its own collation. When a database with a different collation is restored or imported to a target with a different server collation, tempdb uses the server collation, and queries that compare user-database strings to tempdb results (for example, joins to temp tables) can throw collation conflict errors. Remediation: specify the target collation at MI provisioning time when possible, or add COLLATE clauses to queries that cross collation boundaries. The trap is that this error appears only at runtime, not during the migration itself.