Moving Your On-Premises Database to the Cloud

The decision to migrate an on-premises database to the cloud is rarely a simple one. It touches infrastructure, budget, team workflows, and application uptime all at once. Yet for most organizations, moving your on-premises database to the cloud is no longer a question of if but when — and more importantly, how to do it without bringing production systems to their knees. Whether you're running PostgreSQL on bare metal or SQL Server in a company-owned data center, the path to cloud-hosted data has predictable pitfalls and, when navigated carefully, significant payoffs.

This guide walks through the full migration process: from pre-migration assessment to post-migration validation, with real configuration examples and the kind of hard-won detail that vendor documentation tends to skip.

Why Organizations Make the Move

On-premises databases carry hidden costs that compound over time. Hardware refresh cycles, licensing renewals, physical security, power, and cooling — these expenses are easy to underestimate when they're already baked into the operating budget. Cloud databases shift that burden to a consumption model, where you pay for what you use and scale without a procurement cycle.

Beyond cost, there's the question of availability. Cloud providers like AWS, Google Cloud, and Azure run managed database services with built-in replication, automated failover, and point-in-time recovery that would take a dedicated DBA weeks to configure manually. A startup can get enterprise-grade durability out of the box. A mid-sized company can stop worrying about what happens when a hard drive fails at 2 a.m.

Performance is another draw — though it comes with caveats. Managed cloud databases excel at read-heavy workloads and benefit from proximity to cloud-native application services. Latency-sensitive operations or highly custom storage configurations sometimes perform better on dedicated hardware. Knowing which category your workload falls into is the first real question to answer.

Pre-Migration Assessment: Know Before You Move

Skipping the assessment phase is the single biggest mistake teams make. It leads to unexpected downtime, missed dependencies, and — in the worst cases — data loss. A thorough pre-migration audit covers four areas: data volume, schema complexity, application dependencies, and compliance requirements.

Start with a size inventory. You need to know not just the total database size but the distribution across tables, the presence of large object (LOB) columns, and historical growth rates. A 500 GB database that grows 10 GB per week needs a different migration strategy than one that's been static for two years.

Schema complexity matters because cloud databases don't always support every feature of their on-premises equivalents. Oracle-to-PostgreSQL migrations, for example, frequently surface issues around stored procedures, custom data types, and sequences. AWS Schema Conversion Tool (SCT) and pgloader are useful here, but they produce conversion reports, not magic. You'll still need a developer to review the flagged objects.

Application dependencies are where migrations often stall. Query your connection pool configurations, hardcoded IP addresses, and any application code that references the database hostname directly. A migration that takes two days of database work can turn into two weeks when four applications need their connection strings updated and tested.

Choosing a Migration Strategy

There are three broad approaches to database migration: lift-and-shift, heterogeneous migration, and phased cutover. Each has a different risk profile and time-to-value.

Lift-and-Shift

Lift-and-shift moves the database engine and data as-is to a cloud VM, without changing the database software or schema. It's the fastest path and produces the fewest surprises, but it doesn't take advantage of managed services. You're still responsible for patching, backups, and HA configuration.

This strategy makes sense when deadlines are tight, when the application is tightly coupled to a specific database version, or when the migration is a step toward eventual modernization rather than the destination.

Heterogeneous Migration

A heterogeneous migration moves data between different database engines — Oracle to Aurora PostgreSQL, SQL Server to Cloud Spanner, MySQL to AlloyDB. This is the most complex and highest-reward approach. You get schema compatibility improvements, cost reductions from license elimination, and cloud-native performance features. But it requires rigorous application testing because even minor behavioral differences between SQL dialects can cause subtle bugs.

AWS Database Migration Service (DMS) handles the data movement portion, while SCT handles schema conversion. Here's a minimal DMS replication task configuration for a continuous replication job:

