Lesson 284 · AWS Learning Path

AWS 284: AWS DMS and Schema Conversion Tool

Anil Jalela · Published Apr 5, 2026 · 14 min read

Why this lesson matters

A server rehost copies disks; a database migration must preserve database meaning. Tables, keys, indexes, sequences, views, procedures, privileges, character sets and transaction order must work on the target. During an online migration, old rows may be bulk loaded while new transactions continue arriving through change data capture (CDC). A green task status cannot prove that every object converted, every large object survived, the target performs correctly, or rollback can preserve post-cutover writes.

AWS Database Migration Service moves data between supported sources and targets. DMS Schema Conversion assesses and converts schema/code for heterogeneous migrations. The desktop AWS Schema Conversion Tool (AWS SCT) remains a legacy alternative, but current AWS guidance recommends managed DMS Schema Conversion. These tools assist database engineers; they do not replace engine expertise, application testing or change ownership.

Outcomes

By the end, you can:

• separate assessment, schema conversion, data movement, validation and cutover;
• distinguish homogeneous and heterogeneous migration work;
• choose DMS Standard or DMS Serverless from supported features and operating needs;
• trace source logs through replication compute to the target;
• design source/target accounts, network, TLS, secrets and least-privilege users;
• choose full load, CDC-only, or full load plus CDC and explain each phase;
• handle keys, constraints, indexes, sequences, DDL and large objects deliberately;
• monitor capacity, source/target latency, table statistics and validation failures;
• design write freeze, cutover, fallback and post-cutover reconciliation; and
• produce an Oracle-to-Aurora PostgreSQL migration dossier from supplied evidence.

Current product boundaries

DMS Schema Conversion resources are instance profiles, data providers and migration projects. The profile defines network/security and encryption context; source/target data providers describe connections; the project joins them with Secrets Manager credentials. Generative conversion can reduce manual work, but generated SQL is untrusted migration code until reviewed and tested.

Homogeneous versus heterogeneous migration

A homogeneous move keeps a compatible engine, such as PostgreSQL to Aurora PostgreSQL. Schema structures may transfer more directly, but versions, extensions, collations, roles, parameters and managed-service restrictions still differ.

A heterogeneous move changes engines, such as Oracle to Aurora PostgreSQL. It requires type mapping and conversion of schema and procedural code. Proprietary packages, hints, synonyms, database links, jobs, materialized views and application SQL can require redesign.

For either type, inventory:

• databases/schemas, size, growth and daily change rate;
• tables, rows, keys, partitions, LOBs and unsupported types;
• indexes, constraints, triggers, sequences and defaults;
• views, procedures, functions, packages and scheduled jobs;
• users, roles, privileges, encryption and audit behavior;
• encodings, collations, time zones and numeric/date semantics;
• transaction rate, long transactions, retention/log configuration;
• RTO/RPO, outage window and rollback-write policy; and
• query workload, performance baseline and operational tooling.

The two migration planes

Schema/code plane
source metadata -> assessment report -> automatic conversion
                -> manual remediation -> target DDL/code -> functional tests

Data plane
source tables + transaction logs -> DMS replication compute
                                  -> full load + cached changes + CDC
                                  -> target tables -> validation

Deploy target schema in the planned order before data movement. Some objects should exist before load; others, such as secondary indexes, constraints and triggers, may be delayed to improve bulk-load speed and avoid side effects. Record every object excluded from automatic creation and who applies it.

Schema conversion workflow

1. Create a versioned source inventory and compatibility baseline.
2. Configure private network paths and Secrets Manager credentials for least-privilege assessment users.
3. Create instance profile, data providers and migration project.
4. Generate assessment reports by object type and effort.
5. classify actions as automatic, simple manual, redesign, unsupported or intentionally excluded;
6. review converted data types, names, defaults, code and security semantics;
7. export reviewed SQL or apply it first to an isolated target;
8. compile objects and resolve every warning/error;
9. run unit, integration, transaction and performance tests; and
10. version the accepted schema package and manual runbook.

Do not use “95 percent converted” as acceptance. The remaining 5 percent may contain payment logic or a package used by every transaction. Weight effort and risk by business execution path.

DMS architecture and network path

DMS replication compute must connect to both source and target database endpoints. In a hybrid design, packets may cross Direct Connect or Site-to-Site VPN into a DMS subnet group, then reach an RDS/Aurora target in private subnets.

Prove:

• source name resolution, routes, firewall and database listener;
• target DNS, VPC routes, security groups/NACLs and listener;
• return paths and stateful inspection symmetry;
• TLS mode, server certificate trust and hostname behavior for both endpoints;
• Secrets Manager and KMS access without exposing database passwords;
• S3/service endpoints if task features need them;
• enough subnet addresses and multiple-AZ placement where required; and
• CloudWatch Logs/metrics and CloudTrail ownership.

An endpoint connection test proves basic authentication/connectivity from DMS. It does not prove source logging prerequisites, table permissions, sustained throughput, CDC, target DDL or application behavior.

Least-privilege database users and logs

DMS needs engine-specific privileges. A full-load reader differs from a CDC reader, which must access transaction logs or replication slots. The target writer may need table/sequence/metadata privileges. Use dedicated accounts, approved secret rotation and audit trails; never use a database superuser by default.

CDC depends on source logging configuration and retention. Examples include Oracle supplemental logging/archived redo, PostgreSQL logical replication and SQL Server log access. Exact prerequisites differ by engine and version. Set retention long enough to survive outage and maintenance, monitor disk/log growth, and know what happens if DMS falls behind beyond retained history.

Long-running transactions can delay a consistent start point. Binary logging, replication slots or supplemental logs can add source cost. Benchmark on production-shaped load and assign cleanup ownership for slots/publications/log configuration after migration.

Standard versus Serverless

One DCU represents 2 GiB RAM, but memory alone does not size a migration. Consider table count, parallel load, transaction/change rate, LOB mode, transformations, validation, logging and endpoint performance. Serverless cannot use every Standard endpoint/feature, cannot set custom CDC start points, and loses some table/statistical metadata when deprovisioned. Choose from requirements, not from the word “serverless.”

Three data-migration task types

• Full load: copy existing rows as a bounded bulk operation. Suitable when downtime or a separate change mechanism handles new writes.
• CDC only: start from a known log/checkpoint position and replicate changes. Useful after another mechanism seeded the target, but start-point correctness is critical.
• Full load plus CDC: capture changes while tables load, apply cached changes, then continue ongoing replication until cutover.

For full load plus CDC, understand phase transitions:

prepare target -> start log capture -> bulk-load tables
-> cache concurrent changes -> apply cached changes
-> ongoing CDC -> stop writes -> drain lag -> cut over

Tables can be in different phases simultaneously. Task running does not mean all tables are loaded or caught up.

Table mappings and target preparation

Table mapping JSON selects schemas/tables and can rename or transform supported objects. Broad wildcards can copy unintended or sensitive tables; exclusions can silently omit required data. Peer-review selection and transformation rules against an enumerated inventory.

TargetTablePrepMode choices are consequential:

• DO_NOTHING preserves existing target data/metadata and can create duplicate/conflict risk;
• TRUNCATE_BEFORE_LOAD deletes target rows but preserves table metadata; and
• DROP_AND_CREATE removes/recreates tables with DMS-supported metadata, potentially losing carefully converted objects.

Never start a task until the effect is written and approved per target schema. Back up or snapshot the target where rollback requires it.

For bulk speed, teams may delay secondary indexes, foreign keys, triggers and referential constraints. Restore them in a controlled sequence and validate. Triggers enabled during load can duplicate side effects. For CDC, indexes supporting update/delete lookups may be essential for performance.

Keys, updates and transaction integrity

CDC needs a stable way to identify rows. Tables without a primary key/usable unique key can suffer inefficient processing or duplicate records, especially for updates/deletes. Inventory every keyless table and choose remediation, special logging/replica identity, append-only treatment or exclusion with owner approval.

Transactional consistency is maintained within a task under documented boundaries. Splitting related tables across tasks can improve parallelism but can lose cross-table transaction ordering. Group tables that participate in common transactions. Multiple tasks also reread source logs and increase source pressure.

Sequences/identity current values, grants and ownership may not follow ordinary row replication. Explicitly synchronize and test the next generated value at cutover so new inserts do not collide.

Large objects

LOB behavior is a classic silent-data-risk area:

• limited LOB mode is faster but truncates values larger than the configured maximum;
• full LOB mode retrieves data in pieces and can be slower;
• inline LOB mode transfers small LOBs inline and larger ones through full mode where supported.

Profile actual LOB sizes before choosing. Align validation settings with limited LOB size. Chunk sizes affect network/database behavior; blindly increasing them can cause failures. CDC for tables with LOBs requires an appropriate primary key. Test empty, boundary-size, multibyte and maximum-size objects by content hash or application-level comparison.

DDL changes and schema drift

During migration, freeze avoidable source DDL. DMS CDC support for create/alter/drop varies by engine/object and does not mean converted target code remains correct. Record every source schema change after assessment, regenerate/review target changes, and test it.

Adopt a migration schema gate:

• source schema version equals assessed version;
• converted package and manual changes are versioned;
• target drift scan is clean or accepted;
• DDL freeze window and emergency process are active; and
• application release and database cutover versions are compatible.

Monitoring and capacity evidence

Use task/table statistics, DMS events/logs and CloudWatch metrics together. Watch:

• full-load progress, rows loaded and table errors;
• CDC source latency and target latency separately;
• incoming changes versus applied changes and queue/memory/disk pressure;
• replication compute CPU, free memory, swap and free storage;
• network throughput and database connections;
• source log/slot retention and target write/IO limits;
• validation pending/failed/suspended counts; and
• serverless DCU capacity, provisioning/autoscaling state and logs.

Source latency indicates how far capture lags source changes; target latency indicates how far applying lags captured changes. A low source value with high target latency points toward apply/target capacity, not source capture. Correlate trends with bulk load, indexes, long transactions and maintenance.

Read-only inventory examples:

aws dms describe-replication-instances \
  --query 'ReplicationInstances[].{ID:ReplicationInstanceIdentifier,Class:ReplicationInstanceClass,Status:ReplicationInstanceStatus,MultiAZ:MultiAZ}' \
  --output table

aws dms describe-replication-tasks \
  --without-settings \
  --query 'ReplicationTasks[].{ID:ReplicationTaskIdentifier,Type:MigrationType,Status:Status,LastFailure:LastFailureMessage}' \
  --output table

aws dms describe-replications \
  --query 'Replications[].{ID:ReplicationConfigIdentifier,State:Status,Failure:FailureMessages}' \
  --output table

Run in the approved account/Region and redact identifiers. Empty output may reflect the wrong Region or missing permission.

Validation is layered

DMS data validation compares source and target rows and reports mismatches. It adds queries, network and replication load and has key/type/engine limitations. A validation-only task can compare separately, but direction, CDC timing and source changes affect results.

Use four layers:

1. Structural: expected objects, columns, types, nullability, keys, indexes, grants and code compile.
2. Data: DMS validation, row counts, aggregates, checksums/samples, LOB hashes and exception resolution.
3. Behavioral: application CRUD, transactions, jobs, reports, errors and permissions.
4. Nonfunctional: representative performance, concurrency, failover, backup/restore, monitoring and security.

Reconcile every Validation failed, Pending records, suspended table and excluded field. Matching row counts alone can hide wrong values or duplicate/missing pairs.

Oracle-to-Aurora PostgreSQL example

Suppose an 8 TiB Oracle order system has 3,000 tables, 40 packages, 12 database links, LOBs up to 20 MiB, 4,000 transactions/second peak and a 30-minute outage objective.

Plan:

1. assess schema/code and rank each package/link by business path;
2. redesign Oracle-only code and external links, then deploy/test target schema;
3. enable approved supplemental logging and size retention;
4. create private TLS endpoints and least-privilege secret-backed users;
5. profile keyless tables and LOB sizes, then choose task/Lob modes;
6. split tasks only along transactional boundaries and benchmark source impact;
7. perform full load, build deferred indexes/constraints and continue CDC;
8. validate structure, rows, LOBs, sequences, application and performance;
9. rehearse cutover and rollback using measured lag/drain times; and
10. freeze writes, drain CDC to the acceptance position, switch the application, and retain fallback until the decision gate.

The architecture is incomplete until all 40 packages and 12 links have an explicit convert, replace, retire or retain treatment.

Cutover and fallback

Define a source/target write-authority timeline. Before cutover:

• schema and application version are frozen and compatible;
• full load is complete and table errors resolved;
• keys/indexes/constraints/triggers are in the planned state;
• validation exceptions are accepted by data owners;
• CDC source and target latency are below thresholds;
• target load/performance, backup/restore, HA and monitoring pass;
• DNS/secrets/connection strings and pools have a tested switch; and
• rollback authority, time limit and target-write handling are explicit.

Typical sequence: stop writers/jobs, commit/close transactions, capture source checkpoint, wait for DMS to apply through it, run final validation, stop task without losing checkpoint, switch clients, run smoke/business tests, observe, then accept or fall back.

Fallback is not “point the app back” after target writes. Choose before migration among target read-only observation, dual-write with proven reconciliation, reverse replication where supported/tested, or a controlled export/replay process. State data-loss/duplicate risk and reconciliation owner.

Troubleshooting by evidence

Never restart/reload a failed table until you know whether target rows will be truncated, retained or duplicated.

Security, cost and cleanup

Encrypt replication storage/connection data with approved KMS keys, require supported TLS, store credentials in Secrets Manager, restrict database users and DMS roles, protect task logs from sensitive SQL/data, and audit console/API/secret access. Mask or synthesize production data in learner evidence.

Cost owners must include Standard instance or Serverless DCUs, Multi-AZ, replication storage/logs, CloudWatch, Secrets Manager/KMS, NAT/endpoints, cross-AZ/Region transfer, source logging/storage, target database, backups, schema-conversion resources, specialist labor and parallel run.

After acceptance and retention approval: stop/delete tasks or serverless configurations, delete unused replication instances, endpoints, instance profiles/providers/projects, secrets, temporary roles/rules, logs and exported conversion artifacts as appropriate. Remove source replication slots/users/supplemental logging only after proving no consumer needs them. Decommission the source database through backup, legal, application and finance gates.

Hands-on workshop

Build the Oracle-to-Aurora dossier with:

1. engine/version/object inventory and compatibility assessment;
2. automatic/manual/redesign/retire conversion register;
3. network, TLS, secrets, IAM and database-user flow;
4. Standard-versus-Serverless decision with capacity assumptions;
5. table mappings and target preparation review;
6. source logging/retention and task phase plan;
7. keyless-table, LOB, sequence, trigger and DDL treatment;
8. metrics/dashboard and failure thresholds;
9. four-layer validation plan and exception register;
10. performance/HA/backup-restore acceptance;
11. minute-by-minute cutover/fallback with write authority; and
12. cost, cleanup, RACI and evidence index.

Inject failures: one package is unconverted, a keyless table duplicates updates, a 5 MiB LOB is truncated at 1 MiB, target CDC latency grows, a sequence starts below imported IDs, and three orders are written after target activation. Diagnose and decide whether each blocks cutover or triggers fallback.

Knowledge check

1. Does DMS Schema Conversion move table rows?

Its primary purpose is schema/code assessment and conversion; DMS replication performs data movement.

1. Why can a full-load-plus-CDC task be running while migration is not caught up?

Tables can be in different phases and captured changes may still be queued for target apply.

1. What risk does limited LOB mode create?

Values larger than its configured maximum are truncated.

1. Why are keys important for CDC?

They let DMS identify rows for efficient, unambiguous updates/deletes and validation.

1. Is Serverless automatically the best choice?

No. Its endpoint/features, states and start-point limits must match the workload.

1. Does DMS validation replace application testing?

No. Row comparison does not prove converted logic, transactions, permissions or performance.

1. Why must fallback handle target writes?

DMS does not automatically merge post-cutover target transactions back into the old source.

Lesson acceptance

Pass only if every schema object and data category has a treatment, every packet/identity path is owned, task phases and metrics have thresholds, validation is layered, and cutover/fallback preserve an explicit write authority. Reject submissions that use retired Fleet Advisor, treat generated conversion as trusted, omit keyless/LOB/sequence behavior, infer correctness from task status or row count, or delete replication/source resources before retention approval.

Official sources

• AWS Database Migration Service
• DMS Schema Conversion
• Legacy AWS SCT workflow and current recommendation
• Working with DMS Serverless
• DMS Serverless limitations
• DMS full-load task settings
• DMS LOB support
• DMS data validation
• DMS best practices
• Fleet Advisor end of support