Moving data from Azure SQL to Azure Database for MySQL Flexible Server
Full-rebuild design, type mapping, batch loading, verification and pitfalls of an Azure SQL to MySQL migration.
Feeding an Azure Database for MySQL Flexible Server from an Azure SQL database is a heterogeneous migration: types, collations and several objects have no exact equivalent. This is the "rebuild on every run" approach I use for reference databases consumed by MySQL applications, with the design choice, the type mapping and the pitfalls I met.
Design: drop and rebuild
Every run drops and recreates the target tables. The target therefore faithfully reflects the source at launch time, with no incremental synchronisation logic to maintain. It suits moderate volumes and data that is fully reloaded; for large, evolving volumes you need another tool.
flowchart LR
A["Azure SQL Database"] --> B["Schema introspection"]
B --> C["Translation to MySQL DDL"]
C --> D["DROP + CREATE (primary key only)"]
A --> E["Batched data read"]
E --> F["Load (multi-row INSERT or LOAD DATA)"]
D --> F
F --> G["Secondary indexes + foreign keys"]
G --> H["Row count verification"]
I["Key Vault"] -.->|"credentials"| F
The steps:
- Read the source catalog (tables, columns, primary keys, indexes, foreign keys).
- Translate into MySQL
CREATE TABLE. - Disable foreign key checks, drop and recreate tables with the primary key only.
- Load the data table by table.
- Then create secondary indexes and foreign keys (much faster than before the load), and re-enable checks.
- Verify and produce a report.
Type mapping
| SQL Server | MySQL | Note |
|---|---|---|
int, bigint, smallint |
INT, BIGINT, SMALLINT |
|
tinyint |
TINYINT UNSIGNED |
0 to 255 on SQL Server |
bit |
TINYINT(1) |
|
decimal(p,s) |
DECIMAL(p,s) |
|
money |
DECIMAL(19,4) |
|
datetime2(n) |
DATETIME(n) |
|
nvarchar(n) |
VARCHAR(n) |
LONGTEXT for MAX |
uniqueidentifier |
CHAR(36) |
|
varbinary(max) |
LONGBLOB |
|
datetimeoffset |
DATETIME (UTC) or VARCHAR |
Offset lost: decide |
geography, hierarchyid, sql_variant |
no direct equivalent | Approximation, warning |
MySQL identifiers are limited to 64 characters, so index names must be shortened deterministically.
Batch loading
Two paths depending on table size:
- Small tables: parameterised multi-row
INSERT, in batches of a few thousand rows. - Large tables: bulk load (
LOAD DATA LOCAL INFILE, or the bulk copy class of the MySqlConnector .NET driver). This requires thelocal_infile = ONserver parameter on the Flexible Server.
-- MySQL side: before and after the load
SET FOREIGN_KEY_CHECKS = 0;
-- ... DROP / CREATE / load ...
ALTER TABLE `Orders` ADD INDEX `ix_Orders_1` (`CustomerId`);
ALTER TABLE `Orders` ADD CONSTRAINT `fk_Orders_Customer`
FOREIGN KEY (`CustomerId`) REFERENCES `Customer`(`CustomerId`);
SET FOREIGN_KEY_CHECKS = 1;
MySQL credentials are read from a Key Vault at run time, never from a versioned configuration file. The Flexible Server enforces TLS.
Verification
The minimum: row counts table by table, source against target, with a report. For critical tables I add an aggregate (sum of a numeric column, min/max of a date) on both sides.
Pitfalls and lessons learned
- Collation: a case- and accent-insensitive collation on the MySQL side can silently merge keys that differ only by case or accent (for example "Butgenbach" and "Bütgenbach"). The load then reports fewer rows inserted than copied. I use a case- and accent-sensitive
utf8mb4collation so no rows are ever lost. - Default values (
GETDATE(),NEWID()): not portable, recreate them by hand. - Views, procedures, triggers, computed columns: not migrated. This approach moves tables and their data, nothing else.
IDENTITYtoAUTO_INCREMENT: only when the column is the single-column primary key.- Date precision: SQL Server
datetimehas a peculiar granularity; choose an explicit fractional precision on the MySQL side. - Driver: pick a maintained, tested driver; verify bulk loading on the first real run (booleans in particular).
- Re-runnability: since everything is recreated, an interrupted run is simply restarted. That is the main virtue of this design.