A database migration almost never fails the way people picture it — a dramatic crash halfway through cutover weekend. It fails quietly. A batch of rows that never carried over because a foreign key pointed at something the migration script didn't expect. A report that's been quietly wrong for three weeks before anyone notices the totals don't match. A nightly job that worked fine on the old server and times out on the new one because nobody checked whether the new database's default settings matched the old one's. None of that looks like a failure on cutover day. It looks like a failure a month later, by which point nobody remembers which step might have caused it.
This guide is about the data layer specifically: moving a production database to a new engine version, a new platform, or a new provider, with the application on top staying largely the same. That's a different project from deciding whether your infrastructure belongs in the cloud at all — our cloud migration guide covers that broader decision — and different again from replacing the application itself, which our guide to replacing legacy software covers from the app side. Here, the application mostly survives; it's the database underneath it that's moving.
Why this comes up
A database migration is rarely optional once it starts. The usual triggers:
- An end-of-support deadline. A specific engine version stops getting security patches on a fixed date — our guide to [SQL Server 2016's own deadline](/articles/sql-server-2016-end-of-support) covers one example — and staying on it past that date stops being a choice you can quietly keep making.
- Outgrowing what you have. Query times that used to be instant now take seconds, and the fix is a bigger instance, a different engine, or a managed service that scales without you babysitting it.
- Consolidation. Two companies merge, or two systems that grew up separately both need to feed the same reporting layer.
- Moving the hosting, not the software. The database goes from a server in your office to a managed cloud database, while the application connecting to it barely changes.
None of these are emergencies on the day they appear. They become one if you wait until the deadline is a month away to start the real work.
The four phases that keep a migration boring
A migration that goes well is, almost by definition, uneventful. The way you get there is by doing the unglamorous work before cutover day, not during it.
1. Assess before you touch anything
Before any data moves, find out what will actually break. Compatibility issues — a data type the new engine handles differently, a feature your application depends on that doesn't exist on the target, a stored procedure written against behaviour that changed between versions — are far cheaper to find on paper than to discover in production. This is a genuine step with real tooling behind it, not just caution for its own sake: Microsoft's own guidance for SQL Server environments is built around exactly this kind of assessment, flagging what it calls migration-blocking issues, partially supported features, and breaking changes before you move a single row.
Worth knowing if you're in that world specifically: Microsoft's own free assessment tool for this job, the Data Migration Assistant, was retired on 16 July 2025. If an older guide or a colleague's notes point you at it, that advice is already out of date. Microsoft's current path depends on where you're headed — the Azure SQL migration extension for Azure Data Studio for a move to Azure SQL Managed Instance, or Azure Database Migration Service, which Microsoft describes as built for migrating databases from multiple sources to Azure with minimal downtime, for a larger-scale move. The tool matters less than the habit it points at: check what's current before you plan around it, because this exact tool changed within the last two years.
2. Clean and map the data, not just the schema
The schema is the easy part to plan for. The data itself is where migrations actually go wrong: duplicate customer records that both "worked" under the old application's loose validation, foreign keys pointing at rows that were soft-deleted years ago and never cleaned up, date fields that mix formats because three different integrations wrote to the same column over the years, encoding mismatches that turn accented characters into question marks. None of that shows up until you try to move it somewhere stricter.
Map every field you're moving to what it actually means today, not what the original documentation says it should mean — years of real-world use reliably drift from the original design. Decide explicitly what you're migrating versus archiving: old transactional history you're legally required to keep but nobody queries daily doesn't need to live in your fast, expensive production database going forward.
3. Choose your cutover method deliberately
There are two basic shapes, and most SMEs default to the first without seriously considering the second:
A big-bang cutover. Freeze writes to the old database, copy everything across, verify it, and switch the application over — all inside one planned downtime window. Simpler to execute and to reason about, and it's the right choice for most SME-scale migrations, because the data volumes involved usually fit comfortably inside a weekend or an evening.
A phased or parallel-run migration. Keep both systems live and in sync for a period, migrating in stages, so there's no single all-or-nothing window. This buys you lower downtime risk at the cost of real added complexity — you need a way to keep the two databases synchronised while both are live, and a plan for what happens if they briefly disagree. Worth it when a full stop genuinely isn't tolerable; overkill, and a source of its own bugs, when it isn't.
Pick the method based on how much downtime your business can genuinely absorb — decided explicitly, with the people who depend on the system, not estimated from what sounds reasonable — rather than on which option sounds more sophisticated. A retailer's order system and an internal reporting dashboard rarely have the same answer, and treating them as if they did is how a migration plan ends up either needlessly expensive or quietly risky.
4. Validate before you call it done
Row counts matching on both sides is the minimum check, not the finish line. Run your actual reports against the migrated data and compare the output to what the old system produced for the same period. Spot-check records a human can verify by hand, not just a handful chosen by the migration script's own logic. If you have the luxury of a non-production rehearsal environment, run the entire migration there first, exactly as you plan to run it for real, and time it — a rehearsal is also how you find out your "four-hour window" was actually going to take seven.
The rollback plan nobody wants to need
Keep the old database intact and reachable, in read-only mode if nothing else, for a defined period after cutover — long enough to catch the problems that only surface once real daily use resumes, not just the ones visible in the first hour. Decide in advance what would actually trigger a rollback, rather than deciding it live under pressure on the day something looks wrong. The migration that goes genuinely badly is usually the one where the only copy of the "before" state got deleted the moment the "after" state looked like it was working.
What carries over, and what doesn't
Moving a database to a new engine or a new provider doesn't reset any legal clock attached to the data inside it. If some of what you're migrating is personal data with its own retention obligations, those obligations travel with it — our guide on how long you're required to keep customer data under the AVG covers that question directly, and it's worth checking before you decide what to archive versus delete during the move rather than after.
The mistakes that show up every time
Skipping the compatibility assessment because "it's basically the same database." Basically the same is exactly where a default collation, a data type's precision, or a deprecated feature quietly differs.
Treating the schema as the migration and the data as an afterthought. The schema almost always moves cleanly. The accumulated mess inside the data is where the real work is.
No rehearsal. The first time you run the full migration should not be the one time it actually counts.
Deleting the source too soon. The old database is your rollback plan. Keep it until you're confident you won't need it, not until the first hour looks fine.
One downtime number for every system. A four-hour window can be generous for one database and nowhere near enough for another feeding a different part of the business.
FAQ
How much downtime does a data migration actually need? It depends on data volume, the method you choose, and the target. A small operational database often moves inside a planned window of a few hours; a phased, near-zero-downtime migration trades that short window for weeks of running two systems in sync. Decide the acceptable downtime first — the method follows from that.
Can you migrate a database without any downtime at all? Close to zero is realistic with a phased, parallel-run approach, but "zero" in an absolute sense is rare — there's almost always a brief final moment where the application switches from the old database to the new one.
How long does a typical SME database migration take? The technical move itself can take anywhere from a few hours to a weekend for most SME-scale systems. The project around it — assessment, data cleanup, a rehearsal — usually takes longer than people plan for, because data-quality work is harder to estimate upfront than the copy step.
Do we need a rehearsal migration, or can we just do it once properly? A rehearsal is how you find problems before they're live problems — a script that works against a clean test set often behaves differently against years of real production mess. Skipping it is the single most common reason a migration overruns its planned window.
What tools should we use for the migration itself? That depends on your source and target engines, and the answer changes over time — the tool Microsoft recommended for SQL Server assessments two years ago was retired in 2025 in favour of newer options. Check what's current for your specific engines rather than an older guide's exact tool names.
Getting the sequence right
The technical copy of a database migration is rarely the hard part — the assessment, the data cleanup, and a rehearsal that's actually run before the real date are. Our Data & Databases team runs that sequence end to end, including the compatibility assessment and the data reconciliation most SMEs underestimate. And when the migration's destination is a managed cloud platform rather than another server, our Cloud & Infrastructure team handles the hosting side of that move as part of the same project, rather than treating the database and its new home as two separate pieces of work.