Examkin

Question

Difficulty: MediumDatabase Migration and Schema Conversion using DMS and SCT

A solutions architect is planning the heterogeneous migration of a 5 TB on-premises Microsoft SQL Server database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration must minimize application downtime. The solutions architect has already used the AWS Schema Conversion Tool (AWS SCT) to convert the schema and apply it to the target Aurora DB cluster. An AWS Database Migration Service (AWS DMS) replication task with 'Full load and ongoing replication' is configured. During testing, the replication task successfully completes the full load phase, but changes made on the source database after the full load started are not being replicated to the target database. Which of the following is the required configuration change to enable ongoing replication?

  1. Enable Microsoft Change Data Capture (MS-CDC) on the source database and the tables to be migrated, and ensure that the SQL Server Agent service is running.Answer
  2. B
    Enable binary logging on the source database instance by setting the parameter log_bin to ON, and configure the binlog format to ROW.
  3. C
    Reclassify the migration path as a Replatform, and use AWS Application Migration Service (AWS MGN) to continuously replicate the SQL Server operating system and database volumes.
  4. D
    Request an AWS Snowball Edge device to ship the SQL Server transaction log backups to Amazon S3, and configure AWS DMS to use the S3 bucket as the replication source for the CDC phase.

Answer

Enable Microsoft Change Data Capture (MS-CDC) on the source database and the tables to be migrated, and ensure that the SQL Server Agent service is running.
To capture ongoing changes from a Microsoft SQL Server source database, AWS Database Migration Service (AWS DMS) requires Microsoft Change Data Capture (MS-CDC) to be enabled on the database and target tables, and the SQL Server Agent service must be running to process transaction logs. This allows AWS DMS to read change tables and replicate new transactions to the target database.

Step-by-Step Solution

1
Analyze the database engine mismatch between source (SQL Server) and target (Aurora PostgreSQL).
Identify that the migration is heterogeneous, meaning the schema must be converted (completed by AWS SCT) and data replicated with CDC to minimize downtime.
Helps narrow down the required replication technologies and target configurations.
2
Determine the prerequisites for AWS DMS to perform Change Data Capture (CDC) on a SQL Server source.
Identify that AWS DMS requires either MS-CDC or MS-Replication, along with an active SQL Server Agent, to capture ongoing transaction logs.
Verifies the missing configuration causing the task to stall after the full load.
3
Evaluate the proposed options against the SQL Server prerequisites and filter out incorrect database engines or block-level tools.
Select the option specifying MS-CDC and SQL Server Agent activation, while rejecting MySQL parameters, block-level host replication, and offline physical transfers.
Arrives at the correct configuration required for SQL Server CDC.

Key Concept

AWS DMS source database prerequisites for Change Data Capture (CDC) on Microsoft SQL Server
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic