AWS DMS, SCT, and Change Data Capture

Data movement versus schema conversion

SAP-C02 Task 4.2 lists AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT) as the database migration tools. They are a pair, not synonyms. DMS is a managed replication engine: a replication instance (or serverless replication) reads a source endpoint and writes a target endpoint. AWS documents that either the source or the target (or both) must reside on Amazon RDS or on Amazon EC2; on-premises-to-on-premises replication is not supported. SCT is a project-based converter you install locally (DMS Schema Conversion is the console-hosted relative). SCT assesses and, where possible, converts schema and code so a different engine can host the application.

Keep the split ruthless in your head:

• DMS copies table data and applies ongoing row changes.
• SCT / DMS Schema Conversion convert tables, views, procedures, and related code when a mapping exists, then tell you what they could not convert.

Starting a DMS task does not “turn Oracle into PostgreSQL.” Creating an SCT project does not move 50 TB of rows.

Homogeneous versus heterogeneous

Homogeneous means the same engine family: Oracle to Amazon RDS for Oracle, SQL Server to RDS for SQL Server, MySQL to Aurora MySQL-Compatible. Schema objects can often travel with native tools (Oracle Data Pump, SQL Server backup/restore, MySQL dump). DMS still earns its place when you need CDC so the source stays open during a multi-day load. SCT can copy schema in homogeneous projects, but many teams skip SCT when native export already creates the target objects.

Heterogeneous means a different engine: Oracle or SQL Server to Amazon Aurora PostgreSQL-Compatible, Oracle to Aurora MySQL-Compatible, or a warehouse engine to Amazon Redshift. That is a two-step path in AWS’s own DMS feature writing: convert schema, then migrate data. DMS Schema Conversion in the console currently emphasizes Oracle and SQL Server sources toward RDS, Aurora, and Redshift targets; the downloadable SCT supports a broader matrix (Oracle 10.1 and higher to Aurora MySQL, Aurora PostgreSQL, MariaDB, MySQL, Oracle, and PostgreSQL, among other pairs). If the exam names a pair, assume you still run an assessment before you promise dates.

Amazon Aurora and Amazon RDS are the usual managed targets. Aurora PostgreSQL is the classic modernization landing zone for Oracle OLTP on this exam. RDS for Oracle is the classic replatform-without-rewriting-PL/SQL landing zone. EC2-hosted databases remain valid when you need features RDS does not offer (for example certain Oracle options), but then you also own patching.

What SCT converts — and what it does not

SCT builds a local converted schema and does not apply it to the target until you choose to apply. The database migration assessment report lists every action item that could not convert automatically, with effort estimates. That report is how you size the application team, not a green sticker that “the database is ready.”

SCT generally attempts tables, constraints it can express, views, stored procedures, functions, and packages where a target equivalent exists. For Oracle to PostgreSQL it can also convert SQL*Plus-style files into psql-oriented SQL, and it can convert application SQL in a separate application-conversion workflow. Dynamic SQL is an extra, explicit conversion choice because the string may change at runtime.

When no equivalent exists, SCT does not silently invent one. Examples AWS documents:

• Features with no RDS equivalent (Oracle UTL_SMTP email, some job schedulers) may need the extension pack, which installs an extra schema such as AWS_ORACLE_EXT and can wire AWS Lambda to emulate email or scheduling. Emulation is not the same as native conversion.
• You may modify the source, write target-side objects by hand, or keep that piece on EC2.
• SCT does not convert your Java/C# application by itself unless you run the application SQL conversion against those codebases.
• SCT does not replace users, roles, and privilege design on the target; those are security work.
• Generating converted schema is not data movement; LOBs, partitioning choices, and character sets still hit DMS limits.

If an item cannot convert, the assessment report is the artifact. Do not invent a pass-rate for automatic conversion; AWS does not publish a single percentage that is true for every schema.

What DMS migrates — and what it does not

AWS’s DMS best-practice guide is blunt. DMS supports basic schema migration, including creating tables and primary keys. It does not automatically create secondary indexes, foreign keys, user accounts, and similar objects. Ongoing CDC replicates a limited set of DDL and does not propagate indexes, users, privileges, stored procedures, or other changes that are not table data.

Operational consequences the exam loves:

• Create secondary indexes and foreign keys after full load (or disable them during load) so bulk insert is not fighting index maintenance and parent/child order.
• Triggers on the target during full load cause duplicate-work and constraint failures; add them later.
• Cascade constraints may not appear in the source log stream the way DMS expects; premigration assessments call this out on engines such as MySQL.
• Views are not CDC objects in the usual sense; DMS can load a view as a table in a full-load task, not as a living view definition.

Task modes:

Enable supplemental logging (Oracle) or the engine-specific CDC prerequisites (SQL Server recovery model and replication, PostgreSQL logical decoding, MySQL FULL binlog image). Multi-AZ on the replication instance is for replication high availability during a long CDC tail; it can cost performance. Size LOB settings explicitly; inline versus limited versus full LOB mode changes both duration and completeness.

Premigration assessments

A premigration assessment run evaluates a task before you start it. AWS DMS also keeps a narrower legacy data type assessment. If you choose a premigration assessment run, you do not need a separate data type assessment. Results land in the console and in an Amazon S3 bucket you designate.

Assessments catch missing CDC configuration, unsupported data types or primary key shapes, foreign keys that will break alphabetical full load, cascade constraints DMS will not replay, and similar engine-specific blockers. They do not convert schema. They do not replace the SCT assessment report. Run both on a heterogeneous 50 TB program: SCT for code and objects, DMS premigration for the task you are about to start.

DMS Fleet Advisor (discovery of database estates) is the inventory cousin of ADS: it tells you what you have, not whether this Tuesday’s task will start.

50 TB Oracle on a Professional clock

A 50 TB Oracle OLTP system with a 30-minute contractual window is the scenario that separates Associate trivia from SAP-C02 design.

First pick the target: RDS for Oracle if the application must keep PL/SQL with minimal rewrite; Aurora PostgreSQL if the program accepted heterogeneous risk and funded SCT remediation. Second pick the initial copy path. Fifty terabytes at a dedicated 100 Mbps is on the order of 45–50 days; at 1 Gbps on the order of 4–5 days; at 10 Gbps, hours rather than weeks (protocol overhead and change rate add time). If the WAN cannot carry that, the initial consistent copy rides Direct Connect, a current Snowball Edge job for accounts that can still order Snow, or AWS Data Transfer Terminal / partner offline transfer for accounts that cannot. Third, start CDC (or CDC-only after the native copy) so the target catches up while the source stays open. Fourth, during the 30-minute window: quiesce writers, wait for zero lag, cut applications, enable indexes/FKs/triggers you deferred, and keep a rollback (source still authoritative until you decommission it).

Do not use MGN as the Aurora path: MGN would rehost the Oracle host onto EC2. Do not use DataSync against Oracle data files and expect Aurora to mount them. Do not assume SCT converted application Java SQL because the schema report looked green. Do not skip premigration assessments because a lab-sized schema succeeded.