{
  "TargetMetadata": {
    "TargetSchema": "",
    "SupportLobs": true,
    "FullLobMode": false,
    "LobChunkSize": 64,
    "LimitedSizeLobMode": true,
    "LobMaxSize": 32768
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DROP_AND_CREATE",
    "CreatePkAfterFullLoad": false,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 8,
    "TransactionConsistencyTimeout": 600,
    "CommitRate": 50000
  },
  "Logging": {
    "EnableLogging": true,
    "LogComponents": [
      { "Id": "SOURCE_UNLOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
      { "Id": "TARGET_LOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
      { "Id": "TASK_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }
    ]
  }
}

The FullLobMode: false setting combined with LimitedSizeLobMode: true is a common performance optimization — it handles most LOB columns efficiently while avoiding the full-scan overhead that comes from treating all LOBs as potentially massive.

Phased Cutover

Phased cutover uses change data capture (CDC) to keep the source and target databases synchronized while the application continues running. Once the target is caught up and validated, traffic is switched over with minimal downtime — often under a minute.

This is the preferred approach for production systems where extended downtime isn't acceptable. The tradeoff is complexity: you're running two databases in parallel, which requires careful monitoring and a clear rollback plan.

Setting Up Change Data Capture

CDC works by reading the database transaction log and replicating changes to the target in near real-time. PostgreSQL exposes this via logical replication; MySQL uses binary log (binlog) replication; SQL Server uses CDC tables backed by the transaction log.

For PostgreSQL-to-PostgreSQL migrations, you can set up logical replication with a publication and subscription:

-- On the source database
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();

CREATE PUBLICATION migration_pub FOR ALL TABLES;

-- On the target database
CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=source-db.internal port=5432 dbname=myapp user=replicator password=secret'
  PUBLICATION migration_pub;

Once the subscription is active, PostgreSQL streams all inserts, updates, and deletes from source to target. You can monitor lag with pg_stat_subscription:

SELECT subname, received_lsn, latest_end_lsn,
       (latest_end_lsn - received_lsn) AS lag_bytes
FROM pg_stat_subscription;

A lag of zero bytes means the target is caught up. That's your signal that the cutover window is ready.

Cutover Execution and Rollback Planning

The actual cutover has a predictable sequence: stop writes to the source, confirm the target has caught up, update connection strings, verify application connectivity, then remove the replication slot. Each step should be documented with a time estimate and a rollback step.

What many migration guides leave out is the importance of a tested rollback. Before executing the cutover, run a dry run in staging that includes the rollback path. Can you repoint applications back to the source database in under five minutes? Is there a runbook that anyone on the team can follow at midnight? These aren't hypothetical questions — they're the difference between a controlled incident and a crisis.

Communication matters too. Coordinate the maintenance window with product teams and customer support. Even a migration with near-zero downtime should have a status page entry and a prepared message for users in case something unexpected occurs.

Post-Migration Validation

A migration isn't done when the data lands in the cloud. It's done when you've confirmed the data is correct and the application behaves identically. Row count checks are the minimum — not the finish line.

Run a checksum comparison on critical tables:

-- Run on both source and target; checksums should match
SELECT MD5(CAST((array_agg(t.* ORDER BY id)) AS TEXT))
FROM your_table t;

Beyond checksums, replay a sample of recent production queries against the target and compare execution plans. Query planner behavior can differ between environments — particularly if the cloud database was initialized without running ANALYZE or if index statistics are stale.

Monitor application error rates and slow query logs for the first 48 hours post-migration. This window captures most regression issues before they compound into bigger problems. Set up alerting on database CPU, connection counts, and replication lag (if you've kept the source running as a fallback).

Conclusion

Migrating an on-premises database to the cloud is a project with real complexity — but it's entirely manageable when you work through it systematically. Assess before you plan, plan before you execute, and validate before you celebrate. The teams that have the smoothest migrations are almost always the ones that invested the most time in the pre-migration phase, built a tested rollback, and resisted the urge to rush the cutover.

If you're starting this process, begin with a data inventory and a dependency audit this week. Even if the migration is months away, that groundwork shapes every decision that follows. The cloud isn't a destination you arrive at — it's a platform you migrate to with intention.