Examkin

Question

Difficulty: Very hardDatabase Migration and Schema Conversion using DMS and SCT

A financial company is migrating an on-premises Microsoft SQL Server database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration must have minimal downtime. The database contains tables with XML and VARBINARY columns (large binary objects, or LOBs) that are up to 256 KB in size. The source database utilizes active transactions and requires Change Data Capture (CDC) to replicate ongoing changes. The database migration team uses the AWS Schema Conversion Tool (SCT) and AWS Database Migration Service (DMS).

Which three actions should the Solutions Architect perform to prepare the source database and configure AWS DMS and SCT for a successful migration? (Select three.)

  1. Ensure that the SQL Server Agent service is running on the source server, enable Microsoft Change Data Capture (MS-CDC) on the source database and tables, and grant the AWS DMS database user account the db_owner role.Answer
  2. Install the AWS Schema Conversion Tool (SCT) Extension Pack on the target Aurora PostgreSQL DB cluster to emulate Microsoft SQL Server system functions, schema components, and database objects.Answer
  3. Configure the AWS DMS replication task to use Limited LOB mode, and set the maximum LOB size to 256 KB.Answer
  4. D
    Configure the AWS DMS replication task to use Full LOB mode, and set the LOB chunk size to 256 KB to allow parallel replication of the XML and VARBINARY columns without performance degradation.
  5. E
    Install the AWS SCT extraction agent on the source SQL Server host to capture real-time transaction logs, and stream the changes to Amazon Kinesis Data Streams for loading into Aurora PostgreSQL.
  6. F
    Configure AWS DMS to read the active transaction logs directly from the SQL Server source without enabling MS-CDC or SQL Server Agent, using a database user account restricted to the db_datareader role to minimize security privileges.

Answer

The correct configuration requires enabling MS-CDC and the SQL Server Agent on the source database with a user account in the db_owner role, applying the AWS SCT Extension Pack on the target Aurora PostgreSQL DB cluster to emulate SQL Server system functions, and setting the AWS DMS task to use Limited LOB mode with a maximum LOB size of 256 KB.
For a successful heterogeneous CDC migration from SQL Server to Aurora PostgreSQL with minimal downtime, MS-CDC and SQL Server Agent must be active on the source database, and the DMS user must have the db_owner role to read transaction logs. Since the XML and VARBINARY columns (LOBs) have a maximum size of 256 KB, using Limited LOB mode set to 256 KB provides optimal replication speed compared to Full LOB mode, while avoiding truncation. Applying the AWS SCT Extension Pack on the target Aurora PostgreSQL cluster ensures compatibility by emulating SQL Server-specific system functions and schemas.

Step-by-Step Solution

1
Prepare the source SQL Server database for change data capture.
Ensure the SQL Server Agent service is running, enable MS-CDC on the database and specific tables, and assign the db_owner role to the migration user.
AWS DMS relies on MS-CDC to read the transaction logs on self-managed SQL Server databases. The SQL Server Agent must run to manage CDC jobs, and db_owner permissions are necessary to access the CDC tables.
2
Convert and apply the schema using AWS SCT.
Generate the PostgreSQL target schema using AWS SCT and apply the generated SQL. Install the AWS SCT Extension Pack on the target Aurora PostgreSQL cluster.
The SCT Extension Pack emulates SQL Server system functions, schemas, and data types on the target PostgreSQL engine to ensure compatibility of the converted schema.
3
Configure LOB settings in the AWS DMS replication task.
Select Limited LOB mode and set the Max LOB size to 256 KB.
Since the source database's LOB columns (XML and VARBINARY) are known to be up to 256 KB, using Limited LOB mode with this maximum size pre-allocates memory and transfers LOBs inline. This provides much higher replication performance than Full LOB mode while preventing data truncation.

Key Concept

Heterogeneous database migration from SQL Server to Aurora PostgreSQL requires source configuration (MS-CDC, running Agent, and db_owner permissions), target emulation (SCT Extension Pack), and task LOB tuning (Limited LOB mode) to balance data integrity and replication performance.
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic