How to Migrate from MySQL to SQL Server in PostgreSQL

Galaxy Glossary

How can I migrate a MySQL database to Microsoft SQL Server without data loss?

Move schemas and data from a MySQL database to Microsoft SQL Server quickly and safely.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does “migrating MySQL to SQL Server” involve?

You must convert schema, translate data types, move the data itself, and rewrite application queries that use MySQL-specific syntax.

Which tools handle most of the heavy lifting?

Use Microsoft SQL Server Migration Assistant (SSMA), the Data Migration Assistant (DMA), or command-line exports paired with BULK INSERT or bcp.

When should I choose SSMA?

SSMA excels when you need automatic schema conversion, data copy, and post-migration validation in one wizard-driven workflow.

When is dump-and-load faster?

For small databases or one-time moves, exporting to CSV with mysqldump --tab and loading via BULK INSERT can finish in minutes.

How do I prepare my MySQL schema?

Disable foreign keys and triggers, set SET FOREIGN_KEY_CHECKS = 0, then capture DDL with mysqldump --no-data so SSMA can parse clean definitions.

How does SSMA convert data types?

SSMA maps MySQL INT to INT, TINYINT(1) to BIT, DATETIME to DATETIME2, and TEXT to NVARCHAR(MAX). Review mappings and override when storage or precision matters.

How do I migrate data?

In SSMA, click “Migrate Data.” For manual moves, export each table:
mysqldump -T /tmp/ecom --fields-terminated-by="," ecommerce_db Products
Then load into SQL Server:
BULK INSERT dbo.Products FROM '/tmp/ecom/Products.txt' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n')

How do I verify migrated rows?

Run counts and checksums:
SELECT COUNT(*) FROM mysql_db.Products
vs.
SELECT COUNT(*) FROM sqlsrv_db.dbo.Products. For deeper checks, compare CHECKSUM_AGG on key columns.

What post-migration optimizations matter?

Create clustered indexes, rebuild statistics, and enable READ_COMMITTED_SNAPSHOT for row-versioning concurrency similar to MySQL’s default.

How do I cut over with minimal downtime?

Replicate ongoing MySQL changes into SQL Server using Azure DMS or custom CDC scripts, run a final sync, switch application connection strings, and monitor error logs.

Why How to Migrate from MySQL to SQL Server in PostgreSQL is important

How to Migrate from MySQL to SQL Server in PostgreSQL Example Usage


-- Verify row counts match after migration
SELECT
    (SELECT COUNT(*) FROM mysql_ecom.Customers)  AS mysql_customers,
    (SELECT COUNT(*) FROM sqlsrv_ecom.dbo.Customers) AS sqlsrv_customers;

-- Spot-check data integrity in Orders
SELECT TOP 5 id, customer_id, order_date, total_amount
FROM   sqlsrv_ecom.dbo.Orders
ORDER BY id DESC;

How to Migrate from MySQL to SQL Server in PostgreSQL Syntax


-- 1. Export schema only
mysqldump --no-data --routines --triggers ecommerce_db > schema.sql

-- 2. Use SSMA CLI to convert and load schema
ssmacmd -s MySQL -t SQLServer ^
         -om MyEcomProject ^
         -sourceserver localhost -sourceuser root -sourcepwd P@ss ^
         -targetserver sqlsrv01 -targetdatabase EcomDB -targettrusted ^
         -createproject -convertschema -migratedata

-- 3. Manual CSV path (Products table)
mysqldump -T /tmp/ecom --fields-terminated-by="," ecommerce_db Products

-- 4. SQL Server bulk load
a) CREATE TABLE dbo.Products (
       id INT PRIMARY KEY,
       name NVARCHAR(255),
       price DECIMAL(10,2),
       stock INT);
b) BULK INSERT dbo.Products
       FROM '/tmp/ecom/Products.txt'
       WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Common Mistakes

Skipping data-type review. Allowing SSMA to choose defaults can store currency as FLOAT instead of DECIMAL, causing rounding errors. Always validate mappings before loading.
Leaving MySQL triggers and foreign keys enabled during dump. They slow export and may re-fire on import. Disable them, migrate, then recreate equivalent logic in SQL Server.

Frequently Asked Questions (FAQs)

Is SSMA free to use?

Yes, Microsoft distributes SQL Server Migration Assistant at no cost.

Can I migrate stored procedures automatically?

SSMA converts many routine constructs, but complex MySQL-specific syntax (e.g., LIMIT offset) may need manual editing.

How do I handle MySQL ENUM columns?

SSMA maps ENUM to NVARCHAR. Create CHECK constraints in SQL Server to enforce allowed values post-migration.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie