Examkin

Question

Difficulty: MediumDatabase Migration and Schema Conversion using DMS and SCT

A Solutions Architect is migrating an on-premises Microsoft SQL Server database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The schema conversion has been completed using the AWS Schema Conversion Tool (SCT). The architect configures an AWS Database Migration Service (DMS) task with Change Data Capture (CDC) for ongoing replication. During testing, the architect notices that updates and deletes on several source tables are not being replicated to the target Aurora DB cluster, while inserts are successfully replicated. Which of the following is the most likely cause of this behavior, and the correct resolution?

  1. A
    The source SQL Server Agent is not running, which prevents the Change Data Capture (CDC) jobs from capturing UPDATE and DELETE transactions for tables without primary keys. The architect must start the SQL Server Agent and enable MS-Replication.
  2. B
    The AWS DMS task is configured to use Microsoft Change Data Capture (MS-CDC) instead of MS-Replication. The architect must reconfigure the source endpoint to use MS-Replication, which natively supports capturing updates and deletes on tables without primary keys.
  3. The affected tables lack a primary key or a unique index, which limits AWS DMS to replicating only INSERT statements during CDC. The architect must define a primary key or a unique index on these tables in the source database.Answer
  4. D
    The AWS Schema Conversion Tool (SCT) extension pack has not been applied to the target Aurora PostgreSQL DB cluster, which is required to emulate and handle updates and deletes on tables without a natural primary key. The architect must apply the extension pack and enable DMS validation.

Answer

The affected tables lack a primary key or a unique index, which limits AWS DMS to replicating only INSERT statements during CDC. The architect must define a primary key or a unique index on these tables in the source database.
The correct option is correct because AWS DMS has a specific replication limitation when using Microsoft SQL Server as a source: if a table does not have a primary key or a unique index, DMS can only replicate INSERT statements during Change Data Capture (CDC). Any UPDATE or DELETE operations on these tables are silently ignored. Defining a primary key or unique index on the source tables resolves this limitation.

Step-by-Step Solution

1
Identify the tables in the source SQL Server database that are experiencing missing update and delete replication during the Change Data Capture (CDC) phase.
A list of source tables lacking primary keys or unique indexes is identified.
AWS DMS requires a primary key or unique index to track and apply row-level UPDATE and DELETE operations during CDC on SQL Server databases.
2
Define a primary key or a unique index on each of the identified tables in the source SQL Server database.
Each table now has a unique identifier for its rows.
This allows the AWS DMS task to generate and execute the corresponding UPDATE and DELETE statements on the target PostgreSQL database.
3
Restart the AWS DMS task to ensure changes are correctly read and applied.
The DMS CDC phase successfully replicates INSERT, UPDATE, and DELETE operations to the Amazon Aurora PostgreSQL DB cluster.
Once the source tables have unique identifiers, DMS can map the transaction log changes to the target rows.

Key Concept

AWS DMS Change Data Capture (CDC) limitations with SQL Server source databases.
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic