Examkin

Question

Difficulty: MediumDatabase Migration and Schema Conversion using DMS and SCT

A digital logistics provider is transitioning a production Microsoft SQL Server database hosted on an on-premises physical cluster to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. A database administrator has successfully completed schema conversion using the AWS Schema Conversion Tool (SCT) and applied the DDL scripts to the target Aurora DB cluster. The administrator then sets up an AWS Database Migration Service (DMS) replication task configured with a task type of 'Full load and ongoing replication'. The full load phase completes successfully, copying all baseline tables to Aurora. However, the task fails immediately upon transitioning to the Change Data Capture (CDC) phase. Which of the following actions is required to resolve this failure and ensure ongoing replication?

  1. Configure Change Data Capture (MS-CDC) on the source SQL Server database and all migrated tables, and verify the DMS source endpoint user has the appropriate sysadmin or db_owner privileges.Answer
  2. B
    Modify the target Aurora PostgreSQL DB cluster parameter group to set rds.logical_replication to 1 and restart the cluster to enable CDC support.
  3. C
    Export native SQL Server backup files to an Amazon S3 bucket, and use the AWS Schema Conversion Tool (SCT) to restore the database backup directly to the Aurora DB cluster.
  4. D
    Create a new AWS DMS replication task using only the 'Ongoing replication' (CDC) option, specifying the MS-Replication distribution database on the target Aurora cluster.

Answer

Configure Change Data Capture (MS-CDC) on the source SQL Server database and all migrated tables, and verify the DMS source endpoint user has the appropriate sysadmin or db_owner privileges.
For AWS DMS to perform ongoing Change Data Capture (CDC) from SQL Server, Change Data Capture (MS-CDC) or MS-Replication must be configured on the source database and tables. The database user configured in the DMS source endpoint also requires administrative privileges, such as sysadmin or db_owner roles, to access the transaction log files and CDC tables.

Step-by-Step Solution

1
Analyze the AWS DMS replication task status to verify if baseline data copied successfully but failed when transitioning to Change Data Capture (CDC).
Confirming the task fails immediately after full load points to a missing source-side transactional logging configuration.
Ongoing replication requires the source database engine to track transactional updates in a format AWS DMS can read.
2
Enable Change Data Capture (MS-CDC) on the source Microsoft SQL Server database and each individual table being replicated.
The SQL Server database starts logging insert, update, and delete transactions in internal change tables.
AWS DMS requires these CDC logs to identify and apply ongoing changes to the target database.
3
Verify and grant the necessary permissions (sysadmin or db_owner database roles) to the user account configured in the AWS DMS source endpoint.
The DMS task receives authorization to query the transaction logs and MS-CDC tables.
Without these permissions, the source database will deny access, causing the replication task to fail.

Key Concept

AWS DMS requires source-side transactional logging configuration (such as MS-CDC for SQL Server) and appropriate database permissions to execute Change Data Capture (CDC) replication.
Estimated Time:2m 0s
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic