Back to Blog

AWS DMS Internals for the SAP-C02 Exam: Replication Instances vs DMS Serverless vs Homogeneous Data Migrations, the Three Task Phases & Cached Changes, CDC Start Points & Checkpoints, Transactional vs Batch Apply, Full / Limited / Inline LOB Modes, Table Mapping Rules & Parallel Load, Data Validation, Premigration Assessments and DMS Schema Conversion

A practitioner's deep dive into AWS Database Migration Service for the AWS Certified Solutions Architect - Professional (SAP-C02) exam: how a task really moves data through full load, cached changes and ongoing replication, why change capture starts per table, replication instance classes and the three memory buffers, DMS Serverless DCUs and its state machine, homogeneous data migrations built on native tools, how CDC reads Oracle / SQL Server / MySQL / PostgreSQL logs, native start points vs checkpoints, batch apply trade-offs, the task settings that become distractors (TargetTablePrepMode, MaxFullLoadSubTasks, LobMaxSize), selection / transformation / table-settings rules, parallel load, row-level validation and validation-only tasks, premigration assessments, DMS Schema Conversion vs SCT, the Fleet Advisor retirement and the quotas the exam turns into wrong answers.

By Sailor Team , November 15, 2026

Every SAP-C02 candidate can recite the two-line version of AWS Database Migration Service: use DMS with change data capture for a minimal-downtime migration, and pair it with schema conversion when the engines differ. Our SAP-C02 migration strategies guide covers that tool-selection layer alongside MGN, DataSync and the Snow family, and the database selection guide places DMS in its source-to-target decision table.

The exam, however, has moved a layer down. It asks why a full-load-plus-CDC task briefly leaves the target inconsistent and when it becomes consistent again. It asks which DMS offering fits “unpredictable change volume, no servers to size”, and which one fits “migrate the stored procedures too”. It asks what happens to a 40 MB document column in the default LOB mode, how you restart a CDC-only task at exactly the right position after a bulk load, and why a validation report says “No primary key” for a table that clearly has one.

This guide is a practitioner’s walk through that layer, written for the AWS Certified Solutions Architect - Professional exam and verified against the current DMS User Guide. It assumes you already know what a replication instance and an endpoint are, and focuses on the mechanisms, settings and quotas that turn into distractors.

Scope note. DMS has grown into four distinct offerings plus a retired one. DMS Standard (replication instances and tasks), DMS Serverless, homogeneous data migrations and DMS Schema Conversion are all current. DMS Fleet Advisor reaches end of support on 20 May 2026 and stopped accepting new customers on 20 May 2025; AWS points customers to Migration Evaluator instead. The SAP-C02 exam guide was written before that announcement, so if a question still names Fleet Advisor as the discovery tool, answer within the question’s world, but do not recommend it in a real design.

The Four Current DMS Offerings (and the One That Retired)

OfferingWhat runs the workMigratesTypical exam cue
DMS Standard (replication instance + tasks)An EC2-backed replication instance you size, in your VPCData: full load, CDC, or both, any supported source to any supported target, heterogeneous allowed”Oracle to Aurora PostgreSQL with minimal downtime”, “needs a public IP”, “tune LOB handling per table”
DMS ServerlessAWS-provisioned capacity measured in DMS Capacity Units (DCUs)Same task model (full load / CDC / both) for a subset of endpoint types”Unpredictable change volume”, “no capacity planning”, “pay for what is used”
Homogeneous data migrationsServerless environment running native database toolsData and secondary objects (partitions, functions, stored procedures) between like engines to RDS, Aurora or DocumentDB”PostgreSQL to RDS for PostgreSQL including stored procedures”, “like-to-like, simplest path”
DMS Schema ConversionFully managed, web-based, built on the SCT engineSchema and code objects only, never data”Convert Oracle PL/SQL to PostgreSQL without installing a desktop tool”
DMS Fleet AdvisorCollector on-premises, inventory + target recommendationsNothing; discovery onlyEnd of support 20 May 2026; use Migration Evaluator

Two facts about this table carry exam weight. First, the desktop AWS Schema Conversion Tool (SCT) still exists and shares its conversion engine with DMS Schema Conversion; a question that says “download a tool to a workstation” means SCT, one that says “fully managed, in the console” means DMS Schema Conversion. Second, classic DMS tasks and DMS Serverless do not migrate secondary indexes, foreign keys, triggers, sequences, stored procedures or users. The docs are blunt: DMS “creates tables, primary keys, and in some cases unique indexes, but it won’t create any other objects that are not required to efficiently migrate the data”. Only homogeneous data migrations (via native dump tools) and schema conversion bring those objects across.

How a Task Actually Moves Data: The Three Phases

A migration task “is where all the work happens” and can consist of three phases: migration of existing data (full load), the application of cached changes, and ongoing replication (CDC). The order and the per-table nature of these phases is the single most-tested DMS mechanism.

Full load. DMS loads tables from source to target, by default eight tables at a time (MaxFullLoadSubTasks, maximum 49). Each table is a full table scan on the source.

Cached changes. While a table is being loaded, changes to it keep arriving. DMS caches them on the replication instance, in memory first and on disk when memory is exhausted. The crucial sentence: “AWS DMS doesn’t capture changes for a given table until the full load for that table is started. In other words, the point when change capture starts is different for each individual table.” When a table’s load finishes, DMS “immediately begins to apply the cached changes for that table”.

Ongoing replication. Once all tables are loaded and all cached changes are applied, “tables are transactionally consistent” and DMS switches to applying changes as whole transactions. Until that moment, the target is not transactionally consistent across tables, which is why referential-integrity constraints must be disabled during the full load and why a report run against the target mid-migration can show orphaned rows. A backlog of transactions then causes initial lag; the task reaches steady state, and that is when you stop the application, let the remaining transactions drain, and cut over.

Three settings shape this sequence and show up as answer options:

SettingDefaultWhat it controls
TargetTablePrepModeDROP_AND_CREATE in the consoleDO_NOTHING leaves existing target data and metadata alone; DROP_AND_CREATE recreates the table; TRUNCATE_BEFORE_LOAD empties it but keeps the metadata (so your pre-built indexes and constraints survive)
StopTaskCachedChangesNotApplied / StopTaskCachedChangesAppliedfalsePause after full load, before or after cached changes are applied, so you can build secondary indexes and constraints before CDC starts, then resume
TransactionConsistencyTimeout600 sHow long a full-load-plus-CDC task waits for open source transactions to close before starting the load; a full-load-only task starts immediately
CreatePkAfterFullLoadfalseDelay primary key / unique index creation until after the full load completes
CommitRate10,000 (max 50,000)Records transferred together during the full load

The index guidance follows directly from the phases. For a full load, the docs recommend dropping primary and secondary indexes, referential-integrity constraints and DML triggers, or delaying their creation. For full load plus CDC, “add secondary indexes before the CDC phase” because DMS applies changes with logical row-by-row operations that need the indexes to avoid full table scans. Enable triggers “right before the cutover”. And when the target is RDS, turn off automated backups and Multi-AZ on the target until cutover to remove write overhead. A scenario asking why the CDC phase is slow after a fast full load is usually “no indexes on the target” or “transactional apply with a target that cannot keep up”.

Views are the other phase-bound rule: “You can migrate views using full-load tasks only.” A CDC-only or full-load-plus-CDC task includes only tables, so loading views means a separate full-load-only task.

Inside the Replication Instance

A replication instance is an EC2 instance in a VPC that “connects to your source data store, reads the source data, and formats the data for consumption by the target data store”, then loads it. “Most of this processing happens in memory”, but large transactions buffer to disk and cached transactions and logs are written to disk.

Instance classes and the three memory buffers

DMS supports T3, C5, C6i, C7i, R5, R6i and R7i classes, from dms.t3.micro (2 vCPU, 1 GiB) up to dms.r7i.48xlarge (192 vCPU, 1,536 GiB). The documented way to choose is to understand the CDC pipeline: there are two stream buffers (incoming and outgoing) and a sorter, “another memory buffer” that tracks transactions, forwards only relevant ones, and “makes sure that transactions are forwarded in the same commit order as on the source”. If any of the three experiences memory pressure, performance suffers and the task can fail. Hence the exam heuristic:

  • Compute-optimized (C) when the work is CPU-bound: heterogeneous migrations convert every value through DMS’s intermediate types (“Oracle to PostgreSQL” is the docs’ own example), many parallel tasks, many sub-tasks.
  • Memory-optimized (R) when the work is memory-bound: “high number of transactions per second”, long transactions, LOBs (processed in memory), many tables in CDC.
  • T3 for proofs of concept and small migrations. T3 launches in unlimited mode, so a sustained CPU burst above baseline is billed as surplus credits, which also matters for dms.t3.micro under the free tier.

Storage, quotas and what they are for

Storage on a replication instance is “used for log files and any cached changes that are collected during the load”, plus memory spill; it is GP2, so IOPS scale with size, and “you cannot decrease the disk size after you increase it”. Smaller classes include 50 GB and larger classes 100 GB in the hourly price. The quotas worth memorising, all per account per Region and adjustable except API throttling:

ResourceDefault quota
Replication instances60
Total storage across all replication instances30,000 GB
Endpoints per account / per replication instance1,000 / 100
Tasks per account / per replication instance600 / 200
Serverless replications100
Homogeneous data migrations per account / running at once / per migration project600 / 100 / 10
Replication subnet groups / subnets per group60 / 60
Certificates100
API throttling200 requests per second maximum, refilling at 8 per second, not adjustable

A distractor pattern: “a single replication instance cannot host more than X tasks”. The real limit is 200 tasks per instance, and the practical limit is memory and CPU long before that.

Multi-AZ, networking and engine versions

With Multi-AZ, DMS “automatically provisions and maintains a synchronous standby replica of the replication instance in a different Availability Zone”. The docs recommend it for ongoing replication and warn it “can have an impact on performance”. One nuance the exam likes: “when using a single AZ or Multi-AZ replication instance during a FULL LOAD and a failover or host replacement occurs, the full load task is expected to fail”, and you restart it for the tables that did not complete. Multi-AZ protects the CDC phase, not an in-flight bulk load.

An instance can be public or private. Private instances reach source and target over VPC routing, peering, Transit Gateway, Direct Connect or VPN; a public instance gets a public IP for sources reachable over the internet. Replication instance engine versions can be upgraded (never downgraded); DMS stops running tasks before an upgrade, and a new instance runs the latest version unless you pin one. All data at rest on the instance is KMS-encrypted and in-flight traffic can use SSL.

DMS Serverless: DCUs and the State Machine

DMS Serverless “eliminates replication instance management tasks like capacity estimation, provisioning, cost optimization, and managing replication engine versions and patching”. You still create the same source and target endpoints, but instead of an instance plus a task you create a replication configuration with table mappings, settings and a Compute Config.

Capacity. “A DCU is 2GB of RAM.” You set MinCapacityUnits (default 1) and MaxCapacityUnits; valid sizes are 1, 2, 4, 8, 16, 32, 64, 128, 192, 256 and 384 DCUs. DMS “bills your account for each DCU that your replication is currently using”. The documented tuning advice is counter-intuitive and quotable: set the maximum DCU to the largest value so autoscaling can absorb spikes (you pay only for what is used), and set the minimum high enough to handle sudden bursts because provisioning takes time. Under-provisioning shows as CapacityUtilization pinned at 100 percent and an out-of-memory event; the fastest fix is raising MinCapacityUnits and resuming. Serverless “can’t autoscale down while a full load is in progress”.

States. Starting a replication walks through Initializing, Preparing Metadata Resources, Testing Connection, Fetching Metadata, Calculating Capacity, Provisioning Capacity, Replication Starting and finally Running. “It can take up to 40 minutes to initialize all the resources the first time you start a new AWS DMS Serverless replication.” A stopped replication is resumed with start-replication-type resume-processing; the plain start-replication is for the initial start only. And the trap: “You cannot resume a replication that has not started in 48 hours as AWS DMS deprovisions the resources”, at which point “the metadata related to the table and the replication statistics are lost”.

Storage. Serverless allocates 100 GB initially, checks utilisation every 15 minutes and scales up at 90 percent, with no cooling period. Full-load operations “restart from the beginning for any incomplete tables when you resume a previously stopped task”.

Limitations that become distractors:

  • No public IP address; management is through the console and APIs.
  • A subset of endpoints only. Sources: MongoDB, DocumentDB, SQL Server, PostgreSQL-compatible, MySQL-compatible, MariaDB, Oracle, S3, Db2. Targets: SQL Server, PostgreSQL, MySQL-compatible, Oracle, S3, Redshift, DynamoDB, Kinesis Data Streams, MSK, OpenSearch Service, DocumentDB, Neptune.
  • “AWS DMS serverless does not support views.”
  • Dependencies such as S3, Kinesis, Secrets Manager, DynamoDB, Redshift and OpenSearch must be reached through VPC endpoints.
  • No SSL for Db2 endpoints; the engine version (currently 3.5.4) is managed for you.
  • Multi-AZ is available as a Compute Config option and recommended for CDC.

Exam framing: “variable, unpredictable change rate and the team does not want to size or patch instances” points to Serverless; “needs a public replication endpoint”, “must migrate views”, “uses an endpoint type Serverless lacks” or “needs to pin a specific engine version” points back to a replication instance.

Homogeneous Data Migrations: Native Tools, Serverless, Whole Schema

Homogeneous data migrations “simplify the migration of self-managed, on-premises databases to their Amazon RDS equivalents” by using “native database tools to provide easy and performant like-to-like migrations”. Under the hood DMS “connects to the source data provider, reads the source data, dumps the files on the disk, and restores the data using native database tools”: think pg_dump/pg_restore plus logical replication for PostgreSQL, mysqldump plus binlog replication for MySQL and MariaDB, mongodump/mongorestore for MongoDB to DocumentDB.

Why the exam cares: this is the only DMS path that moves “data, table partitions, data types, and secondary objects such as functions, stored procedures, and so on”. It is serverless, supports full load, ongoing replication, and full load with ongoing replication, and accepts on-premises, EC2 and RDS sources (including a MySQL read replica) with RDS or Aurora targets (DocumentDB for MongoDB).

It reuses the Schema Conversion resource model: an instance profile (network and security settings), data providers (connection details) and a migration project that binds them with Secrets Manager secrets for credentials. Limitations worth knowing: no built-in data validation; selection rules depend on engine and type (PostgreSQL and MongoDB sources for all types, MySQL only for full load); PostgreSQL views are migrated as tables; schema-level changes during ongoing replication are captured only for MySQL (new tables on other engines need a restart); and you cannot migrate from a higher engine version to a lower one.

Decision cue: “same engine, want procedures and functions to come across, minimal tooling” means homogeneous data migrations; “need per-table LOB tuning, transformation rules, data validation or a heterogeneous target” means a classic task.

CDC Internals: How DMS Reads the Logs

CDC “works by collecting changes to the database logs using the database engine’s native API”. Each engine needs its own preparation, and the engine-specific mechanism is a frequent SAP-C02 detail question:

SourceWhat DMS readsRequired source configuration
OracleOnline and archived redo logs by SCN, via LogMiner (default) or Binary ReaderARCHIVELOG mode; database-level minimal supplemental logging plus PRIMARY KEY (or ALL columns for tables without a key); on RDS, rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 24)
SQL ServerTransaction log by LSN, using MS-Replication or MS-CDC to mark changes and fn_dblog() / fn_dump_dblog() to read themFull or bulk-logged recovery, transaction log backups retained; DMS creates a publication unless you use a native start point
MySQL / MariaDB / Aurora MySQLRow-based binary logsbinlog_format = ROW, binlog retention (RDS: mysql.rds_set_configuration('binlog retention hours', 24)), automated backups enabled on RDS
PostgreSQL / Aurora PostgreSQLA logical replication slot with the test_decoding (or pglogical) pluginwal_level = logical (rds.logical_replication = 1 on RDS); PostgreSQL “automatically keeps the required WAL” for the slot

For RDS sources generally, the docs recommend backups enabled and change logs retained for “24 hours is usually enough”.

Oracle: LogMiner vs Binary Reader

“AWS DMS uses LogMiner by default”, and the guidance is to stay with it “unless you have one of the following situations: you need to run several migration tasks on the source Oracle database; the volume of changes or the redo log volume on the source Oracle database is high, or you have changes and are also using Oracle ASM”. Binary Reader mines the redo files directly, so it has “less chance of having I/O or CPU impact”, performs better, is “highly recommended” for RAC, supports XA transactions and LOB updates in 12c, but requires directory objects (CREATE ANY DIRECTORY), ASM credentials when applicable, and does not support table clusters or most Oracle compression and encryption options. You switch with the extra connection attributes useLogMinerReader=N;useBfile=Y; and must restart the CDC task after switching.

Start points, checkpoints and stop points

A CDC-only task can start from three kinds of position:

  1. Custom CDC start time. A UTC timestamp DMS converts to a native position (LSN or SCN). Not available for PostgreSQL “because the PostgreSQL database engine doesn’t have a way to map a timestamp to an LSN or SCN”, and only via the StartFromContext attribute for Db2.
  2. CDC native start point. An exact log position: an SCN from SELECT CURRENT_SCN FROM V$DATABASE for Oracle, an LSN such as 00000014:00000061:0001 for SQL Server, a binlog file and position such as mysql-bin-changelog.000024:373 for MySQL, or a replication-slot checkpoint for PostgreSQL. Preferred when “a timestamp can indicate multiple native points in the transaction log”.
  3. Checkpoint. A DMS-generated recovery position such as checkpoint:V1#1#000004AF/B00000D0#0#0#*#0#0, readable from DescribeReplicationTasks while the parent task is stopped or failed, from the console, or persistently from the awsdms_txn_state table on the target if TaskRecoveryTableEnabled is set. Checkpoints let you recover a failed task or start a second task to another target from the same point.

Two rules follow. “When the task is created, AWS DMS marks the CDC start point, and it can’t be changed”; a different start point means a new task. And with an SCN or timestamp start “you miss the results of any open transactions” that began before the start position and committed after it, unless (Oracle, engine 3.5.1 and later) you set the openTransactionWindow endpoint setting so DMS shifts the capture position back far enough.

Tasks can also stop at a source commit time or a replication-instance server time, which is how you align a CDC cutoff to a maintenance window. And a stopped full-load-plus-CDC task can be started with target reload (reload-target), which reapplies TargetTablePrepMode and reloads every table; with DO_NOTHING you must truncate manually first.

Latency, DDL and bidirectional replication

DMS states plainly that CDC “does not provide real-time replication”, that “there are no SLAs for CDC latency”, and that latency “can increase to several minutes or longer” during batch jobs or index rebuilds. A question demanding sub-second guaranteed replication is not a DMS question; native replication or a streaming design is the answer. DMS replicates “only a limited amount of data definition language (DDL) statements” and never indexes, users, privileges or stored procedures during CDC.

Bidirectional replication is two tasks, one per direction, each with LoopbackPreventionSettings so a change DMS applied to B is not echoed back to A. DMS records each applied transaction in an awsdms_loopback_prevention table and ignores transactions that reference it. Constraints: it tracks DML only (filter DDL in one task), requires BatchApplyEnabled to be false, offers no conflict detection or resolution (“isn’t intended as a full multi-master solution”), and “only one task in a bidirectional pair can be full load and CDC”. Use it only where data on the two nodes is operationally segregated.

Transactional Apply vs Batch Optimized Apply

“By default, AWS DMS processes changes in a transactional mode, which preserves transactional integrity.” Setting BatchApplyEnabled to true switches to batch optimized apply, which “efficiently groups transactions and applies them in batches” and “almost always violates referential integrity constraints”, so the docs tell you to disable those constraints during the migration and re-enable them at cutover.

Batch apply has prerequisites that appear as distractors:

  • A primary key or unique key on the source tables; without one “only batch inserts are applied but not batch updates and deletes”, and the sizing guide notes that tables without PKs make batch apply fail over to transactional apply.
  • A target table with both a unique constraint and a primary key is not supported in batch mode.
  • With LOB columns included, batch apply works only in limited LOB mode.
  • A data error from a table with the default error policy drops the whole task “from batch mode to one-by-one mode for the rest of the tables” unless you set SUSPEND_TABLE on the DataErrorPolicy and ApplyError*Policy settings.
  • Loopback prevention requires batch apply off.

The memory knobs behind both modes live in ChangeProcessingTuning. DMS “attempts to keep transaction data in memory until the transaction is fully committed”, spilling to disk when a transaction exceeds MemoryLimitTotal (default 1,024 MB) or stays longer than MemoryKeepTime (default 60 s). Batch mode adds BatchApplyMemoryLimit (500 MB), BatchApplyTimeoutMin / BatchApplyTimeoutMax (1 / 30 s) and BatchSplitSize (0, unlimited); transactional mode uses MinTransactionSize (1,000 changes) and CommitTimeout (1 s). BatchApplyPreserveTransaction (default true) applies only to Oracle targets and guarantees a batch holds whole source transactions. StatementCacheSize defaults to 50 prepared statements (max 200). The exam rarely asks for these numbers, but it does ask what “CDC changes being written to disk on the replication instance” means: memory pressure, cured by a bigger memory-optimized instance or by batch apply.

LOB Modes: Full, Limited, Inline and Per-Table Overrides

Large objects are “sometimes difficult to migrate”, and the LOB settings are a reliable source of scenario questions because the default silently truncates.

ModeMechanismTrade-offKey setting
Limited LOB mode (default)DMS pre-allocates memory and loads LOBs inline in bulk, up to a maximum size; larger LOBs are truncated with a warning in the task logFastest; “we recommend that you use limited LOB mode whenever possible”LimitedSizeLobMode = true, LobMaxSize default 32 KB, maximum 102,400 KB (100 MB)
Full LOB modeEvery LOB is looked up from the source and migrated “one at a time, piece by piece” in chunksNo truncation, “can be quite slow”FullLobMode = true, LobChunkSize recommended 64 KB; larger chunk sizes “can cause task failures”
Inline LOB modeLOBs smaller than a threshold go inline during full load; larger ones use full LOB mode; during CDC all LOBs use source lookupBest when most LOBs are small and a few are largeInlineLobMaxSize (default 0 = off, range 1–102,400 KB), only valid when FullLobMode is true; not supported for S3 or Redshift targets

Mechanics that generate distractors:

  • Two-step write. DMS “creates a new row in the target table and populates the row with all data except the associated LOB value”, then updates the row with the LOB. Therefore “all LOB columns on the target table must be nullable” during migration, even if they are NOT NULL on the source. DMS sets them nullable when it creates the tables; if you pre-created tables, you must. Exception: Oracle to Oracle in limited LOB mode writes the whole row at once.
  • Keys. “A primary key or unique index is required for tables containing LOB columns during change data capture (CDC) operations”, because DMS looks LOB values up by key. For full-load-only tasks in limited mode no key is required, but full and inline modes need one even for full load only.
  • Memory arithmetic. In limited mode during full load, DMS allocates roughly LobMaxSize × CommitRate × number of LOB columns; a LobMaxSize above 63 KB with the default commit rate pushes the instance into swap. Reduce CommitRate if performance drops. With Oracle sources, LOBs are fetched as VARCHAR in bulk when they fit in 32 K, so a limited size under 32 K is optimal.
  • JSON counts as a LOB on some engines (PostgreSQL is the docs’ example), so a limited LobMaxSize can truncate JSON documents.
  • Target support. Redshift and S3 targets have limited LOB support only, so “unlimited LOB size” is not an option for them.
  • Validation. To validate truncated LOBs, set ValidationPartialLobSize equal to LobMaxSize.

Per-table overrides live in a table-settings rule’s lob-settings object: mode of limited (default), unlimited or none, and bulk-max-size in KB. In unlimited mode a bulk-max-size of zero is standard full LOB mode, a non-zero value is combination mode (inline up to the size, lookup above it, never truncated). Because DMS converts LOBs to binary, “to specify the largest LOB you need to replicate, multiply its size by three”. Table-settings rules require exact schema and table names; a % wildcard fails validation.

The classic scenario: “after migration, the documents column is cut off at 32 KB for some rows and the task log shows truncation warnings”. Cause: default limited LOB mode. Fix: raise LobMaxSize to the largest LOB (check with dbms_lob.getlength, datalength, length or octet_length on the source), use inline mode, or use full LOB mode for that table via lob-settings.

Table Mapping: Selection, Transformation and Table-Settings Rules

Table mapping is JSON with a rules array. Three rule types matter.

Selection rules

At least one is required. rule-action is include, exclude or explicit; object-locator carries schema-name, table-name and an optional table-type of table (default), view or all. The % wildcard works for include and exclude (“all tables except those starting with DMS” is the docs’ own include-then-exclude example); explicit names exactly one table with no wildcards. Names are case-sensitive; Oracle and Db2 names go in upper case. load-order is a positive integer where higher values load first, but it only orders table initialisation: with MaxFullLoadSubTasks greater than 1, the next table does not wait for the previous one to finish. Views load to equivalent tables on the target with the same name, never to views.

Source filters hang off include rules only. Each filter names one column and one or more filter-conditions; multiple filters combine with AND, multiple conditions within a filter combine with OR. Operators are lte (alias ste), gte, eq, noteq, between, notbetween, null and notnull. Filters do not support wildcards in table names, must not be applied to LOB columns, and should only be applied to immutable columns: a row that is filtered in or out stays that way even if a later update would move it across the boundary, which the docs call “adverse behavior”. Dates use YYYY-MM-DD and SQLite comparison semantics. Filters are also how you split a huge table across tasks by key range when you cannot use parallel load.

Transformation rules

Optional, applied to rule-target of schema, table, column, table-tablespace or index-tablespace (the last two for Oracle targets only). Actions: rename, convert-lowercase, convert-uppercase, add-prefix / remove-prefix / replace-prefix, add-suffix / remove-suffix / replace-suffix, remove-column, include-column (switches the table to drop-all-columns-by-default), add-column (with a SQLite expression and data-type), change-data-type (column target only), define-primary-key (table target, no wildcards allowed, origin of primary-key or unique-index), add-before-image-columns (for Kinesis and Kafka targets, default prefix BI_), and three data-masking actions (data-masking-digits-mask, data-masking-digits-randomize, data-masking-hash-mask).

Rules that become answer options: only one action per object at the same level, and when two collide “AWS DMS applies the rule with the higher rule-id” for schema and table targets (and, since engine 3.6.1, for columns too). A schema rename transformation “takes precedence over” the TargetSchema task setting. The only transformation allowed on BLOB/CLOB-mapped columns is dropping them. Transformations that alter the target schema (rename, remove-column, add-prefix) “will not take effect until you restart the task”; a resume leaves the altered column half-populated. DMS never merges two source tables into one target table. And for Oracle targets, DMS writes into the schema of the endpoint user unless you add a schema rename rule mapping PERFDATA to PERFDATA.

The add-column expression language also exposes header columns such as $AR_H_OPERATION, $AR_H_TIMESTAMP, $AR_H_COMMIT_TIMESTAMP, $AR_H_STREAM_POSITION and $AR_H_USER, which is how you stamp an S3 or Redshift target with operation type and commit time for a CDC-driven data lake. The SAP-C02 data analytics guide covers what happens downstream of that S3 target.

Table-settings rules and parallel load

table-settings rules target exactly one table (no wildcards: “Exact schema and table name required when using table settings rule”) and carry parallel-load and lob-settings. Parallel load splits one table across threads; its type is:

  • partitions-auto: every existing partition gets a thread (also the autosegmentation mode for MongoDB and DocumentDB, with number-of-partitions default 16).
  • subpartitions-auto: Oracle only, every subpartition gets a thread.
  • partitions-list: named partitions (and Oracle subpartitions); overlapping choices duplicate rows or hit PK violations.
  • ranges: segments defined by columns (up to 10, indexed columns recommended, not DOUBLE, FLOAT, BLOB, CLOB or NCLOB) and boundaries (each array is an upper boundary, so N boundaries yield up to N+1 segments). “PostgreSQL endpoints support only this type of a parallel load.” Rows with null values in the boundary columns are not replicated.
  • none: single-threaded (the default).

Parallel load is supported from Oracle, SQL Server, MySQL, PostgreSQL, Db2 LUW, SAP ASE, MongoDB and DocumentDB sources into Oracle, SQL Server, MySQL, PostgreSQL, S3, SAP ASE, Redshift, Db2 LUW, MongoDB and DocumentDB targets. The ceiling is still MaxFullLoadSubTasks (max 49), which counts table segments, and the sizing guide advises not to exceed eight unless the instance is large. ParallelLoadThreads is a different, multiplicative knob that applies only to DynamoDB (max 200), Kinesis, Kafka, OpenSearch (max 32) and Redshift targets; the two settings multiplied give the thread count, and a high product “can consume too much memory and fail”.

The scenario: “a 4 TB partitioned Oracle table dominates the full-load time”. Answer: a table-settings rule with partitions-auto (or ranges on an indexed key), on a replication instance large enough for the extra sub-tasks, rather than more replication instances.

Data Validation and Validation-Only Tasks

With EnableValidation true, “validation begins immediately after a full load is performed for a table” and continues comparing incremental changes during CDC. DMS “compares each row in the source with its corresponding row at the target” by issuing queries, which “will consume additional resources at the source and target”. It is supported for Oracle, PostgreSQL-compatible, MySQL-compatible, SQL Server and Db2 LUW sources, and those plus Redshift and S3 as targets. Engine 3.5.4 added a faster GROUP_LEVEL mode (set automatically) for Oracle and SQL Server to PostgreSQL and like-to-like Oracle and SQL Server paths, requiring pgcrypto on PostgreSQL and DBMS_CRYPTO execute on Oracle.

Requirements and limits that appear in questions:

  • “Data validation requires that the table has a primary key or unique index.” PK columns cannot be CLOB, BLOB, BINARY or BYTE; VARCHAR/CHAR key columns must be shorter than 1,024; an Oracle key created with NOVALIDATE does not count; null key values cannot be validated. A table failing these shows No primary key even when a key exists.
  • Views are not validated; neither are columns under data-masking transformations (a masked PK suspends the whole table), the Oracle LONG type, or tables when “consolidating several databases into one”.
  • Rows “continuously modified during validation” cannot be validated and are counted as suspended.
  • Validation stops once “more than 10,000 failed or suspended records” accumulate (FailureMaxCount, default 10,000); per table the ceiling is TableFailureMaxCount (1,000). ThreadCount defaults to 5 and PartitionSize to 10,000 rows per batch.
  • Failures are written to awsdms_control.awsdms_validation_failures_v1 on the target with FAILURE_TYPE of RECORD_DIFF, MISSING_SOURCE, MISSING_TARGET or TABLE_WARNING and a JSON DETAILS column of mismatched values keyed by the row’s primary key; engine 3.6.1 adds a _v2 table that backs the data resync feature.
  • PostgreSQL targets need collation C on key columns when validating against Oracle, or HandleCollationDiff set to true.

Table-level ValidationState values are Not enabled, Pending records, Mismatched records, Suspended records, No primary key, Table error, Validated, Error, Pending validation, Preparing table and Pending revalidation. The CloudWatch counterparts are ValidationSucceededRecordCount, ValidationFailedOverallCount, ValidationSuspendedOverallCount and ValidationPendingOverallCount.

A validation-only task sets EnableValidation and ValidationOnly to true, “performs data validation without performing any migration or replication of data”, must use TargetTablePrepMode DO_NOTHING, and is immutable after creation. Its migration type still matters: a full-load validation-only task does one fast pass, reports every mismatch and shuts down (ideal “just before cut-over” to get a point-in-time mismatch count, or when heterogeneous type differences would otherwise cause endless retries). A CDC validation-only task runs continuously, re-validates changed rows, delays by ValidationQueryCdcDelaySeconds (180 s by default in this mode) and retries before failing rows. It must run in the same direction as the replication task because it reads the source change log to know what to re-check. The documented use cases are exactly the exam’s: control when validation load hits the databases, offload validation to its own instance, and feed a data-repair script from the failures table.

Premigration Assessments

A premigration assessment “evaluates specified components of a database migration task to help identify any problems that might prevent a migration task from running as expected”. The modern form is a premigration assessment run made of individual assessments (for example unsupported-data-types-in-source), chosen from those applicable to your task configuration, with results written as summary.json plus one JSON file per assessment to an S3 bucket through an IAM role you supply. The legacy data type assessment returns only unsupported source data types in a single JSON.

Mechanics: enabling an assessment run on a new task “disables the option to start the task automatically on task creation”. Run status values include passed, warning, failed, error-provisioning, error-executing, cancelling, deleting, provisioning, running and starting; failed “takes priority over all other statuses”. Individual assessments report passed, warning, failed, error, skipped, cancelled, pending or running. And since the CDC-start-point and target-reload pages both state it: “If no assessments have been performed within the last 7 days for a given task, the premigration assessment will automatically execute” when the task resumes or reloads. Assessment runs exist for Oracle, SQL Server, MySQL, MariaDB, PostgreSQL and Db2 LUW engines. The CLI verbs are describe-applicable-individual-assessments, start-replication-task-assessment-run, describe-replication-task-assessment-runs and describe-replication-task-individual-assessments.

DMS Schema Conversion vs AWS SCT

DMS Schema Conversion “converts your source database schemas to a format compatible with your target database on Aurora, Amazon RDS, or Amazon Redshift” and “converts your database schema, not your data”. It is “a fully managed, web-based feature that builds on the AWS Schema Conversion Tool (AWS SCT) conversion engine”, free to use apart from S3 storage for exported scripts, and driven from the console, CLI, SDKs or an AI agent through the API.

Its three resources are the same as homogeneous data migrations: an instance profile, source and target data providers, and a migration project carrying Secrets Manager secrets. You can start with a virtual target before the real database exists. The workflow is assess (a conversion assessment report estimating automatic vs manual effort), convert (tables, views, stored procedures, functions and other objects, with transformation rules and optional generative AI conversion for objects the rules cannot finish), review and edit the SQL, then apply to the target or export SQL scripts to S3. An extension pack emulates source features the target lacks.

Supported paths include Oracle, SQL Server, Db2 LUW, Db2 for z/OS and SAP ASE to Aurora or RDS for PostgreSQL (with generative AI), Oracle and SQL Server to Aurora or RDS for MySQL, PostgreSQL to MySQL, MySQL to PostgreSQL, Oracle to Redshift, and Db2 for z/OS to RDS for Db2. Db2 for z/OS paths are API and CLI only.

Exam framing: a heterogeneous migration is always “schema conversion first (DMS Schema Conversion or SCT), apply the converted schema, then a DMS task for data, then convert the application code the tool flagged”. Same-engine schema moves use native tools (the docs name Oracle SQL Developer, MySQL Workbench and pgAdmin 4) or a homogeneous data migration.

Monitoring, Sizing Signals and Time Travel

Two task metrics diagnose almost every “replication is lagging” scenario. CDCLatencySource is the gap between the last event captured from the source and now; high values mean capture is delayed, so look at the source (log generation, LogMiner load, network). CDCLatencyTarget is the gap between the oldest event waiting to commit on the target and now; if it is high while source latency is not, the docs say to check for missing primary keys or indexes on the target, resource bottlenecks on the target or instance, and the network to the target. “If both are high, investigate CDCLatencySource first.” CDCIncomingChanges counts events waiting to be applied, and CDCChangesMemorySource/CDCChangesDiskSource (and the Target pair) show whether changes are spilling to disk. On the instance, watch FreeableMemory, SwapUsage, FreeStorageSpace and ReadIOPS + WriteIOPS against the GP2 baseline.

Only the task log (CloudWatch Logs, enabled per task) records truncation warnings and rows rejected for foreign key violations, so “data is missing but the task shows no errors” means read the log. Time Travel records change data into S3 for troubleshooting; enable it only for tasks you are debugging because EnableRawData raises memory use. SNS event subscriptions cover instance and task lifecycle events.

Pricing Shape

You pay for replication instances by the hour (Single-AZ or Multi-AZ rates by class, with 50 GB or 100 GB of GP2 included and extra storage per GB-month), for T3 surplus CPU credits, and for DMS Serverless per DCU-hour of capacity actually used. “All data transfer into AWS DMS is free”, transfers between DMS and RDS or EC2 in the same Availability Zone are free, and standard rates apply across AZs, Regions or out to the internet. Homogeneous data migrations are billed hourly for the migration’s duration with no storage charge. DMS Schema Conversion is free apart from S3. The free tier offers 750 hours per month of dms.t3.micro for a year on accounts created before 15 July 2025, and credits under the newer Free Plan afterwards. The exam uses these shapes qualitatively: “a Multi-AZ R-class instance running for months to keep a reporting copy in sync” is the expensive option versus a Serverless replication that scales down between bursts.

Exam Cue to Answer Table

Scenario wordingAnswer
”Minimal downtime, Oracle to Aurora PostgreSQL”DMS Schema Conversion (or SCT) for schema, then a full-load-plus-CDC task; disable FKs and triggers during load, add secondary indexes before CDC
”Same engine, stored procedures and partitions must come across, simplest”Homogeneous data migration (native tools, serverless)
“Unpredictable change volume, no capacity planning, pay for use”DMS Serverless with a high MaxCapacityUnits
”Must migrate views”Full-load-only task on a replication instance; Serverless does not support views, CDC tasks include only tables
”Target inconsistent until when?”After all tables are loaded and all cached changes applied; CDC then applies whole transactions
”Replication to Redshift target cannot keep up, PK on source”Enable BatchApplyEnabled; disable referential integrity until cutover
”Some rows in a document column are cut to 32 KB”Default limited LOB mode; raise LobMaxSize, use inline mode, or per-table full LOB mode
”Table with LOB columns and no primary key fails in CDC”PK or unique index required for LOB replication during CDC
”Bulk load already done with Snowball; start replication at the right point”CDC-only task from a native start point (SCN, LSN, binlog file:position) or a checkpoint
”Oracle source, very high redo volume, RAC, several tasks”Binary Reader (useLogMinerReader=N;useBfile=Y) instead of LogMiner
”PostgreSQL source, start CDC from a timestamp”Not supported; use a replication-slot checkpoint native start point
”Multi-AZ instance failed over during full load, task failed”Expected; restart for incomplete tables; Multi-AZ protects CDC not bulk load
”Second CDC task to a new target from the same point”Use the parent task’s checkpoint (awsdms_txn_state with TaskRecoveryTableEnabled)
“Huge partitioned table dominates full load”table-settings rule with parallel-load partitions-auto or ranges
”Need to rename schema and prefix tables on target”Transformation rules; restart (not resume) the task after changing them
”Validate without re-migrating, just before cutover”Full-load validation-only task (ValidationOnly, TargetTablePrepMode DO_NOTHING)
“Validation says No primary key but table has a PK”Key type disallowed (LOB / BINARY / unbounded VARCHAR / NOVALIDATE) or masked PK column
”Serverless replication stopped for three days, cannot resume”Deprovisioned after 48 hours; create and start a new replication
”Serverless cannot reach Secrets Manager / S3 / Kinesis”Serverless requires VPC endpoints for those services
”Replicate changes in both directions without conflicts”Two CDC tasks with LoopbackPreventionSettings, batch apply off, operationally segregated data; DMS is not multi-master
”Guaranteed sub-second replication”Not DMS CDC (no latency SLA); native replication or streaming
”Which instance class for Oracle to PostgreSQL with 20 parallel tasks”Compute-optimized C class; R class for high-TPS CDC memory pressure
”Discover on-premises database fleet (2026 design)“Migration Evaluator; Fleet Advisor retires 20 May 2026
”Replication instance quota”60 instances, 30,000 GB storage total, 200 tasks and 100 endpoints per instance
”Rows rejected for FK violation but task shows no error”Read the CloudWatch task log; only it records truncation and FK rejections

Common Mistakes

Treating DMS as a schema migration tool. Classic tasks create tables, primary keys and sometimes unique indexes. Secondary indexes, foreign keys, triggers, sequences, procedures and users do not come across. Pick homogeneous data migrations or a schema conversion step when the scenario needs them.

Assuming the target is consistent during the full load. It is not. Change capture starts per table, cached changes apply per table, and only after every table is done does DMS switch to transaction-ordered apply.

Forgetting the LOB default. Limited LOB mode at 32 KB is the default and truncates silently except for a task-log warning. Any scenario involving documents, images or JSON columns is a LOB-mode question.

Choosing batch apply without checking keys. Batch apply needs a source PK or unique key for updates and deletes, violates referential integrity, and is unavailable with full LOB mode or loopback prevention.

Resuming instead of restarting after a schema-altering transformation. Renames, prefixes and column removals take effect only on restart.

Using Multi-AZ as full-load insurance. It protects the CDC phase; a failover during full load fails the task.

Confusing DMS Serverless with homogeneous data migrations. Both are serverless. Only the latter uses native dump tools and carries secondary objects; only the former supports heterogeneous targets like Redshift, Kinesis and OpenSearch.

Starting a CDC-only task from a timestamp on PostgreSQL. Not possible; use a checkpoint from the replication slot.

Hands-On Lab: Watch the Phases and the Settings Bite

Run this in a sandbox account with two small RDS instances (or one RDS PostgreSQL plus one Aurora PostgreSQL cluster) in the same VPC. Expect a few dollars of cost for the instance hours.

  1. Create endpoints and a replication instance. Use dms.t3.medium, Single-AZ, 50 GB. Test both endpoint connections from the console. Note the engine version shown.
  2. Create a full-load-plus-CDC task with validation. Use TargetTablePrepMode: DROP_AND_CREATE, EnableValidation: true, MaxFullLoadSubTasks: 2, and the default LOB settings. Table mapping: include public.%.
  3. Pre-load a table with a 100 KB text column on the source, then start the task. Watch the task log for the truncation warning and compare octet_length on source and target. Modify the task (stopped) to LobMaxSize: 200 and reload the table; confirm the full value arrives.
  4. Generate writes during the load. Insert rows into a large table while it is loading and observe the table statistics: FullLoadRows, then Inserts counting the cached changes after the load, then live CDC counts.
  5. Break validation. Update a row directly on the target. Watch ValidationState change to Mismatched records and query awsdms_control.awsdms_validation_failures_v1 to read the DETAILS JSON.
  6. Add a transformation rule that prefixes tables with mig_. Resume the task and observe that nothing changes; restart it and observe the new tables.
  7. Record a checkpoint. Stop the task and run aws dms describe-replication-tasks --query "ReplicationTasks[].RecoveryCheckpoint". Create a CDC-only task using that value as --cdc-start-position to a second target and confirm it picks up only post-checkpoint changes.
  8. Run a premigration assessment on a new task copy with aws dms describe-applicable-individual-assessments then start-replication-task-assessment-run, and open summary.json in the S3 bucket. Delete everything afterwards; the replication instance bills hourly.

If you have a serverless-eligible pair, repeat steps 2 to 4 as a Serverless replication with MaxCapacityUnits: 4 and time the provisioning states. That first-run wait is what makes “40 minutes” memorable.

Practice the Decisions, Not the Definitions

The SAP-C02 does not ask you to define CDC. It hands you a migration scenario with a downtime budget, a data profile (LOBs, no primary keys, a 4 TB partitioned table), an engine pair and an operational constraint, then offers four plausible DMS configurations. The right one follows from the mechanics above: which phase is the bottleneck, which setting controls it, which offering carries the objects the scenario mentions, and which quota or limitation eliminates an option.

The fastest way to make those eliminations reflexive is to drill them against realistic distractors. Sailor.sh’s AWS Certified Solutions Architect Professional Mock Exam Bundle includes migration scenarios whose explanations walk through why the Serverless answer, the batch-apply answer or the full-LOB answer wins or loses in each case. Pair this guide with the migration strategies guide for the surrounding tool choices, the application modernization guide for where database migration sits in a strangler-fig plan, and the SAP-C02 study plan, then test yourself on the SAP-C02 practice questions before booking.

Frequently Asked Questions

When does a DMS full-load-plus-CDC task make the target transactionally consistent?

Only after every selected table has finished its full load and all cached changes for every table have been applied. Change capture starts separately for each table when that table’s load begins, cached changes are applied per table as each load finishes, and DMS then switches to applying changes as whole transactions in source commit order. During the full load, cross-table consistency is not guaranteed, which is why referential-integrity constraints and triggers should be disabled until the CDC phase.

What is the difference between DMS Serverless and homogeneous data migrations?

Both are serverless, but they do different jobs. DMS Serverless runs the classic replication engine (full load, CDC or both) on AWS-managed capacity measured in DCUs of 2 GB RAM each, supports heterogeneous targets such as Redshift, Kinesis, OpenSearch and Neptune, and still only creates tables and primary keys on the target. Homogeneous data migrations use native database tools (dump, restore and native replication) for like-to-like moves to RDS, Aurora or DocumentDB, and carry secondary objects such as partitions, functions and stored procedures, but have no built-in validation and support fewer selection-rule options.

Why did DMS truncate my LOB column, and how do I fix it?

The default is limited LOB mode with a 32 KB maximum. LOBs above LobMaxSize are truncated and a warning is written to the task log. Raise LobMaxSize to the largest LOB in the table (up to 100 MB), switch to inline LOB mode so small LOBs go inline and large ones use source lookups, or use full LOB mode for that table through a table-settings rule with lob-settings mode unlimited. Remember that full and inline modes need a primary key or unique index on the table even for full-load-only tasks.

When should I use batch optimized apply?

When the target cannot keep up with transactional apply and you can tolerate temporary lapses in transactional integrity, typically high-change-rate replication into Redshift or a busy relational target. Batch apply needs a primary key or unique key on the source tables for updates and deletes, is incompatible with full LOB mode and with loopback prevention, and almost always violates referential-integrity constraints, so disable them until cutover.

How do I start a CDC-only task at exactly the right position after a bulk load?

Use a CDC native start point: an Oracle SCN from V$DATABASE, a SQL Server LSN, a MySQL binlog file and position from SHOW MASTER STATUS, or a PostgreSQL replication-slot checkpoint. Alternatively use a checkpoint from a previous DMS task. PostgreSQL does not support starting from a timestamp. The start point is fixed when the task is created; choose a different one by creating a new task.

Is DMS Fleet Advisor still on the SAP-C02 exam?

The exam guide predates the retirement, so a question may still describe Fleet Advisor’s discovery role, and you should answer within the question. In practice, Fleet Advisor stopped accepting new customers on 20 May 2025 and reaches end of support on 20 May 2026; AWS recommends Migration Evaluator for database migration assessments.

What does a validation-only task do?

It compares source and target rows without migrating or replicating anything. A full-load validation-only task does a single fast pass and reports every mismatch, which suits a pre-cutover check. A CDC validation-only task runs continuously, re-validates changed rows after a 180-second default delay and retries before failing them, which suits long-running replications. Both require TargetTablePrepMode set to DO_NOTHING, and the ValidationOnly setting cannot be changed after the task is created.

Conclusion

DMS looks like one service and behaves like four. Classic tasks on replication instances give you every knob: instance class, LOB mode, table mapping, parallel load, batch apply, validation and checkpoints. DMS Serverless keeps that task model but trades the knobs for DCUs, a state machine and a shorter endpoint list. Homogeneous data migrations trade flexibility for completeness, bringing procedures and partitions along through native tools. DMS Schema Conversion handles the code, never the data. The SAP-C02 rewards candidates who can read a scenario and see which of the four it is describing, then recall the one mechanism, setting or quota that separates the right configuration from the three plausible wrong ones. Learn the phases, the per-table capture rule, the LOB default, the key requirements and the start-point options, and the migration domain becomes one of the most predictable parts of the exam.

Limited Time Offer: Get 80% off all Mock Exam Bundles | Sale ends in 7 days. Start learning today.

Claim Now