Examkin

Question

Difficulty: MediumDatabase Migration and Schema Conversion using DMS and SCT

A Solutions Architect is designing the migration of an on-premises Microsoft SQL Server database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration must be completed with minimal downtime, and the application must continue writing to the source database during the replication process. Which two actions must the Solutions Architect take to support the schema conversion and data replication phases? (Select two.)

  1. Run the AWS Schema Conversion Tool (SCT) to generate a schema conversion assessment report, convert the schema, and apply the converted SQL scripts to the target Aurora PostgreSQL database before starting the migration task.Answer
  2. Enable MS-CDC on the source Microsoft SQL Server database and the tables selected for migration to allow the AWS DMS task to perform Change Data Capture (CDC).Answer
  3. C
    Configure AWS DMS to automatically convert the source database schema and create the tables on the target Aurora PostgreSQL database during the replication phase.
  4. D
    Enable supplemental logging on the source database tables to allow AWS DMS to read the transaction logs during the Change Data Capture (CDC) phase.
  5. E
    Deploy a single AWS DMS task configured for Full Load only, and use the SQL Server Agent on the source server to automatically forward incremental transaction log backups directly to the Aurora PostgreSQL target.

Answer

Run the AWS Schema Conversion Tool (SCT) to generate a schema conversion assessment report, convert the schema, and apply the converted SQL scripts to the target Aurora PostgreSQL database before starting the migration task. Also, enable MS-CDC on the source Microsoft SQL Server database and the tables selected for migration to allow the AWS DMS task to perform Change Data Capture (CDC).
For a heterogeneous database migration (SQL Server to Aurora PostgreSQL), the schema must first be converted and applied using the AWS Schema Conversion Tool (SCT). To perform replication with minimal downtime, a Full Load plus CDC task is required, which depends on Microsoft Change Data Capture (MS-CDC) being enabled on the source SQL Server database and tables to trace ongoing transactions.

Step-by-Step Solution

1
Analyze schema compatibility and convert the database schema using the AWS Schema Conversion Tool (SCT).
SQL Server schema is converted to PostgreSQL-compatible SQL scripts.
Since SQL Server and PostgreSQL are heterogeneous database engines, AWS DMS cannot automatically map and convert the schema structures (like stored procedures or custom data types) on its own.
2
Apply the converted schema scripts to the target Aurora PostgreSQL DB cluster.
The target database schema, tables, and views are created and ready to receive data.
Applying the schema first ensures that when AWS DMS starts copying data, the target tables exist with the correct data types.
3
Configure Microsoft Change Data Capture (MS-CDC) on the source SQL Server database and individual tables.
SQL Server starts recording changes to system tables, making them accessible to AWS DMS.
Ongoing replication requires AWS DMS to read database changes. For SQL Server, this is achieved by enabling MS-CDC.
4
Create and run an AWS DMS replication task configured for Full Load plus CDC.
Existing data is migrated, and subsequent updates are replicated to the target in near real-time.
This configuration enables minimal downtime migration by allowing the source database to remain online during replication.

Key Concept

Heterogeneous database migration requires schema pre-conversion via SCT and source-side database engine-specific replication logging configuration (like MS-CDC for SQL Server) to support DMS CDC.
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic