Migrating data and verifying the migration
Moving data from one database to another: inventory, column and type mapping, dry run, cutover and rollback; then the checks that prove nothing has changed.
- 28 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Data migration: what is it?
Show the answer
Moving data from one system to another (another database, another schema, another host), which ends with the old one being retired.
Unlike a warehouse refilled every night, a migration is a one-off: it has to be prepared, rehearsed and planned for failure.
The source table holds ids 1, 2, 3 and 4, the target 1, 2 and 4. What does this query return?
```sql
SELECT id FROM source
EXCEPT
SELECT id FROM cible;`31, 2, 41, 2, 3, 4- No rows
Show the answer
3EXCEPT returns the first query's rows that are missing from the second: only 3 is missing from the target. To see extra rows too, the query is run the other way round.
A … replays the whole migration on a copy of the data, to measure its duration and run the checks before touching production.
Show the answer
A dry run replays the whole migration on a copy of the data, to measure its duration and run the checks before touching production.
It reveals conversions that fail and the real duration, hence the downtime window to announce. It is repeated until the checks pass.
Schema mapping: what is it?
Show the answer
A document that matches each source column to a target column, with the destination type and the conversion rule.
This is where it is decided that a date stored as 'DD/MM/YYYY' text becomes a real DATE, or that an 'O' or 'N' becomes a boolean. Migration errors mostly come from unwritten conversions.