Seven common mistakes when migrating an on-premises database to the cloud

A good migration is quiet. The dump restores, replication catches up, the application points at a new endpoint, and nothing happens — which is exactly the result everyone wanted. The migrations that go badly rarely fail at the technical copy. They fail because a column behaves differently, because nobody modelled the invoice, or because the reverse path out of the cloud turns out not to exist.

None of the seven mistakes below are exotic. Most are visible a fortnight before cutover, if anyone looks — and each has a fix that costs far less than the incident it prevents.

1. Treating the migration as a copy rather than a translation

Lift and shift sounds reassuring: same tables, same rows, different home. Engines do not agree on as much as you would hope. A Postgres serial column and an identity column behave differently at the edges. Oracle's NUMBER semantics do not map cleanly onto numeric. Older MySQL character sets cannot hold the same strings as utf8mb4, and index key limits quietly change what fits. Case-insensitive collations, empty strings that arrive as nulls, and different default isolation levels are all places where a comfortable staging test hides a production surprise.

Before cutover, produce a written comparison of types, nullability, defaults, indexes, constraints and collations. Give every difference an owner and a decision. Matching row counts is a starting point, not a finish line.

2. Letting schema drift creep in

Drift is what turns a manageable incident into a long night. It usually starts innocently: a column added by hand in production during an outage, a hotfix index that never made it into the migration folder, a test environment rebuilt from a different branch. Two versions of the truth now exist, and your rollback plan assumes one of them.

• Apply every schema change through versioned migrations in a pipeline — Flyway, Liquibase, Alembic, Sqitch — and never by hand.
• Record checksums so an edited migration is rejected rather than silently reapplied.
• Run a nightly schema diff against production and alert on anything unexpected, including new grants.
• Remove DDL rights from application roles in the cloud account, so drift has to go through the front door.

Then snapshot the schema at cutover and freeze changes for a defined window. A frozen schema is a rollback you can trust.

3. Not pricing the running cost properly

The compute line is the easy one, and it is rarely the one that causes trouble. What bites is everything around it: data crossing availability zones, egress when analytics pulls a copy, provisioned IOPS, storage that autoscales without asking, snapshot and log retention, read replicas that were only meant to be temporary, and non-production environments left running overnight and at weekends. If you are bringing your own licences, check what the cloud provider's terms actually permit before you migrate.

Build the estimate, then double the storage and data-transfer lines and ask whether the business case still stands. A parallel run is the honest test: for a while, both estates are live and both are on the invoice. Tag resources from day one and review the bill in week two, not month three.

4. A rollback plan that exists only on paper

"Restore the backup" is not a rollback. If the application has written to the new database for six hours, those writes need to come back with it — and they will not, unless you have built a path for them. You have three realistic options: reverse replication into the old system, a dual-write period with a single declared source of truth, or an explicit decision to roll forward only.

What a usable plan contains

A trigger (error rate above a threshold, a failed data check, latency the business will not accept), a named person who can call it, and a time limit after which rollback stops being sensible. Test the reverse path at least once with real volumes. Rolling back a schema change is harder than rolling back code, so keep the old database untouched, reachable and sized to take the load again for as long as the plan claims.

5. Skipping the rehearsal and cutting over at the wrong moment

Rehearse with production-scale data, anonymised where it needs to be. Time every step honestly: export, restore, index rebuild, replication catch-up, application warm-up, cache priming. If the DNS record has a long TTL, the cutover is not finished when the switch flips — it is finished a day later, and your rollback window has to account for that.

Run the rehearsal twice. The second run exposes what the first one hid, usually in the ordering of steps. Then choose the window with the business, not only with the on-call rota: avoid month-end, payroll runs and public holidays with no support cover.

6. Assuming performance will look after itself

Matching vCPU and memory does not mean matching performance. Managed platforms arrive with their own defaults for connection limits, autovacuum behaviour, statistics targets and buffer sizes, and some of those you cannot change. Storage has an IOPS ceiling that the migration's index rebuilds will find before your users do.

The network is now in the path between application and database. Queries that were fine on a local network cross an availability zone, and a connection-per-request pattern that was tolerable becomes a bottleneck. Load-test with the real query mix, add a pooler such as PgBouncer or a managed proxy, and read latency percentiles rather than averages.

7. Leaving security, access and day-two ownership until the end

The final week is when shortcuts happen: a database left publicly reachable "temporarily", a superuser password in a configuration file, backups that are encrypted but never restored, audit logging switched off because it was noisy, a service account with rights nobody can explain. All of it is cheaper to fix before cutover than after.

Decide ownership at the same time. Who patches, who tunes, who restores, who answers at three in the morning, and where the runbook lives. Handovers are quieter than cutovers, and they are usually the reason a migration still feels unfinished months later. If the database holds regulated or contractual data, get sign-off from whoever owns compliance before the switch, not afterwards.

A short pre-cutover checklist

1. Schema comparison signed off, with every difference decided and documented.
2. Migrations frozen, checksums verified, drift detection running.
3. Rollback trigger, owner and time limit agreed and tested.
4. Cost estimate reviewed, including a parallel run and the transfer lines.
5. Restore test completed and the timing written down.
6. Monitoring and alerts pointed at the new endpoint before the first user arrives.

Then schedule your own checks: one hour after cutover, one day after, and two weeks later when the first full bill lands. Most migrations are not saved by heroics at 2 a.m. They are saved by a fortnight of unglamorous preparation.

Photo: Peggy_Marco / Pixabay