Migrating a Heterogeneous Database Fleet with DMS and SCT

The situation

We run six databases, some on self-managed EC2 in ap-southeast-2 and the rest on a rack still sitting in a Sydney colo. 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.

Some of it can, and the cost differs sharply by workload. MySQL to Aurora MySQL is close to a lift: the engine is the same, the SQL is the same, and the application changes a connection string. Oracle to Aurora PostgreSQL is a different job. Every PL/SQL package, trigger, sequence and proprietary data type has to be converted, tested, and in places hand-written. SQL Server to Aurora PostgreSQL sits in between, and Babelfish changes it again.

Five workloads, and three can take a window. Billing and the reporting warehouse go read-only over a weekend, and the internal .NET app is quiet all Saturday. The order system and the customer web tier run close to 24/7, and the business will tolerate minutes. How much downtime each workload absorbs decides more about the migration mechanics than the engine does.

What actually matters

Schema and code conversion is where a heterogeneous migration spends its money. Moving bytes is close to a solved problem, and AWS Database Migration Service (DMS) streams a terabyte across without much trouble. When the engines differ, every stored procedure, function, trigger, packaged view and non-standard data type has to be translated, and whatever the tooling cannot translate becomes hand-written work with its own design and test cycle. That residue is knowable up front. An assessment report counts it and sorts it by effort: simple items take under two hours, medium items two to six, significant items more than six.

Downtime tolerance decides the shape of the cutover. A workload that can go offline for the length of a bulk copy takes a full-load-only migration: freeze the source, copy everything, point the app at the target. A workload that cannot needs change data capture (CDC) running alongside, replicating live changes while the source stays in production, so the switch at the end is minutes rather than hours. AWS publishes no latency SLA for DMS change data capture and says plainly that it is not real-time replication, so a cutover plan has to watch measured lag rather than assume it.

A homogeneous move leaves the SQL alone, so the app changes an endpoint and a password. A heterogeneous move changes the dialect: identifier quoting, pagination syntax, date functions, sequence semantics. Worse, a schema conversion converts server-side code. The SQL embedded in the .NET data layer and in the ORM mappings never lived in the database, so it has to be captured and assessed separately. Converting the database and forgetting the application is the classic way to be surprised on cutover night.

Then the pipe itself. DMS runs in a VPC, so the colo rack needs Direct Connect or a VPN, and its hostnames need resolving from that VPC. The replication instance is a managed EC2 box you size for throughput; AWS charges for its hours and any extra log storage, and inbound data transfer is free. A migration is also not finished when the data lands. DMS data validation compares source and target row by row and reports mismatches, and skipping it means a truncated LOB or a rounded numeric surfaces in production instead.

What we’ll filter on

1. Schema conversion effort: none on a same-engine move, or an assessment report with automatic conversions and manual action items when the engine changes.
2. Application and SQL change: unchanged, dialect-shimmed, or rewritten, server-side and client-side both.
3. Downtime tolerance: can the workload take a bulk-copy window, or does it need CDC to cut over in minutes?
4. Data validation: is the load provably correct before the app points at it?
5. Ongoing cost and operational weight: replication instance hours, the duration of the task, and what survives cutover.

The landscape

Homogeneous, native tooling. Same engine on both ends, so no schema conversion at all. For MySQL to Aurora MySQL, Percona XtraBackup full and incremental backup files land in an S3 bucket and Aurora restores a new cluster from them. AWS describes that as considerably faster than migrating with mysqldump. For PostgreSQL you use pg_dump and pg_restore. A native restore gives 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, which produces an assessment report and converts what it can, then DMS moves the data. 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, and available on Aurora PostgreSQL rather than RDS for PostgreSQL. Babelfish adds a second listener to the cluster that speaks the SQL Server Tabular Data Stream protocol, versions 7.1 through 7.4, and understands a large subset of T-SQL. The T-SQL dialect answers on port 1433 and the PostgreSQL dialect on 5432, on the same cluster. A SQL Server application can point at 1433 and run its T-SQL largely unchanged, which turns “rewrite every stored procedure” into “assess compatibility, fix the gaps”. You still move the data with DMS.

AWS Schema Conversion Tool (SCT) is the downloadable application. It connects to a source, generates the database migration assessment report, and applies the converted schema to the target. It also converts SQL embedded in C++, C#, Java and other application code, which DMS Schema Conversion does only for Java, by handing its project metadata to Amazon Q Developer in the IDE. SCT accepts a virtual target, so it can produce a Babelfish assessment report with no cluster to connect to. DMS Schema Conversion is the same conversion engine offered inside the DMS console with no install. Three resources make it up: an instance profile carrying the network and encryption settings, a data provider per database, and a migration project tying them together with the Secrets Manager secrets. Its rules engine converts first, and on the Oracle and SQL Server paths to Aurora PostgreSQL generative AI conversion takes another pass at what the rules leave behind. Both the feature and that pass are available in ap-southeast-2, and AWS charges nothing for the feature beyond S3 storage for exported scripts. SCT’s data-extraction agents matter for warehouse loads 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

What the assessment report says before you commit. Point SCT or DMS Schema Conversion at the Oracle source and it reports how many schema objects convert automatically, what the remaining action items are, and an effort band for each: simple under two hours, medium two to six, significant more than six. A report where nearly everything converts and the manual list is a dozen PL/SQL packages using autonomous transactions is a plannable project. One where the schema leans on Oracle-specific features throughout is a signal to reconsider scope, or to weigh a partial rewrite against a full conversion. Read the report before you promise a date.

Significant items are the ones with no clean target equivalent, where a person designs the replacement. Oracle packages, autonomous transactions and proprietary functions land there on the way to PostgreSQL. On the Oracle and SQL Server paths to Aurora PostgreSQL, generative AI conversion narrows that list without emptying it, and what it produces is still code someone has to read and test. Budget the remainder as real engineering, 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, captures every change happening on the still-live source while that runs, then replays those changes so the target catches up and stays caught up until you cut over. The source keeps serving production through the entire copy, which for a terabyte might be many hours. When lag is near zero, you stop the application briefly, let the last changes drain, and repoint it. Views come across on full-load-only tasks, and only where the selection rule asks for them, while a task with a CDC phase migrates tables alone. The reporting warehouse is thick with views, which is one more reason its weekend window is worth having.

What CDC needs on each source. DMS reads changes from the source’s transaction log, so each engine has to be configured to expose one. Oracle needs ARCHIVELOG mode and minimal supplemental logging at the database level. It also needs supplemental logging per replicated table: the primary key where there is one, the columns of a single unique index where there is not, and all columns where there is neither. LogMiner is the default reader, and Binary Reader is the alternative where redo volume is high. SQL Server needs full transaction logging, which means turning on MS-REPLICATION or MS-CDC, a recovery model of Full or Bulk logged, and a full backup taken before the first task runs. MySQL needs row-format binary logging with a retention window long enough to cover the load. Miss any of it and the full load succeeds while CDC has nothing to read. Turn CDC on only where the downtime budget demands it, which here means the order system and the web tier.

Sizing the replication instance, and DMS Serverless. The replication instance does the actual work and it is the throughput bottleneck. Too small and a large table’s full load crawls while 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. Most of the processing happens in memory, with only large transactions, cached changes and logs spilling to disk, so memory is the dimension to size for first. AWS recommends Multi-AZ for anything doing ongoing replication, with one wrinkle: a failover during a full load fails the task, and you restart the tables that did not finish. DMS Serverless is the alternative. You set a minimum and a maximum in DMS capacity units, one DCU being 2 GB of RAM, and DMS calculates the capacity load, provisions it and scales between them, which suits a migration nobody can size confidently. Serverless does not migrate views and does not take a custom CDC start point, so the warehouse load and any restart from a binlog position stay on a provisioned instance.

Validation, and the assessment that runs before it. Enable data validation on the task and it starts comparing as soon as each table’s full load finishes, then revalidates CDC changes as they arrive. It needs a primary key or unique index on every table it checks, it does not validate views, and it stops once it finds more than 10,000 failed or suspended rows. This is where truncated LOBs, rounded numerics, character-set mismatches and timezone-shifted timestamps surface. Run a premigration assessment first as well. It evaluates the task configuration against the source and target, flagging unsupported data types, missing supplemental logging, and primary keys validation cannot use. Those turn up in a report rather than three hours into a load.

LOBs, and where the throughput goes. DMS writes each row first and then updates it with the LOB value. Every LOB column on the target therefore has to be nullable while the task runs, even where the source column is not. DMS sets that itself on tables it creates; on a schema SCT pre-created, check it. There are three modes. Full LOB mode moves every LOB whole, in chunks, and is slow. Limited LOB mode pre-allocates memory against a maximum size you declare, which is much faster, and truncates anything above it with a warning in the task log. The default is 32 KB and the ceiling is 100 MB. Inline LOB mode moves small LOBs with the row and falls back to full LOB mode for the rest. On an Oracle source, DMS fetches LOBs as VARCHAR where it can, and an Oracle VARCHAR stops at 32 K, so a limited size under that is the fast path. Measure the largest LOB per table with dbms_lob.getlength instead of guessing. The primary key requirement depends on the mode. CDC uses a primary key or unique index to look up LOB values, so every table with a LOB column needs one. A full-load-only task in limited LOB mode does not, but full LOB mode and inline LOB mode need one even on a full load alone.

The gotchas that bite on heterogeneous cutover. DMS creates tables and primary keys and nothing else: no secondary indexes, no foreign keys, no triggers, no users. For a full-load task, drop or delay primary key and secondary indexes, referential integrity constraints and DML triggers, because the load fills groups of tables at a time and constraints get violated mid-load. For full load plus CDC, put the secondary indexes back before the CDC phase, since applying changes without them means full table scans, and turn the triggers on just before cutover. Sequences do not carry their current value across either; reset each target sequence to max(id) + 1 or the next insert collides, and identity and auto-increment columns have the same trap. Oracle NUMBER with no precision, proprietary spatial and XML types, and DATETIME2 edge cases all need an explicit mapping decided during conversion rather than left to a default.

Where Babelfish changes the sum. The default path for the two SQL Server instances is to convert every T-SQL stored procedure to PL/pgSQL, then rewrite the .NET data layer for the PostgreSQL dialect and driver. Babelfish collapses most of that. Stand up an Aurora PostgreSQL cluster with Babelfish turned on, run Babelfish Compass over the exported DDL, fix what it flags, and the app connects over TDS on 1433 and runs its T-SQL largely as written. Compass is open source and lives on GitHub rather than in the console, and bugs in it go there instead of to AWS Support. Compass reads server-side DDL, so capture the app’s client-side queries with a SQL Server Profiler TSQL_Replay trace or extended events and run those through it too. Two mechanics matter on the way in. Create the schemas, user-defined types and tables with their primary keys in T-SQL first, load the data, then add the constraints, triggers, indexes, views and procedures. And for continuous replication, point DMS at the cluster’s Aurora PostgreSQL endpoint rather than the Babelfish one. Some T-SQL is unsupported and becomes the manual list, and Compass names it up front, the way SCT’s report does for a full conversion.

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 Aurora in sync, validate, then cut the app over in a low-traffic window with a few minutes of drain. Alternatively, restore a new cluster from XtraBackup files in S3 for the initial load, then start a CDC-only task from a native start point, which for MySQL is the binlog file name and position together. Effort: low, and it is the workload finance sees the licence saving on first.

Oracle order system → Aurora PostgreSQL. Heterogeneous, and close to 24/7, so this is the full project. Run the assessment first and let the 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 run DMS full load plus CDC so the cutover is minutes. Configure ARCHIVELOG and supplemental logging on the source before CDC, and add the secondary indexes back between the full load and the CDC phase. Reset sequences immediately before cutover. Validate and reconcile mismatches before the app points at Aurora. Effort: high, which is why this one starts now rather than at renewal minus a month.

Oracle billing and reporting → Aurora PostgreSQL. Heterogeneous like the order system, but both go read-only over a weekend, which takes CDC out of the plan. Convert with SCT, then run full-load-only inside the window: freeze the source, load, validate, cut over. No transaction-log configuration, no long-lived replication task, nothing to keep in sync, and the warehouse’s views come across because the task has no CDC phase. The conversion effort is still real, since these hold the warehouse’s proprietary functions and the billing system’s packages, but the cutover mechanics are the simple ones.

SQL Server pair → Aurora PostgreSQL, via Babelfish. Run Compass over both instances’ DDL and captured client queries 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 the data with DMS full-load-only inside the Saturday window. If Compass comes back heavily incompatible, fall back to converting the T-SQL and rewriting the data layer, and price it accordingly. Either way the report is the gate: read it before committing these two to a plan.

What’s worth remembering

1. Conversion effort is the migration. Same-engine moves skip schema conversion; different-engine moves make it the job, and data movement the easy tail.
2. Read the assessment report first. It bands manual items: simple under two hours, medium two to six, significant more than six.
3. Downtime tolerance picks the cutover. Full load only where a window exists; full load plus CDC for minutes; AWS publishes no CDC latency SLA.
4. CDC needs source logging. Oracle needs ARCHIVELOG plus supplemental logging, SQL Server MS-REPLICATION or MS-CDC, MySQL row-format binlog; otherwise CDC reads nothing.
5. DMS creates only tables and keys. Secondary indexes, foreign keys and triggers are yours; placed wrongly, loads fail or CDC scans full tables.
6. Babelfish keeps T-SQL on port 1433. Run Compass over server-side and client-side SQL; point DMS at the Aurora PostgreSQL endpoint, not Babelfish’s.