LLI Wiki — My Digital Knowledge Base

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:

  1. Read the source catalog (tables, columns, primary keys, indexes, foreign keys).
  2. Translate into MySQL CREATE TABLE.
  3. Disable foreign key checks, drop and recreate tables with the primary key only.
  4. Load the data table by table.
  5. Then create secondary indexes and foreign keys (much faster than before the load), and re-enable checks.
  6. 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:

-- 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

Mastodon LLIMastodon SocialFosstodon