Examkin

Question

Difficulty: MediumDatabase Migration and Schema Conversion using DMS and SCT

A company is migrating a self-managed Microsoft SQL Server database running on-premises to an Amazon Aurora MySQL-Compatible Edition DB cluster. A solutions architect uses the AWS Schema Conversion Tool (SCT) to convert the database schema. The migration strategy requires continuous replication to keep the target database in sync with the source database until the cutover window. The solutions architect configures an AWS Database Migration Service (AWS DMS) replication task with the migration type set to 'Full load and CDC'. During a test run, the full load phase completes successfully, but subsequent transactional updates on the source SQL Server database are not reflected in the target Aurora DB cluster. Which of the following is the most likely cause of this replication failure?

  1. A
    The target Amazon Aurora MySQL DB cluster was not configured with binary logging enabled in its cluster parameter group.
  2. Microsoft Change Data Capture (MS-CDC) or SQL Server Replication was not enabled on the source SQL Server database and tables.Answer
  3. C
    The AWS DMS replication task was configured with the target table preparation mode set to 'Do nothing' instead of 'Truncate'.
  4. D
    AWS Application Migration Service (MGN) was not deployed on the source SQL Server host to replicate the transactional changes at the block level.

Answer

Microsoft Change Data Capture (MS-CDC) or SQL Server Replication was not enabled on the source SQL Server database and tables.
For AWS Database Migration Service (AWS DMS) to capture ongoing changes (CDC) from a self-managed Microsoft SQL Server source database, you must enable Microsoft Change Data Capture (MS-CDC) or MS-Replication on the source database and tables. This generates the necessary transaction logs that AWS DMS reads to capture changes. If this configuration is omitted, the full load phase completes successfully, but subsequent transactional updates will not be replicated.

Step-by-Step Solution

1
Analyze the migration symptom: the initial full load completed successfully, but subsequent transactional updates (CDC) are not being replicated.
Identify that the issue lies specifically with capturing change data (CDC) from the source database, not with target writing or connectivity.
This narrows down the troubleshooting scope to the source-side transaction logging configuration.
2
Recall the prerequisites for SQL Server when acting as a source for AWS DMS CDC tasks.
Determine that AWS DMS requires SQL Server Change Data Capture (MS-CDC) or MS-Replication to be enabled on the database and tables to capture ongoing transactions.
AWS DMS reads the SQL Server active transaction logs to replicate changes, which requires these SQL Server native features.
3
Evaluate the choices to find the one addressing the source database CDC prerequisites.
Confirm that failing to enable MS-CDC or SQL Server Replication on the source is the direct cause of this replication stall.
Without MS-CDC/Replication, the source database does not produce the necessary transaction log entries that AWS DMS expects.

Key Concept

AWS DMS CDC requirements for SQL Server source databases
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic