Execute and Troubleshoot Migrations

Executing migrations

Offline migration: step by step

To Azure SQL Managed Instance (native backup/restore)

Kenji’s preferred method for databases with an acceptable downtime window:

1. Take a full backup to Azure Blob Storage

BACKUP DATABASE NorthStarERP
TO URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP.bak'
WITH CREDENTIAL = 'AzureStorageCredential', COMPRESSION;

2. Restore on MI

RESTORE DATABASE NorthStarERP
FROM URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP.bak';

3. Validate — check row counts, run application smoke tests
4. Redirect applications — update connection strings to MI endpoint

Key requirements:

• Backup must be in Azure Blob Storage (MI can’t restore from local disk)
• Use CREDENTIAL for storage authentication (SAS token or managed identity)
• Backup must be compatible with the MI SQL version
• COPY_ONLY backups recommended to avoid breaking on-prem log chains

To Azure SQL Database (BACPAC)

Priya uses BACPAC for new customer database provisioning:

1. Export BACPAC from source (via SSMS, SqlPackage, or Azure Portal)
2. Upload to Azure Blob Storage
3. Import into Azure SQL Database (via Portal or SqlPackage)

# Export using SqlPackage
SqlPackage /Action:Export /ssn:source-server /sdn:MyDB /tf:MyDB.bacpac

# Import into Azure SQL Database
SqlPackage /Action:Import /tsn:myserver.database.windows.net \
  /tdn:MyDB /tu:admin /tp:Password /sf:MyDB.bacpac

💡 BACPAC limitations

BACPAC files include both schema and data — but be aware:

• No incremental sync — it’s a point-in-time snapshot
• Size limit: Import can be slow for databases over 150 GB
• Schema validation on import can fail if unsupported features are used
• For large databases, consider DMS or native backup/restore (to MI) instead

Online migration: step by step

Azure DMS online migration to SQL Database

1. Create a DMS instance in the Azure portal
2. Create a migration project — specify source (on-prem SQL Server) and target (Azure SQL DB)
3. Run pre-migration assessment — validates compatibility
4. Start migration — DMS performs initial full data copy
5. Continuous sync — DMS tails the transaction log, replicating ongoing changes
6. Monitor sync status — check for replication lag in the DMS dashboard
7. Cutover — when sync is caught up, stop application writes, wait for final sync, switch connection strings

Log Replay Service (LRS) to Managed Instance

Kenji’s method for the mission-critical ERP database:

1. Configure continuous log backup shipping to Azure Blob Storage
2. Start LRS in continuous mode

Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "NorthStarRG" `
  -InstanceName "northstar-mi" -DatabaseName "NorthStarERP" `
  -StorageContainerUri "https://northstarstorage.blob.core.windows.net/logbackups" `
  -StorageContainerSasToken "sv=2021-06-08..." `
  -AutoComplete

3. LRS restores full backup, then continuously restores differential and log backups
4. Monitor progress — check restore status, replication lag
5. Cutover — take a final log backup, LRS restores it, database comes online
6. Redirect applications to MI

ℹ️ LRS auto-complete vs manual complete

• Auto-complete: LRS completes automatically when it detects the last log backup (with NORECOVERY flag removed). Set the -AutoComplete flag.
• Manual complete: You explicitly trigger the cutover with Complete-AzSqlInstanceDatabaseLogReplay. Gives more control over timing.

Auto-complete is simpler; manual is safer for mission-critical databases where you want to verify sync status before cutover.

Migrating between Azure SQL services

Sometimes the migration is within Azure — moving from one service to another:

From → To	Method
SQL Database → SQL Database	Database copy, geo-replication, BACPAC
SQL Database → Managed Instance	DMS, BACPAC export/import
Managed Instance → SQL Database	BACPAC export/import (may lose MI-specific features)
Managed Instance → Managed Instance	MI database copy, MI link, native backup/restore
SQL VM → Managed Instance	DMS, native backup/restore, LRS
SQL VM → SQL Database	DMS, BACPAC

MI database copy and move

Azure SQL Managed Instance supports database copy and move operations — particularly useful for:

• Moving databases between MIs in different subscriptions or regions
• Creating copies for testing/dev environments
• Disaster recovery setup

Database copy

Creates a copy of a database on the same or different MI:

• Same instance: Quick internal copy
• Cross-instance: Uses managed instance link technology
• The copy is a point-in-time snapshot
• Source database remains online during copy

Database move

Transfers a database from one MI to another:

• Source database is removed after successful transfer
• Works across subscriptions and regions
• Uses Azure Resource Manager for orchestration

💡 Exam tip: copy vs move

The exam distinguishes between copy and move:

• Copy = database exists on BOTH source and target after the operation
• Move = database exists ONLY on the target after the operation (removed from source)

Both operations are initiated through the Azure portal, PowerShell, or CLI — not through T-SQL.

Troubleshooting migrations

Migrations fail. Kenji’s learned these lessons the hard way:

Common failure patterns

Problem	Cause	Fix
Compatibility errors	Source uses features not supported on target	Run DMA assessment first; remediate or choose different target
Timeout during import	Large database, slow network, insufficient target tier	Increase target tier temporarily; use native backup instead of BACPAC for large DBs
Login/user orphaning	Logins exist on source server but not target	Map orphaned users with ALTER USER ... WITH LOGIN or create new logins
Collation mismatch	Source and target have different default collations	Set explicit collation in CREATE DATABASE or use COLLATE in queries
Storage credential failures	SAS token expired, wrong permissions, wrong container	Regenerate SAS token with Read/List permissions; verify container path
DMS sync lag increasing	Source generating changes faster than DMS can replicate	Increase DMS tier, reduce source write load, check network bandwidth
LRS stuck on a log backup	Missing or corrupt log backup file	Check blob storage for gaps in log sequence; re-take the missing backup

Post-migration validation checklist

1. Row counts — compare source and target for all tables
2. Checksum validation — verify data integrity
3. Schema comparison — objects, indexes, constraints, permissions
4. Application testing — run functional tests against the new target
5. Performance testing — compare query execution times
6. Security validation — logins, users, permissions, encryption status
7. Monitoring setup — configure alerts on the new target

Tomas asks Sam: “What if the migration fails halfway through?” Sam replies: “That’s why we always keep the source running until validation is complete. Offline migration? Don’t delete the source for at least two weeks. Online migration? Keep DMS running until you’re confident in the target.”

Question

What is an orphaned user in the context of migration?

Click or press Enter to reveal answer

Answer

An orphaned user is a database user that exists in the migrated database but has no matching login on the target server. Fix with ALTER USER ... WITH LOGIN or by creating the login on the target.

Click to flip back

Question

What's the difference between MI database copy and move?

Click or press Enter to reveal answer

Answer

Copy: database exists on both source and target after the operation. Move: database exists only on the target — it's removed from the source.

Click to flip back

Question

When using native backup/restore to migrate to MI, where must the backup file be stored?

Click or press Enter to reveal answer

Answer

Azure Blob Storage. MI cannot restore from local disk or network shares. Use BACKUP ... TO URL with a storage credential (SAS token or managed identity).

Click to flip back

Knowledge Check

Kenji migrated a database to Azure SQL MI using native backup/restore. Users report they can't log in. The error says 'user not found.' What is the most likely cause?

AThe database is in single-user modeBTransparent Data Encryption (TDE) is blocking loginsCDatabase users are orphaned — no matching logins on the MIDThe MI firewall is blocking connections

Check Answer

Knowledge Check

Priya needs to move a database from one Azure SQL Managed Instance to another in a different Azure subscription. The source database should not exist after the operation. What should she use?

ABACPAC export and importBAzure DMS online migrationCMI database move operationDNative backup to Blob Storage and restore on target MI

Check Answer

Knowledge Check

During an LRS online migration to MI, Tomas notices the restore is stuck and not progressing. What should he check first?

AThe MI compute tier is too smallBThere is a gap in the log backup sequence in Azure Blob StorageCThe database is in FULL recovery mode on the sourceDThe MI subnet has insufficient IP addresses

Check Answer

🎬 Video coming soon

You’ve completed Domain 1! You now understand how to choose, deploy, scale, and migrate Azure SQL platforms.

Next up: Entra Authentication for Azure SQL — secure your databases with Microsoft Entra ID authentication.