Database migrations fail for predictable reasons: incomplete inventories, untested scripts, and cutover plans that assume everything will work the first time. This checklist is built from dozens of migrations we have executed across PostgreSQL, MySQL, SQL Server, and MongoDB. Print it out, pin it to the wall, and check off every item.
Phase 1: Assessment and Inventory
The assessment phase is where most teams cut corners and pay for it later. You cannot migrate what you have not documented. Spend the time here and the rest of the project gets dramatically easier.
- Document every table, view, materialized view, and their row counts
- Inventory all stored procedures, functions, and triggers with dependency maps
- Record all scheduled jobs, replication configurations, and backup routines
- Identify all applications and services that connect to the database
- Map connection strings, credentials, and access patterns for each consumer
- Measure current database size, growth rate, and peak query load
- Document character encoding, collation settings, and timezone handling
Phase 2: Schema Design and Mapping
If you are migrating between different database engines, this phase is critical. Data types do not map one-to-one across platforms. A MySQL DATETIME and a PostgreSQL TIMESTAMPTZ behave differently. An Oracle NUMBER and a SQL Server DECIMAL have different precision rules. Document every mapping decision and the reasoning behind it.
- Create a field-by-field mapping document between source and target schemas
- Identify data type differences that require transformation
- Plan for encoding conversions, especially for Unicode and special characters
- Design index strategy for the target platform based on actual query patterns
- Translate stored procedures and functions to the target dialect
- Define constraints, defaults, and sequences in the target schema
Phase 3: Migration Scripting and Dry Runs
Never run a migration script against production the first time. Build your scripts, test them against a copy of production data, and iterate until the output is perfect. Three dry runs is the minimum. Five is better.
- Build automated extraction scripts for the source database
- Build transformation scripts for data type conversions and cleansing
- Build load scripts for the target database with error handling
- Create checksum and row count validation queries for every table
- Run at least three full dry runs against a production-sized copy
- Measure execution time of each dry run and identify bottlenecks
- Test application connectivity against the target database
Phase 4: Cutover Execution
The cutover window is not the time for improvisation. Every step should be scripted, timed, and assigned to a specific person. Write a minute-by-minute runbook that includes decision points: at what threshold do you continue, and at what threshold do you roll back.
- Notify all stakeholders of the maintenance window
- Stop application writes to the source database
- Take a final backup of the source database
- Execute the migration scripts
- Run automated validation: row counts, checksums, and sample data comparison
- Update application connection strings to the target database
- Verify application functionality with smoke tests
- Monitor error rates and query performance for the first hour
Phase 5: Post-Migration Validation
The migration is not done when the data is in the new database. It is done when every application is running normally, performance is meeting baseline expectations, and your team has confirmed that reports, exports, and integrations are producing correct results. Keep the source database available in read-only mode for at least two weeks after cutover.
Set up monitoring dashboards that compare key metrics, query response times, error rates, and data freshness, between the old and new environments. Any anomaly in the first week should be investigated immediately, not triaged for next sprint.