Data Migration - Azure SQL MI and Azure SQL DB
Data Migration Between Azure SQL Managed Instance and Azure SQL Database: Methods and Considerations As of this writing, Azure Database Migration Service (DMS) doesn't support SQL MI nor SQL DB as the source database (Supported migration scenarios). As an alternative to use DMS, one of the common support requests we receive from Azure SQL customers is about the best ways to migrate data between Azure SQL Managed Instance (SQL MI) and Azure SQL Database (SQL DB), or vice versa. This post outlines the primary methods available, their pros and cons, and key considerations to help you choose the right approach for your scenario. 1. BACPAC Export/Import Overview: This is a straightforward method where you export your database schema and data to a BACPAC file, then import it into the target environment. Key Points: Simplicity: Easy to use for small to medium-sized databases. Limitations: The BACPAC file size is limited to 200GB when exporting to Azure Blob Storage. Considerations: Larger databases may require splitting or alternative methods. Some features and objects (like certain types of indexes or security settings) may not be fully supported in BACPAC exports/imports. 2. Transactional Replication Overview: If your business requires data to be online during the migration, transactional replication may be your choice. Key Points: Online Migration: Enables near real-time data replication, making it suitable for scenarios where minimal downtime is required. Initialization Challenges: Large tables (having size of hundreds of GBs or more) can make the initial snapshot and data transfer challenging. One-way Only: This method only supports migration from SQL MI to SQL DB, not the other way around. SQL DB cannot act as a publisher due to the lack of Replication Agent (SQL Server Agent) support. 3. Azure Data Factory (ADF) Overview: ADF is a flexible, cloud-based ETL service that can orchestrate data movement between SQL MI and SQL DB. Key Points: Flexibility: Supports both offline and (with limitations) online migrations. Learning Curve: Some initial setup and learning may be required if you’re new to ADF. Online Migration Limitations: Online migration using ADF requires configuring Change Data Capture (CDC) on the source, which is not supported for SQL DB as a source. This means online migration is only feasible from SQL MI to SQL DB, not the reverse Here’s a sample decision matrix to help you choose the best migration method between SQL MI and SQL DB Criteria / Scenario BACPAC Export/Import Transactional Replication Azure Data Factory Direction: SQL MI -> SQL DB ✔️ ✔️ ✔️ Direction: SQL DB -> SQL MI ✔️ ❌ ✔️ Database Size < 200GB ✔️ ✔️ ✔️ Database Size > 200GB ❌ ✔️ ✔️ Minimal Downtime Needed ❌ ✔️ Partial* Online Migration ❌ ✔️ Partial* Learning Curve Low Medium Medium *ADF can support near-online migration if Change Data Capture (CDC) is enabled on the source, but CDC is not supported for SQL DB as a source.Data Migration - Collation mismatch error during an offline migration
This post explores a collation mismatch error during offline migration from SQL Server Express to Azure SQL. It identifies the root cause—AUTO_CLOSE being enabled—and offers a simple fix to ensure smooth migration using Azure DMS.
