MySQL → Azure SQL Database

📄 Overview

This SOP covers a full cross-engine migration from MySQL (5.7 or 8.0) running on-premises, AWS RDS for MySQL, or Azure Database for MySQL Flexible Server, to Azure SQL Database (single database or elastic pool). MigrationBridge handles schema conversion, data transfer, and optional live replication via MySQL binary log streaming.

Key Engine Differences

MySQL and SQL Server use fundamentally different paradigms. The table below summarizes the most impactful cross-engine differences MigrationBridge resolves automatically:

🔑 Prerequisites — MySQL Source

1. Driver

MigrationBridge uses mysql-connector-python. Verify it is installed in the MigrationBridge Python environment:

pip show mysql-connector-python

If missing:

pip install mysql-connector-python

2. MySQL User Permissions

The migration user requires the following grants. For a bulk (snapshot) migration, SELECT is sufficient. For live binlog replication, additional replication privileges are required.

-- Bulk migration only
GRANT SELECT ON your_database.* TO 'migration'@'%';

-- Live binlog replication (in addition to SELECT)
GRANT REPLICATION CLIENT ON *.* TO 'migration'@'%';
GRANT REPLICATION SLAVE  ON *.* TO 'migration'@'%';

FLUSH PRIVILEGES;

3. Validation Queries

Run these on the MySQL source before starting MigrationBridge:

-- Check MySQL version
SHOW VARIABLES LIKE 'version';

-- Verify grants for the migration user
SHOW GRANTS FOR 'migration'@'%';

-- Confirm binary logging is enabled (required for live mode)
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

-- Check current binary log position (note for live sync baseline)
SHOW MASTER STATUS;

4. Network Connectivity

• MySQL port 3306 must be reachable from the MigrationBridge host.
• For AWS RDS: ensure the RDS security group inbound rule allows the migration host IP on port 3306.
• For Azure Database for MySQL Flexible Server: add the migration host IP to the Firewall rules in the Azure Portal.
• For on-premises MySQL: confirm firewall / VPN allows outbound TCP 3306.

☁ Prerequisites — Azure SQL Target

1. Service Tier Recommendation

For production migrations, provision at least:

• DTU model: Standard S3 (100 DTUs) or higher — avoid Basic/S0/S1/S2 for large tables.
• vCore model: General Purpose, 2+ vCores — recommended for > 100 GB databases.
• Scale up temporarily for the migration window; scale back down after validation.

2. Firewall Rule

Add the MigrationBridge host IP to the Azure SQL Server firewall:

-- Azure CLI (run from migration host or Cloud Shell)
az sql server firewall-rule create \
  --resource-group <rg-name> \
  --server <sql-server-name> \
  --name MigrationBridgeHost \
  --start-ip-address <migration-host-ip> \
  --end-ip-address <migration-host-ip>

3. Contained Database User

Create a contained database user with db_owner in the target database:

-- Connect to master first to create login (optional for contained user)
-- Then connect to the target database:
CREATE USER [migration_user] WITH PASSWORD = '<StrongPassword123!>';
ALTER ROLE db_owner ADD MEMBER [migration_user];

-- Verify
SELECT dp.name, dp.type_desc, rp.name AS role_name
FROM  sys.database_principals dp
JOIN  sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
JOIN  sys.database_principals rp  ON drm.role_principal_id = rp.principal_id
WHERE dp.name = 'migration_user';

4. Test Connection from MigrationBridge

sqlcmd -S <server>.database.windows.net,1433 -d <database> -U migration_user -P "<password>" -Q "SELECT @@VERSION"

Open MigrationBridge and navigate to Connections. Configure the MySQL source and Azure SQL target.

MigrationBridge scans the MySQL source schema and produces a compatibility report before any data is moved. This is a read-only pass — no changes are made to either database.

Choose which tables to migrate. MigrationBridge lists all tables from the MySQL source database with row counts, size estimates, and any conversion warnings.

MigrationBridge executes in phases: (1) DDL generation and schema creation on Azure SQL, (2) data transfer table-by-table with batched INSERTs, (3) index and constraint creation. Monitor the real-time log below.

After migration completes, validate row counts on both sides and confirm IDENTITY seeds are correct.

Row Count Validation — Azure SQL Target

-- Row counts on Azure SQL target
SELECT
    t.name        AS table_name,
    p.rows        AS row_count
FROM  sys.tables t
JOIN  sys.partitions p
      ON t.object_id = p.object_id
      AND p.index_id IN (0, 1)
ORDER BY t.name;

Cross-Engine Row Count Comparison

Run this on MySQL source to get counts, then compare against Azure SQL output above:

-- MySQL source: row counts per table
SELECT
    table_name,
    table_rows AS estimated_rows
FROM  information_schema.tables
WHERE table_schema = 'sakila'
  AND table_type  = 'BASE TABLE'
ORDER BY table_name;

-- For exact counts (slower but accurate):
SELECT 'actor', COUNT(*) FROM actor
UNION ALL
SELECT 'film', COUNT(*) FROM film
UNION ALL
SELECT 'payment', COUNT(*) FROM payment
UNION ALL
SELECT 'rental', COUNT(*) FROM rental;

Verify IDENTITY Seeds (Azure SQL)

-- Check current IDENTITY seed vs max value for actor
SELECT
    OBJECT_NAME(object_id) AS table_name,
    last_value,
    seed_value,
    increment_value
FROM  sys.identity_columns
ORDER BY table_name;

-- Confirm actor IDENTITY matches MySQL MAX(actor_id)
SELECT MAX(actor_id) AS max_id FROM [dbo].[actor];

Spot-Check Data Integrity

-- Verify ENUM-converted column values are intact
SELECT DISTINCT rating FROM [dbo].[film] ORDER BY rating;
-- Expected: G, NC-17, PG, PG-13, R

-- Verify TINYINT(1)->BIT conversion for staff.active
SELECT staff_id, first_name, last_name, active FROM [dbo].[staff];
-- active should be 0 or 1 (BIT)

-- Verify TIMESTAMP->DATETIME2 conversion
SELECT TOP 5 last_update FROM [dbo].[film] ORDER BY last_update DESC;
-- Should display as DATETIME2 with full precision

Complete these tasks after data validation passes, before handing off to application teams or cutting over traffic.

1. Reseed Identity Columns

MigrationBridge auto-reseeds during Phase 4, but verify and manually correct if needed:

-- Reseed actor identity to max value + 1
DBCC CHECKIDENT('dbo.actor', RESEED, 200);   -- replace 200 with MAX(actor_id)
DBCC CHECKIDENT('dbo.film', RESEED, 1000);
DBCC CHECKIDENT('dbo.customer', RESEED, 599);
DBCC CHECKIDENT('dbo.payment', RESEED, 16049);
DBCC CHECKIDENT('dbo.rental', RESEED, 16044);

-- Verify all identity seeds
SELECT OBJECT_NAME(object_id) table_name, last_value, seed_value
FROM  sys.identity_columns ORDER BY table_name;

2. Update Statistics

-- Update all statistics on freshly loaded tables
EXEC sp_updatestats;

-- Or per-table (recommended for large tables):
UPDATE STATISTICS [dbo].[payment] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[rental]  WITH FULLSCAN;

3. Verify ENUM Values and Add CHECK Constraints (Optional)

-- Confirm ENUM values are correct on target
SELECT DISTINCT rating FROM [dbo].[film];

-- Optionally add CHECK constraint to enforce ENUM domain on Azure SQL
ALTER TABLE [dbo].[film]
    ADD CONSTRAINT chk_film_rating
    CHECK (rating IN ('G', 'PG', 'PG-13', 'R', 'NC-17'));

4. Review JSON Columns (if any)

-- MySQL JSON columns are migrated as NVARCHAR(MAX)
-- Test JSON path queries with SQL Server's JSON functions (SQL 2016+)
SELECT
    id,
    JSON_VALUE(metadata_col, '$.key') AS extracted_value
FROM  [dbo].[your_table]
WHERE ISJSON(metadata_col) = 1;

5. Replace MySQL Events with Azure Elastic Jobs

MySQL scheduled events have no direct equivalent in Azure SQL Database. Re-implement them using:

• Azure Elastic Jobs — native Azure SQL scheduled jobs (recommended for SQL-based maintenance tasks)
• Azure Logic Apps — for event-driven or complex schedule patterns
• Azure Functions with Timer trigger — for custom code-based jobs

6. Index Review and Rebuild

-- Check index fragmentation after bulk load
SELECT
    OBJECT_NAME(ips.object_id)     AS table_name,
    i.name                          AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM  sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN  sys.indexes i
      ON ips.object_id = i.object_id
      AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

⚡ Live Binlog Replication — MySQL → Azure SQL

MigrationBridge supports live change replication using MySQL binary log (binlog) in ROW format. This enables a low-downtime cutover strategy: perform the full load first, then tail the binlog to keep Azure SQL in sync until you are ready to cut over application traffic.

1. Enable Binary Logging on MySQL (on-premises)

Edit my.cnf (Linux) or my.ini (Windows) and restart MySQL:

[mysqld]
server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_format      = ROW
binlog_row_image   = FULL
expire_logs_days   = 30
# MySQL 8.0+ alternative to expire_logs_days:
# binlog_expire_logs_seconds = 2592000

Verify after restart:

SHOW VARIABLES LIKE 'log_bin';          -- should be ON
SHOW VARIABLES LIKE 'binlog_format';    -- should be ROW
SHOW VARIABLES LIKE 'binlog_row_image'; -- should be FULL
SHOW MASTER STATUS;

2. Enable Binary Logging on AWS RDS for MySQL

RDS manages the MySQL config file — use Parameter Group and the RDS procedure:

-- Set binlog retention in RDS (hours) — call from any MySQL client connected to RDS
CALL mysql.rds_set_configuration('binlog retention hours', 720);

-- Verify binlog format (set via Parameter Group: binlog_format = ROW)
SHOW VARIABLES LIKE 'binlog_format';
SHOW MASTER STATUS;

3. Azure Database for MySQL Flexible Server

-- Binlog is enabled by default on Azure DB for MySQL Flexible Server
-- Verify format via Azure Portal: Server Parameters > binlog_format
-- Or via CLI:
az mysql flexible-server parameter show \
  --resource-group <rg> \
  --server-name <mysql-server> \
  --name binlog_format

4. Full Load First, Then Enable Live Sync

1. Record the binlog position before starting the full load: SHOW MASTER STATUS; — note File and Position.
2. Run the full load migration in MigrationBridge (Steps 1–5 above).
3. In MigrationBridge, navigate to Live Sync tab and enter the binlog file and position recorded in step 1.
4. Start the binlog tail — MigrationBridge will replay all INSERTs, UPDATEs, and DELETEs that occurred during and after the full load.
5. Monitor lag in the Live Sync panel. When lag reaches < 1 second, the target is caught up.
6. Cut over application connection strings from MySQL to Azure SQL. Stop the live sync job in MigrationBridge.

5. Validation During Live Sync

-- Compare row counts during live sync (run on both sides)
-- MySQL source:
SELECT COUNT(*) FROM rental;   -- e.g. 16,052

-- Azure SQL target:
SELECT COUNT(*) FROM [dbo].[rental]; -- should match within lag window

-- Confirm a specific row was replicated
SELECT * FROM [dbo].[rental] WHERE rental_id = 16052;

🔄 Type Conversion Map

Complete MySQL → Azure SQL (T-SQL) data type mapping used by MigrationBridge. All conversions are applied automatically during schema generation (Phase 1).

⚠ Known Issues & Limitations

Case-Insensitive Table Names (MySQL) vs Case-Sensitive Collation (SQL Server)

ENUM Values — No Constraint on Target

MySQL enforces ENUM domain at the storage engine level. After migration to NVARCHAR, any string can be inserted. If you require constraint enforcement, add a CHECK constraint manually (see Step 6).

INT UNSIGNED Overflow Risk

ZEROFILL Formatting Lost

MySQL's ZEROFILL attribute is a display-only zero-padding hint. It is not a storage attribute. MigrationBridge drops it during conversion — the numeric value is preserved but leading zeros are not rendered by default. If your application depends on zero-padded display, implement this in application code or a computed column.

MySQL Events → Azure Elastic Jobs

MySQL EVENT objects (scheduled tasks) are not migrated. Each event must be re-created manually as an Azure Elastic Job, Azure Logic App, or Azure Function Timer trigger. Review all events before cutover:

SELECT event_name, event_definition, interval_value, interval_field, status
FROM  information_schema.events
WHERE event_schema = 'sakila';

MySQL Stored Procedures Not Migrated

Backtick Identifier Conversion

MySQL uses backtick (`) quoting for identifiers. MigrationBridge converts all backtick-quoted identifiers to SQL Server square bracket ([ / ]) notation in generated DDL and DML. Application queries that still use backtick syntax will fail against Azure SQL — update all application SQL to use square brackets or remove quoting if not needed.

MySQL Schema Mapping

In MySQL, a "database" (e.g., sakila) is analogous to a SQL Server "schema." MigrationBridge maps each MySQL database to the dbo schema by default. If you are migrating multiple MySQL databases into one Azure SQL database, configure the schema name mapping in MigrationBridge's Advanced Options to avoid table name collisions.

📷 UI Behavior During Migration

MigrationBridge's UI remains fully responsive during migration and live sync operations. All database work runs in background worker threads. The following behaviors are by design:

• The Progress Bar in the Migrate tab shows per-table and overall completion percentage. It updates every 500ms.
• The Log Panel auto-scrolls to the latest entry. Click anywhere in the log to pause auto-scroll; click Resume Scroll to re-enable.
• The Cancel button stops the migration after the current batch completes. Partial data may exist on the target — re-run the migration (MigrationBridge will truncate and reload tables that are incomplete).
• During Live Sync, the lag indicator updates every 2 seconds. A lag of < 5 seconds is normal; > 30 seconds indicates network or throughput issues — check CloudWatch / Azure Monitor metrics.
• The Verify tab can be opened at any time after the full load completes, even while live sync is running. Row counts reflect the state at query time.
• MigrationBridge writes a persistent sync state file (migrationbridge_sync_state_<jobname>.json) every 10 seconds during live sync. If the process is interrupted, it resumes from the last persisted binlog position on restart.