Why databases are the hard part
Application servers are mostly replaceable. Databases hold state, and state cannot be rebuilt from a pipeline. A database migration has to preserve every committed transaction, keep performance acceptable on the new platform, and give the business a way back if something is wrong. It also tends to be the longest-running piece of any wave, because the data has to be copied while the source keeps changing.
This is why databases are scheduled last within each wave of our migration roadmap, after the servers that use them are proven. The wider programme is described on the cloud migration service page.
What good looks like
- The source is fully understood: version, size, extensions, jobs, users, linked servers and peak write rates.
- The target is chosen for a reason, not by default.
- Replication runs for long enough to prove it keeps up, before the cutover date.
- Validation is automated and repeatable.
- The cutover has been rehearsed with a stopwatch, and the rollback has been rehearsed too.
Same engine or a different one
A migration from one version of an engine to the same engine on a managed service is mostly about data movement and parameters. A migration across engines, such as a commercial database to PostgreSQL, is a conversion project. Data types differ, stored procedures use different languages, and subtle behaviours such as how nulls, case and dates are handled can change results.
For cross-engine work, the AWS Schema Conversion Tool or the equivalent in the Azure tooling produces a first pass and an assessment of what it could not convert. The remaining work is manual and needs application tests, so the timeline should reflect that. We are candid when the business case for changing engine does not hold up. A same-engine move onto a managed service often delivers most of the operational benefit with much less risk, which is the replatform route in the 6Rs.
How continuous replication works
DMS runs in three phases. A full load copies existing data. Change data capture then reads the source’s transaction log and applies changes to the target, so the target catches up and stays current. At cutover you stop writes, let the remaining changes drain, and switch.
The source must be prepared for this. For PostgreSQL, logical decoding needs the right settings:
-- Source PostgreSQL: prerequisites for logical replication based CDC
-- (set in parameter group or postgresql.conf, then restart)
-- wal_level = logical
-- max_replication_slots >= number of tasks
-- max_wal_senders >= number of tasks
-- Create a dedicated replication user
CREATE ROLE dms_user WITH LOGIN REPLICATION PASSWORD :'dms_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dms_user;
Supply the password from a secrets manager at run time rather than typing it in a script. Also monitor replication slots: an abandoned slot makes the source retain write-ahead log and can fill its disk. That is a classic way for a migration to cause a production incident on the old system.
SQL Server and Oracle sources have their own prerequisites, such as supplemental logging or an appropriate recovery model. We check these during assessment, because some require a restart or a change window.
Validating the result
We validate at three levels. First, counts and checksums per table, using DMS validation where it supports the engine and independent queries where it does not. Second, schema parity: indexes, constraints, sequences and permissions. Sequences and identity values are a frequent miss, since a target that restarts at a low value causes duplicate key errors after cutover. Third, application behaviour: run the real test suite and, where possible, replay representative read traffic and compare responses and timings.
Performance deserves its own look. A query plan that was fast on the old engine can be slow on the new one, particularly after a cross-engine move. We capture slow queries from the source beforehand and compare.
Decisions and trade-offs
Managed service or self-managed. A managed database such as RDS, Aurora or Azure SQL removes patching and backup toil. It also removes some low-level control, and some extensions or settings may not be available. Check the features you rely on before committing.
DMS or native. Backup and restore is the simplest and most reliable method when a longer outage is acceptable. Replication is needed when it is not. Use the simplest method that meets the downtime requirement.
When not to migrate a database. A database with tight coupling to on-premises hardware, or a licence that makes a managed target uneconomic, may be better retained for now with applications moving around it, subject to the network path being suitable.
A worked example: sizing the cutover window
Suppose a rehearsal shows that stopping writes, draining replication and repointing the application takes a measurable number of minutes, and that the post-cutover checks take a further stretch. The announced window is then the sum of those timings plus a safety margin, not an optimistic guess. We also agree a point of no return in advance: if validation has not passed by that time, the team rolls back rather than debating it under pressure.
Common failure modes
- Replication lag at cutover that nobody measured under peak load.
- Large objects and unsupported types that migrate incorrectly or not at all.
- Missing sequence resets, leading to key collisions immediately after the switch.
- Forgotten dependants: reports, ETL jobs and linked servers still pointing at the old host. The dependency mapping catches most of these.
- Disk exhaustion on the source from retained log.
- No rollback data path, so going back means losing the writes made since cutover.
Delivery and handover
You get the migration runbook, the validation scripts and their results, the rehearsal timings and the parameter settings used on the target. Backups and recovery for the new database are configured and tested as part of handover, in line with our backup and disaster recovery practice. The final switch is coordinated with the DNS cutover plan.
How to tell it is working
Replication lag stays near zero under normal load. Validation reports show no unexplained differences. The rehearsal window matches the announced window. After cutover, error rates and query latencies sit within agreed bounds, and the old database is retained read-only for an agreed period before being retired. To scope a database migration, book a consultation.