Migrating From MySQL to SQL Server: The Gotchas

A table copy can finish while the application still behaves differently. Migrating from MySQL to SQL Server means checking semantics as carefully as row counts. Auto increment, text comparisons, paging, and dates deserve tests before cutover.

Assess Before Migrating From MySQL to SQL Server

Inventory schemas, tables, routines, triggers, views, and application queries. Run SQL Server Migration Assistant for MySQL against a representative source to identify conversion work. SSMA can assess and automate parts of schema and data migration, but it does not know every business rule in application code. Read its warnings and build a test list from them.

When migrating from MySQL to SQL Server, I start with the queries users care about, not only the largest tables. A small lookup can change behavior if string comparison changes. Which report would make the team distrust the new system on day one? Put that report in the validation plan. Record source version, target version, character set, collation, and time zone assumptions before converting anything.

Translate Auto Increment Carefully

MySQL AUTO_INCREMENT commonly maps to a SQL Server IDENTITY column, but behavior at insert time needs review. Check whether code supplies explicit keys, reads the most recent generated key, or expects a sequence shared across tables. In SQL Server, use SCOPE_IDENTITY or the OUTPUT clause for values created by a statement. Do not substitute a server-wide last identity query in concurrent code.

The example below uses a temporary table so it can be tested safely in a query window. It shows the OUTPUT pattern and then cleans up. Run the real insert through the application driver too, since return-value handling is part of the migration. I check identity gaps and reseed requirements after loading existing keys.

CREATE TABLE #IdentityDemo
(
    ItemId int IDENTITY(1,1) NOT NULL,
    ItemName nvarchar(50) NOT NULL
);
INSERT INTO #IdentityDemo (ItemName)
OUTPUT inserted.ItemId
VALUES (N'Test');
DROP TABLE #IdentityDemo;

Treat Case Sensitivity as Data Logic

MySQL and SQL Server text comparisons depend on their configured collations. Do not assume the source and target handle case, accents, or trailing spaces the same way. Compare unique keys, search results, and joins using representative values. A username table is a good example: if two source values differ only by case, the target unique constraint can reject one under a case-insensitive collation.

I make a small list of boundary strings and run it against both engines. The database default is only part of the story. Columns can have their own collation. Inspect the target columns after migration and record intentional exceptions. Collation is not a cosmetic setting when it changes which rows match.

Rewrite LIMIT and Ordering

When migrating from MySQL to SQL Server, LIMIT syntax does not copy directly into T-SQL. SQL Server uses TOP for a simple first set, or ORDER BY with OFFSET and FETCH for paging. Always give pages a stable order that breaks ties. Without it, page boundaries can move between calls even when the syntax runs. The query below uses system objects as a safe demonstration of SQL Server paging.

Application code that concatenates LIMIT values into SQL needs both syntax and parameter handling reviewed. I test the first page, a later page, and a page after rows change. A migration can return the right count but different records on each page when ordering was never defined.

SELECT name, object_id
FROM sys.objects
ORDER BY name, object_id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Audit Dates and Zero Values When Migrating From MySQL to SQL Server

MySQL installations can contain zero dates or values accepted under permissive source settings. SQL Server date and datetime types do not represent every such value. Decide whether each exceptional source value means unknown, missing, or an application error. Map it explicitly before loading. Check time zone storage and display behavior at the same time.

I prefer a staging column that preserves the original text while conversion rules are reviewed. TRY_CONVERT can identify values that fail the proposed target type without stopping the whole inspection. Do not silently replace every bad date with today’s date. That makes the migration finish and the history wrong.

Revisit SQL Functions and Procedures

String concatenation, null handling, date arithmetic, and error handling differ across engines. SSMA helps convert code, but generated code needs functional review. Compare result sets and side effects for representative inputs. Look especially at functions used in predicates, because an apparently equivalent rewrite can change index use and query plans.

I keep a list of source routines that changed shape substantially. Those get application-level tests, not just a successful CREATE statement. A stored procedure that compiles is a start. A stored procedure that commits the right rows under failure is the goal. Check transaction behavior and isolation assumptions as part of the same test.

Move Data in Rehearsed Batches While Migrating From MySQL to SQL Server

Choose a migration route for each table based on size, change rate, and allowed downtime. Load into a target that has known constraints and indexes, then compare row coverage, key ranges, and selected values. Reconcile any skipped or transformed rows. Keep a source snapshot or cutoff rule so validation compares the same data at both ends.

I ask for a rerun plan before the first full load. If an import stops halfway, the team should know how to clear, resume, or reconcile it. SSMA can do heavy lifting, but it does not replace a business decision about missing dates or duplicate keys. Record those decisions in the migration log.

Validate the Application, Then Cut Over

Run the application against the target with real authentication, representative concurrency, and critical workflows. Confirm query results, writes, reports, and error handling. Use Query Store or approved monitoring to see which translated queries need tuning. Do not declare success from a table count alone.

At cutover, stop or account for source writes, make the final data movement, validate it, and switch the application connection. Keep a rollback decision point before new target writes make reverse movement difficult. The best migration result is boring to users because the team did the surprising work in rehearsal. Save the source and target query outputs used for sign-off so later differences can be traced to a specific cutoff.

Related reading on this blog: Common Mistakes to Avoid for DBAs Working with MySQL Databases and Migration Assistant for Oracle, MySQL, Sybase and Access v5.2.

A MySQL migration is not a completed copy, it is matching application behavior on a different engine.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.