Examkin

Question

Difficulty: HardDatabase Migration and Schema Conversion using DMS and SCT

A company is planning to migrate its on-premises MySQL 8.0 database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The database contains several tables with JSON and large binary object (BLOB) columns that are updated frequently. A solutions architect has already used the AWS Schema Conversion Tool (SCT) to migrate the schema. The architect now configures an AWS Database Migration Service (AWS DMS) replication instance and task to perform a full load with ongoing replication (Change Data Capture - CDC). During initial testing of the CDC phase, the replication task fails with errors when update operations occur on tables containing the BLOB columns. Which configuration change must the solutions architect apply to resolve this issue and ensure successful CDC replication?

  1. A
    Modify the source database configuration to set the binlog_format parameter to MIXED to allow both statement-based and row-based replication during high-throughput updates.
  2. B
    Enable Microsoft Change Data Capture (MS-CDC) on the source database engine and start the SQL Server Agent to track updates on the BLOB columns.
  3. Configure the binlog_row_image parameter to FULL in the source database parameters, and restart the source database instance.Answer
  4. D
    Configure the AWS DMS replication task settings to enable Inline LOB mode with a MaxLobSize parameter of 0 to force immediate replication of all BLOB data.

Answer

Configure the binlog_row_image parameter to FULL in the source database parameters, and restart the source database instance.
The correct answer is to configure the binlog_row_image parameter to FULL on the source MySQL database. AWS DMS requires that the source MySQL database has binlog_format set to ROW and binlog_row_image set to FULL. When an update occurs on a row containing a BLOB or LOB column, a setting of MINIMAL or NOBLOB would omit unchanged columns from the binary log. This prevents AWS DMS from acquiring the full row image required to apply the update to the target database, resulting in task failures.

Step-by-Step Solution

1
Analyze the source database engine and migration requirements.
The source is MySQL 8.0, and the migration requires Change Data Capture (CDC) via AWS DMS.
Identifying the database engine helps narrow down the correct configuration parameters for binary logging.
2
Identify the cause of the task failure during update operations on LOB/BLOB tables.
AWS DMS requires a complete before-and-after image of the modified row to perform CDC successfully, especially for tables containing large objects.
If the binary log row image is set to MINIMAL or NOBLOB, the binlog does not include the LOB columns unless they are explicitly modified, which causes the AWS DMS CDC task to fail on update events.
3
Select the parameter that controls the binary log row image in MySQL.
Setting binlog_row_image to FULL ensures all column values (including unmodified LOBs) are written to the binary log.
This configuration satisfies the prerequisite for AWS DMS CDC replication from a MySQL source database.

Key Concept

AWS DMS source database prerequisites for MySQL CDC replication
Estimated Time:2m 30s
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic