AWS Database Migration Service (DMS): Migration Patterns and Schema Conversion – SAP-C02 Study Guide

Purpose of This Lesson

AWS Database Migration Service (AWS DMS) helps move and continuously replicate database data between supported source and target systems. For SAP-C02 scenarios, the key decision is whether the migration is homogeneous—the same database engine on both sides—or heterogeneous, where the engine and schema must be transformed.

AWS DMS can support migrations from on-premises databases, Amazon EC2-hosted databases, and Amazon RDS databases to targets such as Amazon Aurora, Amazon Redshift, Amazon DynamoDB, and Amazon DocumentDB.

Key Concepts

AWS DMS migration types

AWS DMS supports several database movement patterns:

• Full-load migration: Copies existing data from the source to the target.
• Change data capture (CDC): Continuously replicates changes made after or during the initial migration.
• Full load plus CDC: Loads the existing database and then keeps the target synchronized with ongoing source changes.

Continuous replication is useful when the source database must remain available during migration or when DMS is being used for disaster recovery, development and testing, or ongoing data distribution.

Homogeneous versus heterogeneous migration

A homogeneous migration uses the same database engine at both ends. Examples include:

• Oracle to Oracle on Amazon RDS
• MySQL to MySQL on Amazon RDS
• Microsoft SQL Server to SQL Server on Amazon RDS
• MySQL to MySQL-compatible Amazon Aurora

DMS can generally replicate the data without requiring a database-engine conversion. Application and schema compatibility still need to be validated, but the migration does not require a fundamental engine translation.

A heterogeneous migration moves between different database engines or data models, such as Oracle to Amazon Redshift or a relational database to Amazon DynamoDB. These migrations may require schema, data type, stored procedure, and application changes.

AWS Schema Conversion Tool

The AWS Schema Conversion Tool (AWS SCT) is used primarily to convert database schemas and code for heterogeneous migrations. It can help assess compatibility and transform objects such as tables, data types, views, and certain database-specific code.

AWS SCT and AWS DMS solve different parts of the migration:

• AWS SCT: Converts or assesses the schema and database code.
• AWS DMS: Moves and replicates the data.

For a heterogeneous migration, schema conversion typically occurs before or alongside data migration. DMS alone should not be assumed to convert every engine-specific schema feature automatically.

Supported migration sources and targets

The source can include databases hosted in several environments:

• On-premises data centers
• Amazon EC2
• Amazon RDS
• Other supported database platforms

Examples of target services include:

• Amazon Aurora
• Amazon Redshift
• Amazon DynamoDB
• Amazon DocumentDB
• Amazon RDS database engines

The exact source and target engine support, feature compatibility, and replication limitations must be checked for the selected migration design.

Common DMS use cases

#### On-premises to AWS migration

DMS can migrate an on-premises Oracle, MySQL, or SQL Server database to a corresponding Amazon RDS engine or another supported AWS target.

#### Cloud-to-cloud migration

DMS can move data between databases hosted on EC2, Amazon RDS, Aurora, and other supported targets. For example, an organization may migrate a database from EC2 to RDS or from RDS to Aurora.

#### Database consolidation

Multiple source databases can be consolidated into one target database when the target schema and application design support the consolidation.

#### Disaster recovery

Continuous replication can maintain a target database in another environment or Region, depending on the supported architecture and operational requirements. This reduces the amount of data that must be restored or re-created after a failure, although it does not by itself provide a complete disaster recovery solution.

#### Development and testing

A production database can be replicated to a development or test environment. Security, masking, access control, and data-retention requirements must be considered before copying sensitive production data.

#### Single-source and multi-target replication

DMS can support patterns such as:

• One source database replicated to multiple targets
• Multiple source databases replicated or consolidated into one target

These designs require careful consideration of schema compatibility, data conflicts, transformations, and target capacity.

Exam-Relevant Takeaways

• Use AWS DMS to migrate or continuously replicate database data.
• Use AWS SCT when moving between different database engines or schemas and schema/code conversion is required.
• Homogeneous migrations, such as MySQL to MySQL, are generally simpler because the database engine remains the same.
• Heterogeneous migrations require analysis of data types, schema objects, SQL dialects, stored procedures, and application dependencies.
• DMS can support on-premises-to-cloud, cloud-to-cloud, database consolidation, disaster recovery, and development/test scenarios.
• Continuous replication is important when downtime must be minimized or when the target must remain synchronized with the source.
• DMS is a data migration and replication service; it is not a universal database schema conversion engine.
• A successful migration design must address networking, credentials, encryption, target capacity, validation, cutover, and rollback—not only data copying.

Architecture Decision Guide

Requirement	Recommended approach	Important consideration
Move MySQL to MySQL-compatible Aurora	Use AWS DMS for full load and, if needed, CDC	Validate engine-specific features and application compatibility
Move Oracle to a different database engine	Use AWS SCT for schema/code conversion and AWS DMS for data movement	Review objects that require manual conversion
Minimize application downtime during migration	Use full load plus CDC, followed by a planned cutover	Monitor replication lag and validate the target before switching applications
Keep a target synchronized for recovery	Use ongoing DMS replication	Define RPO, monitor failures, and test the recovery process
Copy production data to a test environment	Use DMS with appropriate filtering or transformation	Protect sensitive data and avoid unrestricted production access
Consolidate multiple databases	Use multiple source endpoints and a single target design	Resolve naming conflicts, key collisions, and schema differences
Distribute data to several destinations	Use a single source with multiple target tasks where supported	Confirm target-specific transformations and capacity
Move a database from EC2 to a managed AWS database	Use DMS for the data migration	Confirm source connectivity, target sizing, and application connection changes

Common Exam Traps

• Confusing DMS with AWS SCT: SCT addresses schema and code conversion; DMS performs data migration and replication.
• Assuming heterogeneous migrations are automatic: Moving Oracle to a different engine may require substantial schema and application remediation.
• Treating continuous replication as a complete DR design: Replication does not replace backup strategy, failover procedures, monitoring, or recovery testing.
• Ignoring the cutover: A low-downtime migration still requires a final synchronization, application quiescence or write coordination, validation, and DNS or connection changes.
• Overlooking data conflicts during consolidation: Multiple sources may contain duplicate keys, incompatible names, or different representations of the same business entity.
• Copying sensitive production data without controls: Development and test replicas may require masking, restricted access, encryption, and retention policies.
• Assuming every database feature is replicated identically: Engine-specific objects, stored procedures, triggers, indexes, and extensions may need separate treatment.

Real-World Engineer Notes

• Assess the source database before selecting the target. Schema compatibility and application dependencies often determine whether the migration is straightforward.
• Separate the migration into phases: assessment, schema conversion, initial load, CDC, validation, cutover, and post-cutover monitoring.
• Track replication lag, task errors, source changes, target capacity, and data validation results throughout the migration.
• Design network connectivity deliberately. On-premises sources commonly require private connectivity such as AWS Direct Connect or a VPN, along with routing and security-group rules that permit the replication instance to reach both endpoints.
• Keep credentials and connection details in a controlled security model. Use least-privilege database accounts and encrypt traffic where supported.
• For DR, define the expected recovery point objective and recovery time objective before choosing continuous replication. A replicated target still needs operational runbooks and regular failover tests.
• For database consolidation, define ownership, naming conventions, tenant boundaries, key-generation rules, and conflict-resolution behavior before starting the migration.

Quick Reference Summary

• AWS DMS: Moves and replicates database data.
• AWS SCT: Assesses and converts schemas and database code for heterogeneous migrations.
• Homogeneous migration: Same engine to same engine; usually simpler.
• Heterogeneous migration: Different engines or data models; requires conversion and compatibility work.
• Full load: Copies existing source data.
• CDC: Replicates ongoing source changes.
• Common uses: AWS migration, cloud-to-cloud movement, DR, dev/test, consolidation, and multi-target replication.
• Critical design concerns: Connectivity, permissions, encryption, compatibility, replication lag, validation, cutover, and recovery testing.

Flashcards

1. Q: What is the primary purpose of AWS DMS?

A: To migrate and continuously replicate data between supported database sources and targets.

1. Q: What is the difference between AWS DMS and AWS SCT?

A: DMS moves and replicates data, while SCT assesses and converts schemas and database code for heterogeneous migrations.

1. Q: What is a homogeneous database migration?

A: A migration between the same database engine, such as MySQL to MySQL.

1. Q: What is a heterogeneous database migration?

A: A migration between different database engines or data models, such as Oracle to Amazon Redshift.

1. Q: When is full load plus CDC useful?

A: When the source must remain operational during migration and the target needs to receive ongoing changes before cutover.

1. Q: Name two possible AWS DMS targets.

A: Amazon Aurora and Amazon Redshift. Other examples include DynamoDB and DocumentDB.

1. Q: Can DMS alone be assumed to convert all Oracle schema objects for a different target engine?

A: No. Heterogeneous migrations commonly require AWS SCT, manual remediation, and application changes.

1. Q: How can DMS support database consolidation?

A: It can migrate or replicate multiple source databases into a single target, subject to schema and data-conflict resolution.

1. Q: Why is CDC useful for disaster recovery?

A: It keeps a target database updated with source changes, reducing potential data loss compared with relying only on an older backup.

1. Q: What must be considered when replicating production data to development?

A: Data sensitivity, masking or filtering, encryption, access control, and retention.

Practice Questions

Question 1

A company is migrating an on-premises Oracle database to a different database engine on AWS. The company wants to convert tables, data types, and database-specific code before moving the data. Which solution best fits this requirement?

A. Use AWS DMS alone for schema conversion and data replication.
B. Use AWS SCT to assess and convert the schema, then use AWS DMS to migrate the data.
C. Use Amazon DataSync to convert the relational schema.
D. Use AWS Backup to transform the database engine.

Correct answer: B

AWS SCT is designed for schema and code assessment and conversion in heterogeneous migrations. AWS DMS then performs the data movement and, where appropriate, ongoing replication.

Question 2

An organization needs to migrate a MySQL database to a MySQL-compatible Aurora database while keeping the source available. The target must receive changes made during the migration. Which approach is most appropriate?

A. Perform a one-time export and import, then immediately delete the source.
B. Use AWS DMS for a full load followed by change data capture.
C. Use AWS SCT only.
D. Create an EBS snapshot and attach it to Aurora.

Correct answer: B

A full load copies existing records, while CDC keeps the target synchronized with changes made during the migration. This supports a controlled, low-downtime cutover.

Question 3

A company wants to replicate a production database continuously to a secondary environment for disaster recovery. Which statement is most accurate?

A. DMS replication eliminates the need for recovery testing.
B. DMS can provide continuous data replication, but the company must still design monitoring, failover, backups, and recovery procedures.
C. DMS automatically converts any source database into any target with no compatibility work.
D. DMS can replicate only databases running on Amazon RDS.

Correct answer: B

Continuous replication can help maintain a current recovery target, but a complete DR architecture also requires defined RPO/RTO objectives, operational procedures, monitoring, backups where appropriate, and regular testing.

Question 4

An organization is consolidating several source databases into one target database. Which issue is most important to address before starting the migration?

A. Whether all source databases use identical EC2 instance types.
B. Whether duplicate keys, naming conflicts, and incompatible schemas can be resolved.
C. Whether the source databases all use the same Availability Zone.
D. Whether AWS SCT can replace the need for target capacity planning.

Correct answer: B

Consolidation introduces data-model and identity conflicts that do not exist in a simple one-source-to-one-target migration. These conflicts must be resolved through schema and migration planning.

Question 5

A team is copying production data into a development account using AWS DMS. The data contains personally identifiable information. Which design consideration is essential?

A. Grant developers unrestricted access because the target is nonproduction.
B. Disable encryption to improve replication throughput.
C. Apply appropriate masking or filtering and enforce least-privilege access to the replicated data.
D. Use AWS SCT instead of DMS to guarantee that sensitive values are removed.

Correct answer: C

Replicating production data into development creates security and compliance risk. The design should protect data through masking or filtering where required, encryption, restricted access, and suitable retention controls.