Migrating from SQL Server to MySQL: Strategies, Tools, and Lessons Learned

Background: Founded in 2001, Hujiang originally built its services on early Microsoft technologies (ASP, .NET, SQL Server 2000). After more than a decade the company decided to de‑Windows‑ify its stack, migrating the application language from C# to Java and the database from SQL Server to MySQL.

Migration Process Overview: Two migration modes were designed – a simple offline (stop‑the‑world) migration for a few hours of downtime, and a more complex online migration that keeps the service running by synchronizing incremental changes and performing a short cut‑over.

Key Evaluation Criteria: data consistency before and after migration, acceptable downtime, code intrusion level, and support for schema restructuring.

Problems to Solve: mapping heterogeneous data types, handling structural differences, ensuring consistency, supporting schema changes, achieving online migration without service interruption, and providing a reliable rollback path.

Guidance Documents: MySQL’s official "Guide to Migrating from Microsoft SQL Server to MySQL" supplies datatype, predicate, operator, and function mapping tables, plus conversion suggestions for stored procedures. Special handling is required for hierarchyid and other non‑standard types.

ETL Tools Survey: Homogeneous tools – mysqldump, mysqlimport, pt‑table‑sync, XtraBackup. Heterogeneous tools – DataX, yugong, DB2DB, MySQL Workbench, Kettle, Ispirer, Navicat, etc. After evaluation Hujiang selected three solutions: DB2DB for small‑scale offline migrations, DataX for large‑scale offline migrations, and a customized yugong for online migrations with added features such as YAML configuration, case conversion, composite‑key support, and table sharding.

Consistency Check Tool: Percona’s pt‑table‑checksum inspired a custom check mode in yugong that compares source and target rows via JDBC, handling tables without primary keys by using logical row identifiers like rowid (Oracle) or physloc (SQL Server).

Rollback Strategy: To revert after a successful migration, Hujiang records data changes at the DAO layer, but prefers a non‑intrusive approach using MySQL binlog parsing with Canal, then replaying changes back to SQL Server via a reverse ETL pipeline built on yugong.

Operational Practices: Extensive pre‑migration load testing, full‑scale rehearsal environments mirroring production, detailed runbooks, and real‑time metrics dashboards for both MySQL and SQL Server. SQL review tooling (including Meituan‑Dianping/SQLAdvisor) is used to catch performance‑critical queries.

Results: Over a year, 28 night‑time migrations covering 42 systems (user, order, payment, e‑commerce, learning, community, content, tools) moved roughly 100 billion rows with a 100% success rate.

Related Links: MySQL Migration Guide , mysqldump , DataX , yugong , pt‑table‑sync , and many others.