4.3 Migrating Between Azure SQL Services and Troubleshooting

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.

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.

4. Connectivity and Network

Migration failures that surface as 'cannot connect' usually trace to network configuration. For DMS migrations to Managed Instance, the DMS worker must reach both the source and the MI virtual network. Common culprits:

• The DMS subnet is not peered with the MI subnet, or the network security group blocks the required ports (1433 to source, 5022 and 56000-65535 to MI).
• The self-hosted Integration Runtime for the ADS migration wizard is installed on a machine that cannot reach the source, or the machine's firewall blocks outbound 443 to Azure.
• The MI public endpoint is disabled and the source is on-premises without a VPN/ExpressRoute, so the source cannot initiate a connection to the MI.
• Private endpoints on Azure SQL Database block DMS unless the DMS subnet is approved on the private endpoint's network policy.

Diagnostic order: verify NSG rules and route tables first, then test connectivity from the DMS worker machine to the source and target on the required ports (Test-NetConnection or telnet), then check that the Integration Runtime status is green in the Azure Data Studio / Azure Data Factory management view.

5. DMS Agent and Backup Failures

DMS agent failures have a small set of root causes:

• Service account permissions: the DMS worker connects to the source SQL Server with a login that must have VIEW SERVER STATE and db_owner on every database being migrated. If the login lacks permissions, the assessment runs but the migration activity fails partway through.
• Backup file share access: DMS reads backups from an SMB share or an Azure Storage container. Misconfigured SAS token expiry, missing READ permission on the container, or a network path that requires different credentials are common. The MI online migration additionally requires CHECKSUM enabled on the backup and a MAXTRANSFERSIZE sized to the database - missing CHECKSUM is the failure the exam most often names.
• Integration Runtime version: an outdated IR can fail to register with the cloud service; upgrade the IR to the latest version and restart the service.
• Source in simple recovery model: online migrations require the source to be in full recovery model so the transaction log can be captured. A source in simple recovery cannot stream deltas and the online migration fails during setup.

Migration Validation Checklist

After any migration between Azure SQL services, run this checklist before declaring success:

1. Row counts on all user tables match within the expected delta (for online migrations, after the final cutover).
2. Critical queries execute with comparable elapsed time (use Query Store to compare).
3. Logins and users: every application account can connect; no orphaned users in sys.database_principals.
4. Collation: run a sample query that joins to tempdb to confirm no collation conflict errors.
5. SQL Agent jobs (MI target): jobs exist and their owners are valid logins; schedules are enabled.
6. Linked servers and credentials: re-create linked servers on MI; Azure SQL Database does not support them so any dependency must be redesigned.
7. Indexes and statistics: confirm all indexes migrated; refresh statistics on the target (or let auto-update kick in).
8. Application connection strings: update DNS or secrets to point at the new target; verify the application's retry logic tolerates the brief cutover blip.

The exam pattern to recognize: a scenario describes a migration that 'completed successfully' but the application cannot connect or queries fail. The answer is almost always one of the four failure classes above - orphaned users, collation conflict, missing linked server, or DMS agent permission - and the diagnostic always starts with the DMA assessment report and the DMS activity log.