The short answer
For a warehouse, reporting database, data lake or high-volume operational database:
- InventoryEverything that reads or writes it, not just the data.
- Pick a patternOne that lets old and new run side by side.
- ReconcileCounts, totals and key reports, before switching.
- Cut overIn planned steps, with a tested rollback.
- DecommissionDeliberately, within retention and privacy obligations.
Skip inventory or reconciliation and a monthly report or downstream system quietly breaks after cutover.
Step 1: build an inventory
Data sets
Databases, schemas, tables, files: size, growth, owner.
Consumers
Reports, dashboards, notebooks, apps, exports, scheduled jobs reading it.
Producers
Applications, integrations and ETL jobs writing it.
Sensitivity
Personal, health, financial or confidential data sets.
Retention
How long each data set must be kept, and why.
Usage
What is really used; retire what nobody needs.
Discovery tools help: AWS describes DMS Fleet Advisor as building an inventory of servers, databases and schemas that could be migrated (AWS). Tools find databases; only the business knows which reports matter.
Step 2: choose a migration pattern
| Pattern | How it works | Suits |
|---|---|---|
| Big bang | Copy everything, switch everyone at once | Smaller platforms, tolerant users, a long quiet window |
| Phased by domain | One subject area at a time (finance data, then sales data) | Large platforms with clear domains |
| Parallel run with ongoing replication | Old and new stay in sync while consumers move gradually | Platforms that cannot tolerate downtime |
Parallel running needs change replication. AWS Database Migration Service supports one-time migrations and ongoing replication (AWS); other cloud providers and database vendors offer comparable tools.
Step 3: decide what changes and what does not
Adding redesign (new models, tools, naming) makes reconciliation harder: differences may come from the redesign, not errors. Migrate like for like first, then improve, or redesign one domain at a time against the old platform. Document every intended difference so nobody mistakes it for a defect.
Step 4: reconcile before you switch
Agree acceptance tests with the people who use the data, and automate them to rerun through the parallel period:
- Row counts per table and load
- Key totals (revenue, claims, quantities) by period
- Critical reports from both platforms
- Record spot checks chosen by users
- Slowest important queries
Step 5: plan the cutover
List every step, owner, duration and how to reverse it:
- FreezeNo schema changes before cutover.
- Final syncLast synchronization and reconciliation.
- RepointReports, applications and integrations.
- CommunicateFollow the user communication plan.
Privacy, residency and security
- Personal information stays subject to PIPEDA or applicable provincial laws throughout, including temporary copies and test environments (OPC).
- Cross-border processing is generally permitted under PIPEDA, but the organization remains accountable, should use contractual and other means to provide comparable protection, and should be open with individuals (OPC guidelines).
- Canadian regions: AWS, Microsoft Azure and Google Cloud all have them; they support residency, but check backups, replicas, logs and support access.
- Encryption and access: encrypt in transit and at rest, restrict migration tooling, delete temporary copies.
General information, not legal advice.
Controlling cost
Large migrations can surprise finance. Watch for:
- Two platforms during the parallel run
- Egress charges leaving a cloud or data centre
- Targets sized for peak, not normal load
- Storing data that should be archived or deleted
Set a planned end date for the parallel run and track it.
A worked example
Hypothetical example. A group benefits provider's on-premises reporting database feeds finance reports, sponsor statements and an analytics team. The hardware is ageing and nightly loads now overlap the working day.
A low-risk plan: inventory every report and job; move the data to a managed database in a Canadian cloud region with ongoing replication from the old platform; repoint the analytics team, then sponsor statements, then finance reports, reconciling each against the old platform; switch off the old database only after a full month-end has run cleanly on the new one.
Cutover checklist
- Inventory of data sets, consumers, producers, sensitivity and retention is complete.
- Migration pattern chosen and agreed with business owners.
- Intended differences between old and new are documented.
- Automated reconciliation checks run successfully on production-sized data.
- Critical reports compared and signed off by their owners.
- Cutover rehearsed, with timings recorded.
- Rollback trigger and procedure agreed and tested.
- Privacy review completed for the target location and temporary copies.
- End dates set for the parallel run and old-platform decommissioning.
Limitations
Some constraints are beyond this guide: vendor licence terms, proprietary formats, application code tied to one database engine, regulatory approvals. Some platforms cannot run in parallel at all; then careful rehearsal matters even more.
Next step
Our cloud services and migration work covers planning, migration and validation. For analytics platforms, see data analytics and business intelligence; for smaller system migrations, preparing your data for a CRM or ERP migration.
Sources and further reading
Product capabilities and guidance change. These are the primary sources this article relies on, checked on the review date above.
- What is AWS Database Migration Service?, Amazon Web Services
- Guidelines for processing personal data across borders, Office of the Privacy Commissioner of Canada
- PIPEDA in brief, Office of the Privacy Commissioner of Canada
This article is general information, not legal, accounting or security advice for your specific situation. Examples are hypothetical unless stated otherwise.