This is a test migration on invented records. Kove Energy is a showcase firm, not a client. I built a stand-in for an old SQL Server 2012 database, gave it the faults old databases usually have, and moved it into a Dataverse environment to see what breaks. The migration, the build and the app are real. The data is not.
I am writing it up because the useful part is the failure and reconciliation evidence, not the headline row count.
The setup
The scenario is a home energy installer of about 15 people running a custom Windows desktop app on SQL Server 2012 Standard, on Windows Server 2012 R2. Both layers are past end of support. SQL Server 2012 is not supported on Windows Server 2022 or 2025, so replacing the server means the app has nowhere to go.
I rebuilt it on Dataverse with a Power Apps canvas app on top: 8 tables, 8 screens, three roles. It was a rebuild, not a lift into Azure SQL, because a copy would carry every old fault across.
The stand-in held 120,000 rows across 8 tables in a 72.0 MB data file, on SQL Server Express on my build PC. The target was an environment called Kove Staging, a Developer environment run as a Managed Environment.
Make the load re-runnable first
Each of the 8 tables keeps its old ID as an alternate key, or a stable new one where the old table had none. I read all 8 keys back and all 8 were Active before the load started.
That one decision is what made everything after it safe. A load keyed on a stable ID can be run again without loading a row twice. Three tables had no primary key at all, and two of them, 74,590 rows, needed a stable ID before they could move.
The faults I planted, and the rule for each
- Unknown dates. 1900-01-01 stood in for "unknown" on 37 dates (12 on jobs, 25 on people). They are blank now.
- Broken links. 114 links pointed at rows that no longer existed. A reconciliation step before the load reports every one. A missing designer was blanked on 15 jobs and a missing installer on 12. On 38 job lines the product was gone, so the link was blanked and the line kept.
- Macrons. My first build of the stand-in used a non-Unicode collation and stored 203 names with the macrons stripped. A code review caught it. I fixed it with Unicode columns before the run, and 203 of 203 names read back from Kove Staging exactly as written.
- Padded values. 50 values carried trailing spaces from old fixed-width columns. They were trimmed on the way in.
- Duplicates. The data held 150 duplicate people. I merged 0. Whether two records are the same human is a person's call, so the app flags them for review.
The run, and what failed
A small trial of 2,776 rows went first, into my dev environment: 101 checks passed, 0 failed.
The full run took 0.2 minutes to export, 0.1 to clean and reshape, 67.6 to load and 2.1 to verify. Dataverse sent no throttling responses (HTTP 429) during the run.
The first pass landed 119,996 rows, not 120,000. Four comments were lost to connection resets. The shortfall only showed up because the counts were compared.
I retried the four, and the total came to 120,000. The load ran 2 more times in all, once for those comments and once after I took house numbers out of the invented addresses. Across all of them, 0 rows were loaded twice.
The alternate keys are what made that retry safe to run: re-sending rows cannot create duplicates.
Why counts are not enough
Counts and a sample of rows can pass while one row has been swapped for another. So the final check compares the full key set of every table, in both directions: every source key must exist in Dataverse, and every Dataverse key must exist in the source. 145 of 145 checks pass.
| Measure | Value |
|---|---|
| Rows moved | 120,000 |
| Rows landed, first pass | 119,996 |
| Rows landed, after retrying 4 rows | 120,000 |
| Names with macrons kept | 203 of 203 |
| Rows loaded twice | 0 |
| Checks passed, after the retry | 145 of 145 |
| Total run time, first full pass | about 70 minutes |
Security was checked at the platform
There are three roles: office manager, office staff and installer. I tested them with 18 checks, six actions per role, and all 18 passed. Every refusal came back from Dataverse itself as a 403, so an installer cannot edit a job even by going around the app.
What I would take from it
- Give every table a stable key before you move anything. It is what makes a retry safe.
- Compare keys in both directions. A matching count proves very little.
- Decide the rule for each fault in advance, and count how often it fires.
- Expect a few rows to go missing on a long load, and make finding them cheap.
What this is not
These figures come from one dated set of test runs on invented data (2026-10-31), not from a client. I have not measured hours saved or payback, and I make no claim about either.
The demo is a clickable React copy of the app on invented data. It makes no network calls, and you can switch between the three roles and step through a replay of the real run: https://kove.nz/kove-energy-demo/
Originally published at kove.nz, where the full case study lives.
Top comments (0)