Examkin

Question

Difficulty: HardDatabase Migration and Schema Conversion using DMS and SCT

A company is planning to migrate its on-premises Microsoft SQL Server database to an Amazon Aurora MySQL-Compatible Edition DB cluster. The migration must occur with minimal downtime, requiring continuous data replication via Change Data Capture (CDC). The source database contains tables with complex foreign key constraints, and several columns contain VARCHAR(MAX) data with sizes up to 128 KB. A Solutions Architect will use the AWS Schema Conversion Tool (SCT) to convert the schema and AWS Database Migration Service (DMS) for the data migration.

Which of the following actions should the Solutions Architect take to ensure a successful and optimized migration? (Select two.)

  1. Configure the target Amazon Aurora MySQL endpoint in AWS DMS with the extra connection attribute AfterConnectScript=SET foreign_key_checks=0;Answer
  2. Configure the AWS DMS replication task to use Limited LOB mode and set the Max LOB size parameter to 128 KB.Answer
  3. C
    Enable Change Data Capture (CDC) on the source SQL Server database, and ensure that the SQL Server Agent service is stopped to release locks on the active transaction log.
  4. D
    Configure the AWS DMS replication task to use Full LOB mode with a LOB chunk size of 128 KB to optimize data transfer for the VARCHAR(MAX) columns.
  5. E
    Install and apply the AWS SCT extension pack directly on the source Microsoft SQL Server database to emulate MySQL-compatible SQL functions.

Answer

To ensure a successful and optimized migration, the Solutions Architect should configure the target Amazon Aurora MySQL endpoint in AWS DMS with the extra connection attribute AfterConnectScript=SET foreign_key_checks=0; to disable foreign key checks during migration, and configure the AWS DMS replication task to use Limited LOB mode with the Max LOB size parameter set to 128 KB.
Disabling foreign key constraints on the target database during the migration prevents referential integrity violations during parallel table loads. Using Limited LOB mode with the Max LOB size set to 128 KB ensures LOB data is transferred inline with the rest of the row data without being truncated, providing optimal performance.

Step-by-Step Solution

1
Identify the LOB requirements and performance constraints.
Recognize that Limited LOB mode is optimal for known small LOB sizes (up to 128 KB) to avoid the separate lookup overhead of Full LOB mode.
To optimize replication throughput for VARCHAR(MAX) fields.
2
Address target foreign key constraints during parallel load.
Set the AfterConnectScript target endpoint attribute to SET foreign_key_checks=0;.
AWS DMS loads tables in parallel, which can cause foreign key constraint violations if child rows are inserted before parent rows.
3
Verify source CDC prerequisites.
Ensure MS-CDC is enabled and the SQL Server Agent is running.
SQL Server Agent runs the CDC capture jobs required by AWS DMS.

Key Concept

Optimizing heterogeneous migrations with AWS DMS using custom target endpoint connection attributes and LOB mode settings.
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic