A database migration is only finished when you can show that the data arrived complete, correct and usable. Testing a migration is different from testing application code: the main risks are missing rows, altered values, broken relationships and performance surprises. A layered set of checks catches most of them before users do.
Start with a dry run on realistic data
Run the full migration against a copy of production data, or a representative sample, in an environment that resembles production. A dry run reveals errors that small test databases hide, such as unexpected characters, values that do not fit the new types, duplicate keys and steps that take far longer than planned. Record the duration of each phase.
If production data contains personal information, follow your organization's rules on copying it; masked or anonymized copies may be required.
Compare row counts
The simplest check is to count rows in every table on the source and the target. Counts should match exactly unless the migration intentionally filters or merges data. For large tables, compare counts per partition or per date range so that a mismatch points you to the right place.
Matching counts do not prove the data is correct, but a mismatch is a clear signal to stop and investigate.
Compare checksums or aggregates
To detect changed values, compute a checksum or hash over ordered rows or key columns on both sides and compare the results. When source and target use different engines, hash functions and text representations may differ, so compute checksums in a neutral way, for example by exporting normalized values and hashing them outside the database. Aggregates such as sums of numeric columns, minimum and maximum dates and counts of nulls per column are a cheaper complement.
Sample and compare individual rows
Pick random rows and edge cases, such as the oldest and newest records, rows with nulls, very long text and special characters, and compare them field by field. Pay particular attention to:
- character encoding and accented or non-Latin text;
- time zones and timestamp precision;
- decimal precision and rounding;
- boolean and enum mappings between engines;
- empty strings versus nulls.
Verify constraints and relationships
Check that primary keys are unique, foreign keys point to existing rows and check constraints hold. If constraints were disabled during the load for speed, re-enable and validate them afterwards. Orphaned rows are a common sign that tables were loaded out of order or that a filter was applied inconsistently.
Check sequences and identity values
After copying data, auto-increment counters or sequences on the target may still start at 1. The first insert then fails with a duplicate key error. Set each sequence to a value above the current maximum before allowing writes.
Run application tests against the target
Point a test instance of the application at the migrated database and run the automated test suite plus a set of manual checks of key workflows. This finds problems such as SQL syntax that differs between engines, missing views or stored procedures and permissions that were not migrated.
Test performance
Query plans can change on a new engine, version or server. Run the most frequent and most expensive queries and compare their timing with the source. Make sure indexes exist, statistics are up to date and connection pool settings fit the new server.
Repeat the checks after cutover
Run the same validation scripts again immediately after the production migration and keep them available for the observation period. Automating checks as scripts makes them repeatable and fast, and gives you clear evidence if a rollback decision is needed.
Key takeaways
- Dry-run the full migration on realistic data and time each phase.
- Use counts, checksums, aggregates and row samples together.
- Validate constraints, sequences, application behaviour and performance.
- Script the checks and run them again after cutover.