The Complaint
The setup had been ordered for SQL Server 2019. What got installed was SQL Server 2022. Not a malicious substitution, not a shortcut taken on purpose, just a version selected during setup that didn't match what had actually been agreed. It ran fine for a while. Then the complaint came in: the system felt slower than expected.
A "feels slower" complaint after a version that wasn't the one ordered is exactly the kind of report that looks, on the surface, like a straightforward engine regression. New major version, new query optimizer behavior, new default settings, case closed, roll it back.
What the Numbers Actually Said
Before touching anything, we measured instead of assuming. Two findings came back that didn't fit the "2022 is the problem" story at all:
- The database was already running at compatibility level 150, the SQL Server 2019 optimizer behavior. SQL Server 2022's newer query processing improvements were not even active for this workload; there was no new optimizer to blame.
- A side-by-side benchmark of representative workload on both versions showed SQL Server 2022 running about 15% faster, not slower.
That's the part worth sitting with for a moment: the complaint was genuine, the perception was real, but the measured cause and the assumed cause were not the same thing. This happens more often than most performance postmortems admit. Something changes, something else was already different (hardware, load pattern, an unrelated setting), and the most recent change gets blamed by default because it's the most visible one.
The Downgrade Happened Anyway
Here is the part that matters for what came next: the downgrade to SQL Server 2019 went ahead regardless of what the benchmark said.
That wasn't a contradiction. What had been ordered was SQL Server 2019, in writing, and that's what needed to be running, independent of which version happened to perform better in a benchmark. A measurement can settle a technical argument about performance. It doesn't settle a contractual one. The right fix for "we installed the wrong thing" is to install the right thing, not to talk the customer into keeping the wrong thing because it benchmarks better.
So the project became a real, live downgrade: move production databases off the wrongly-provisioned instance and onto a correctly-licensed one, with zero data loss and minimal downtime, on a live customer system.
Where the Existing Tools Ran Out
Moving most of the databases across was routine: backup and restore, or a script-and-recreate pass for smaller tables, verify row counts, done. Two of the databases weren't routine at all. Each ran to roughly 1.2 billion rows in their largest tables, no primary key, no natural resumability, and they needed to move without downtime for the rest of the system while it happened.
That combination breaks the usual quick options one at a time:
- A single BCP or backup/restore pass works until it doesn't: any interruption on a run that takes hours means starting completely over, because there's no primary key to anti-join against and figure out what already made it across.
- Generic bulk-copy tooling maps columns by position, not by name, once you're not copying a whole table verbatim. A computed column anywhere before the end of the physical column order silently shifts every later column's mapping by one, and the corruption doesn't announce itself, it just puts the wrong value in the wrong column.
- Verifying the copy afterward with a plain row count means a full table scan on both sides, which is exactly the kind of load you don't want to put on a live production system you're trying to migrate without disrupting.
- Disabling and rebuilding indexes around the load is necessary for bulk-load performance, but doing it the naive way, once per batch instead of once for the whole run, multiplies a rebuild that should happen once into a rebuild that happens hundreds of times.
None of this is exotic. It's the standard list of things that go wrong the first time anyone tries to move genuinely large, genuinely inconvenient tables between SQL Server instances under real constraints. What was missing wasn't cleverness, it was a tool that had already hit each of these problems once and been fixed.
The Schema Had the Same Problem, Repeated
Before moving anything, we ran a design/health scan across the whole schema, not just the two large databases, to know what we were actually dealing with. The result explained why "just script it and recreate it" wasn't going to hold up as a general strategy:
- A large number of tables had no primary key and no clustered index at all, sitting as heaps.
- A smaller but non-trivial set had no indexes whatsoever, not even a supporting one for the obvious lookup pattern.
- Several foreign keys existed but were disabled or untrusted, meaning SQL Server could not guarantee the data actually satisfied them even though the constraint was technically present.
- A handful of tables were unusually wide, which matters for how much a bulk load and an index rebuild each cost per row.
None of that is unusual for a schema that has grown over years under deadline pressure rather than around a clean design. But it meant the two large tables weren't an isolated edge case to special-case and move on from; missing keys, missing indexes, and untrusted constraints were the norm across the database, not the exception. Building resumability without requiring a primary key, and treating index rebuilds as something to minimize rather than assume, wasn't over-engineering for one bad table. It was the only approach that actually matched the schema in front of us.
Building the Tool the Migration Actually Needed
That's the origin of sqmDataTransfer: a PowerShell module, built on dbatools, purpose-built for exactly this situation and hardened against every failure mode above as it was found.
- Chunked, resumable transfer for tables too large to move in one pass: split by a natural column such as a reporting or snapshot date, copy one value at a time, and skip anything already verified present on a re-run, all without needing a primary key anywhere on the table.
- Explicit, name-based column mapping for every copy, independent of column order, computed columns, or which version of the underlying tooling happens to be installed on the machine running it.
- Metadata-based row-count verification instead of a full scan, so confirming a billion-row table matches on both sides doesn't itself become a production-impacting operation.
- Constraint and index handling scoped to the whole run, not per batch, so a bulk load doesn't pay for the same index rebuild dozens or hundreds of times over.
- A full HTML report and structured log for every run, because "I think it copied everything" is not an acceptable answer when the question is a customer's production data.
Every one of those points exists because something on this list actually happened during the migration, got diagnosed, and got fixed at the tool level instead of patched over once and forgotten.
The Bottom Line
The interesting thing about this story isn't the version mix-up. Mistakes like that happen; the useful response is to measure honestly, fix the actual root cause, and not let a benchmark talk you out of doing the contractually correct thing. The interesting part is what the fix required: a downgrade that had nothing to do with performance still surfaced a real, hard technical problem, moving huge, poorly-indexed tables safely between instances, that generic tools handle badly and that's worth solving properly once instead of improvising every time it comes up.
That's what sqmDataTransfer is for. It's free and MIT-licensed on GitHub, with the full list of what changed and why in its changelog.