A company is migrating an on-premises Microsoft SQL Server database to an Amazon Aurora PostgreSQL-Compatible Edition cluster using AWS Database Migration Service (DMS) and the AWS Schema Conversion Tool (SCT). The database schema has already been successfully converted and applied. The solutions architect needs to run a DMS replication task with Change Data Capture (CDC) to perform a minimal-downtime migration. However, during testing, the DMS task completes the full load phase but fails to replicate any ongoing data changes (CDC) and stalls. Which of the following actions must the solutions architect perform on the source database to enable successful Change Data Capture (CDC) replication? (Select TWO.)
- Enable Change Data Capture (MS-CDC) for the database and the specific tables slated for replication.Answer
- Ensure that the SQL Server Agent service is running on the source database instance.Answer
- CConfigure binary logging (binlog) on the database and set the binlog format to ROW.
- DEnable supplemental logging for all replicated tables using ALTER DATABASE and ALTER TABLE commands.
- EEnable logical replication by setting the wal_level parameter to logical in the parameter group.
Answer
Enable Change Data Capture (MS-CDC) for the database and specific tables, and ensure that the SQL Server Agent service is running on the source database instance.
For Microsoft SQL Server sources, AWS DMS reads changes from the SQL Server transaction log using MS-CDC (Microsoft Change Data Capture). This requires that MS-CDC is explicitly enabled on both the database and the tables being replicated. Additionally, the SQL Server Agent service must be active because it drives the CDC capture job that reads from the transaction log and populates the change tables that DMS queries.
Step-by-Step Solution
Key Concept
AWS Database Migration Service (DMS) Change Data Capture (CDC) prerequisites for Microsoft SQL Server sources.