The situation
We run six database estates on self-managed EC2 and a rack still sitting in a colo in ap-southeast-2. Three are Oracle Enterprise Edition (an order system, a billing system, and a reporting warehouse), two are SQL Server Standard behind a .NET line-of-business app, and one is a three-node MySQL cluster behind the customer-facing web tier. The Oracle and SQL Server licences renew in six months. Finance has asked, politely and then less politely, whether any of that renewal can be avoided.
The answer is “some of it, at very different cost”. MySQL to Aurora MySQL is close to a lift: the engine is the same, the SQL is the same, the application connection string barely changes. Oracle to Aurora PostgreSQL is a different animal. Every PL/SQL package, every trigger, every sequence, every proprietary data type has to be converted, tested, and in some cases hand-written. SQL Server to Aurora PostgreSQL sits in between, and there is a third path (Babelfish) that changes the maths entirely.
Two workloads can take a maintenance window; two cannot. The order system and the customer web tier are close to 24/7 and the business will tolerate minutes, not hours. Billing and reporting can go read-only over a weekend. That single fact, how much downtime each workload can absorb, decides more about the migration mechanics than the engine does.
What actually matters
The headline cost of a heterogeneous migration is not the data copy. Moving bytes is a solved problem; AWS Database Migration Service (DMS) will stream a terabyte across without much drama. The cost is schema and code conversion. When the source and target engines differ, every stored procedure, function, trigger, packaged view, and non-standard data type has to be translated, and the fraction that no tool can translate automatically becomes hand-written work that a person has to design, write, and test. That fraction, the manual action items, is the real budget line, and it is knowable up front from an assessment report rather than discovered halfway through.
Downtime tolerance is the second axis, and it decides the shape of the cutover. If a workload can go offline for the length of a bulk copy, a full-load-only migration is the simplest thing that works: freeze the source, copy everything, point the app at the target, done. If it cannot, the migration has to run change data capture (CDC) alongside, replicating live changes from the source while it stays in production, so the switch at the end is a matter of minutes. CDC is more moving parts, more source-side configuration, and a longer-lived replication task, and you take it on only when the downtime budget forces you to.
Then there is the application. A homogeneous move usually leaves the SQL untouched, so the app changes an endpoint and a password. A heterogeneous move can change the SQL the application sends, because dialects differ (identifier quoting, pagination syntax, date functions, sequence semantics), and any embedded SQL, ORM mapping, or stored-procedure call that assumed the old dialect is now a code change with its own test cycle. Converting the database and forgetting the application is the classic way to be surprised on cutover night.
Finally, cost and correctness of the pipe itself. The DMS replication instance is a managed EC2 box you size for throughput; too small and full load crawls, too large and you overpay for a task that runs for a week. And a migration is not done when the data lands; it is done when the data is proven right. DMS data validation compares source and target row by row after the load and reports mismatches, and skipping it means finding out about a truncated LOB or a rounded numeric in production.
What we’ll filter on
- Schema conversion effort: same engine (none), or different engine (assessment report, automatic plus manual action items).
- Application and SQL code change: unchanged, dialect-shimmed, or rewritten.
- Downtime tolerance: can the workload take a bulk-copy window, or does it need CDC to cut over in minutes?
- Data validation: is the load provably correct before the app points at it?
- Ongoing cost and operational weight: the replication instance, the duration of the task, and how much lives on after cutover.
The landscape
Homogeneous, native tooling. Same engine on both ends, so no schema conversion at all. For MySQL to Aurora MySQL you can restore from a Percona XtraBackup file straight into a new Aurora cluster, or use mysqldump for smaller sets; for PostgreSQL you use pg_dump/pg_restore; for a self-managed engine that Aurora offers wire-compatible, the schema and the SQL come across as-is. Native backup-and-restore is often the fastest full-load path because it bypasses the row-by-row machinery. It gives you a point-in-time copy and no ongoing replication, so on its own it fits workloads that can take a window.
Homogeneous, DMS. Same engine, but you want continuous replication so the source stays live during the copy and you cut over with near-zero downtime. DMS does a full load and then CDC, keeping the target in sync until you flip. You reach for this over native tools when the downtime budget is minutes rather than hours, or when you want DMS’s validation and monitoring even on a same-engine move.
Heterogeneous, schema conversion plus DMS. Different engines. The schema is converted first (AWS Schema Conversion Tool, or the DMS Schema Conversion feature in the console), which produces an assessment report and converts what it can, then DMS moves the data (full load, or full load plus CDC depending on downtime). This is the Oracle-to-Aurora-PostgreSQL and SQL-Server-to-Aurora-PostgreSQL path. The conversion effort is the project; the data movement is the easy tail.
Babelfish for Aurora PostgreSQL. A compatibility path specific to SQL Server. Babelfish is a translation layer built into Aurora PostgreSQL that understands the TDS wire protocol (port 1433) and a large subset of T-SQL, so a SQL Server application can point at Aurora and run its T-SQL largely unchanged. It shrinks the code-rewrite of a SQL-Server-to-PostgreSQL move from “rewrite every stored procedure” to “assess compatibility, fix the gaps”. You still move the data with DMS; Babelfish changes the schema-and-code side, not the copy.
Two supporting pieces sit under all of these. AWS Schema Conversion Tool (SCT) is the desktop application that connects to a source, generates the assessment report (how much converts automatically, what the manual action items are, roughly how much effort each is), and applies the converted schema to the target. DMS Schema Conversion is the same capability offered as a managed feature inside the DMS console with no desktop install, backed by an instance profile and metadata store. For OLTP fleets like this one either produces the report; SCT’s desktop data-extraction agents matter mainly for large warehouse migrations into Redshift, which is not what we have here.
Evaluation
Side by side
| Approach | Schema conversion | App / SQL change | Downtime | Data validation | Fits |
|---|---|---|---|---|---|
| Homogeneous, native | None | None | Window (bulk copy) | Manual / checksum | Same-engine, can take a window |
| Homogeneous, DMS | None | None | Near-zero (CDC) | ✓ built-in | Same-engine, minutes of downtime |
| Heterogeneous, SCT + DMS | Report + auto + manual | Dialect / rewrite | Window or near-zero | ✓ built-in | Oracle/SQL Server → Aurora PostgreSQL |
| Babelfish (SQL Server) | Compatibility assessment | T-SQL mostly unchanged | Window or near-zero | ✓ (DMS moves data) | SQL Server → Aurora PostgreSQL, keep T-SQL |
The decision, drawn
The solution
How SCT reads the schema before you commit. Point SCT (or DMS Schema Conversion) at the Oracle source and it produces an assessment report: how many schema objects convert automatically, how many need manual attention, and an effort estimate for each action item. This is the single most useful artefact in the whole project, because it turns “we’re moving off Oracle, roughly six months of work” into a counted list. A report that says 92% of objects convert automatically and the manual items are twelve PL/SQL packages using autonomous transactions is a plannable project. One that says 40% manual because the schema leans on Oracle-specific features throughout is a signal to reconsider scope, or to consider whether Babelfish (for the SQL Server side) or a partial rewrite is cheaper than a full conversion. Read the report before you promise a date.
The report classifies action items by effort (simple, medium, significant). Simple items are things SCT rewrites with a mechanical rule; significant items are the ones with no clean target equivalent, where a person designs the replacement. Oracle sequences, packages, autonomous transactions, hierarchical CONNECT BY queries, and proprietary functions are common significant items on the way to PostgreSQL. Budget the manual items as real engineering, with tests, not as a find-and-replace afternoon.
Full load, CDC, and why the pairing exists. A DMS task is one of three types. Full load copies existing data once and stops. CDC-only replicates ongoing changes and assumes the bulk data is already there. Full load plus CDC does both: it bulk-copies the existing rows, and while that copy runs it also captures every change happening on the still-live source, then replays those changes so the target catches up and stays caught up until you cut over. That combination is what gives you near-zero downtime. The source keeps serving production through the entire copy, which for a terabyte might be many hours; when the target is in sync and lag is near zero, you stop the application briefly, let the last changes drain, point the app at the target, and you are done in minutes.
CDC is not free to switch on. DMS reads changes from the source’s transaction log, so the source has to be configured to keep those logs available: Oracle needs ARCHIVELOG mode and supplemental logging enabled; SQL Server needs its transaction logs (or MS-CDC) accessible to the replication user; MySQL needs binary logging in ROW format with a sufficient retention window. Miss that configuration and the full load succeeds while CDC silently has nothing to read. Turn CDC on only for the workloads whose downtime budget demands it (here, the order system and the web tier); for billing and reporting, which can go read-only over a weekend, full-load-only is simpler and there is nothing to keep in sync.
Sizing the replication instance, and DMS Serverless. The replication instance is a managed EC2 box that does the actual work, and it is the throughput bottleneck. Too small and full load of a large table crawls and CDC lag grows faster than it drains; too large and you pay for a fat instance for a task that runs a week and is then deleted. Memory matters most, because DMS caches transactions in memory during CDC. For a fleet like this, provision generously for the full-load phase, then consider a smaller class if a long CDC tail runs before cutover. DMS Serverless is the alternative: you declare a capacity range in DMS capacity units and it provisions and scales the replication capacity for you, which suits bursty or hard-to-size migrations and saves you guessing the instance class, at the cost of less direct control.
Validation is part of the migration, not a nicety. Enable DMS data validation on the task and, after the full load, it compares source and target row by row and reports rows that do not match. This is where truncated LOBs, rounded numerics, character-set mismatches, and timezone-shifted timestamps surface. On a heterogeneous move it is the difference between “the data is in PostgreSQL” and “the data in PostgreSQL is the same data that was in Oracle”. Run a DMS premigration assessment first, too: it checks the task configuration before you start and flags problems like tables without a primary key (which CDC needs), unsupported data types, and source settings that will break replication, so you find them in an assessment rather than three hours into a load.
LOBs, the quiet throughput killer. Large objects (BLOBs, CLOBs, TEXT, VARBINARY(MAX)) are handled in a separate phase because DMS cannot know their size in advance. There are three modes. Full LOB mode migrates every LOB whole, correct but slow, and it requires the target table to have a primary key. Limited LOB mode is much faster: you set a maximum size and DMS copies LOBs up to that size and truncates anything larger, which is fine if you know your ceiling and dangerous if you guess low. Inline LOB mode is a middle path that moves small LOBs inline with the row and the large ones separately. Pick limited LOB with a max size you have actually measured against the source, and let validation confirm nothing was truncated.
The gotchas that bite on heterogeneous cutover. Sequences do not carry their current value across automatically; after the load you reset each target sequence to max(id) + 1 or the next insert collides. Identity and auto-increment columns have the same trap. Data types without a clean target equivalent (Oracle NUMBER with no precision, proprietary spatial or XML types, SQL Server DATETIME2 edge cases) need an explicit mapping decided during schema conversion, not left to a default. Default values and check constraints expressed in the source dialect may not convert. And foreign keys and triggers on the target should be disabled during full load and re-enabled after, or the load fights the constraints row by row; DMS does this for you when it manages the target schema, but confirm it if you pre-created the schema by hand.
Where Babelfish changes the sum. For the two SQL Server instances, the default heterogeneous path is SCT-convert every T-SQL stored procedure to PL/pgSQL, then rewrite the .NET data layer for PostgreSQL dialect and driver. Babelfish collapses most of that. You stand up an Aurora PostgreSQL cluster with Babelfish enabled, run the Babelfish Compass tool against the SQL Server code to get a compatibility report, fix the unsupported constructs, and the application connects over TDS on 1433 and runs its T-SQL largely as written. Data still moves with DMS. It is not total: some T-SQL features are unsupported and become the manual list, and Compass tells you which up front, the same way SCT’s report does for a full conversion. When the report comes back mostly green, Babelfish turns a rewrite into a compatibility exercise and can save the SQL Server workloads months.
Worked example
MySQL cluster → Aurora MySQL. Same engine, so no schema conversion and no application SQL change; the connection string and credentials change and little else. The web tier cannot take a long outage, so run DMS full load plus CDC: bulk-copy the cluster into Aurora MySQL while it stays live, let CDC keep the Aurora cluster in sync, validate, then cut the app over during a low-traffic window with a few minutes of drain. Alternatively, restore from an XtraBackup snapshot for the initial load if the copy window is tight, then attach CDC from the snapshot’s binlog position. Effort: low. This is the one finance gets for almost free.
Oracle order system → Aurora PostgreSQL. Heterogeneous, and it is close to 24/7, so this is the full project. Run SCT to get the assessment report first, and let the manual action item count set the timeline. Convert the schema, hand-write the significant PL/SQL items as PL/pgSQL with their own tests, update the application’s embedded SQL and sequence handling, then DMS full load plus CDC so the cutover is minutes. Configure ARCHIVELOG and supplemental logging on the Oracle source before CDC. Reset sequences on the target immediately before cutover. Validate with DMS data validation and reconcile any mismatches before the app points at Aurora. Effort: high, and it is the workload that justifies starting now rather than at renewal minus one month.
Oracle billing and reporting → Aurora PostgreSQL. Heterogeneous like the order system, but both can go read-only over a weekend, which removes CDC from the plan. Convert with SCT, then DMS full-load-only inside the maintenance window: freeze the source, load, validate, cut over. No transaction-log configuration, no long-lived replication task, no sync to babysit. The conversion effort is still real (these are the reporting warehouse’s proprietary functions and the billing system’s packages), but the cutover mechanics are the simple ones because the downtime budget allowed it.
SQL Server pair → Aurora PostgreSQL, via Babelfish. Run Babelfish Compass against both instances’ T-SQL first. If the report is mostly compatible, stand up Aurora PostgreSQL with Babelfish, fix the flagged constructs, keep the .NET app’s T-SQL, and move data with DMS (full-load-only if the app can take a window, full load plus CDC if it cannot). If Compass comes back heavily incompatible, fall back to the SCT-plus-rewrite path and price it accordingly. Either way the assessment report is the gate: read it before committing the SQL Server workloads to a plan.
What’s worth remembering
- Homogeneous and heterogeneous migrations use the same two services but are different projects; same-engine moves skip schema conversion entirely, and different-engine moves make conversion the whole job.
- The assessment report (SCT or DMS Schema Conversion) is the artefact the plan rests on: it counts automatic conversions against manual action items and turns a vague migration into a plannable one. Read it before you promise a date.
- Downtime tolerance picks the cutover. Full-load-only for workloads that can take a window; full load plus CDC for workloads that need to cut over in minutes while staying live.
- CDC needs source-side configuration: Oracle ARCHIVELOG plus supplemental logging, SQL Server accessible transaction logs, MySQL row-format binlog. Miss it and full load works while CDC reads nothing.
- Babelfish for Aurora PostgreSQL lets a SQL Server application keep its T-SQL, turning a full rewrite into a compatibility exercise; run Babelfish Compass first, and DMS still moves the data.
- Convert the database and you are half done; the application’s embedded SQL, sequence handling, and dialect assumptions are the other half, and they have their own test cycle.