Examkin

Question

Difficulty: HardDatabase Migration and Schema Conversion using DMS and SCT

A company is planning to migrate a highly transactional Microsoft SQL Server database located on-premises to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration must be completed with minimal downtime. The source database includes several tables that do not have primary keys, as well as multiple tables containing PDF documents stored in `VARBINARY(MAX)` columns. The solutions architect is designing the replication strategy using AWS Database Migration Service (DMS) and schema conversion using the AWS Schema Conversion Tool (SCT). Which combination of actions should the solutions architect take to meet these requirements? (Select two.)

  1. Enable MS-CDC on the source SQL Server database and the tables without primary keys, and ensure the SQL Server Agent is running on the source database server.Answer
  2. Create two separate AWS DMS replication tasks: one for tables containing `VARBINARY(MAX)` columns configured with Limited LOB mode and a defined maximum LOB size, and another task for the remaining tables.Answer
  3. C
    Enable MS-Replication on the source SQL Server database to capture Change Data Capture (CDC) modifications for the tables without primary keys.
  4. D
    Configure a single AWS DMS replication task using Full LOB mode for all tables, and increase the LOB chunk size parameter to match the size of the largest PDF file.
  5. E
    Install the AWS SCT database extraction agent on the source SQL Server instance to capture transaction log changes and stream them to the target cluster.

Answer

To perform a near-zero downtime migration of a SQL Server database containing tables without primary keys and LOB columns to Aurora PostgreSQL, enable MS-CDC on the database and tables without primary keys with SQL Server Agent running, and split the migration into two separate AWS DMS tasks: one using Limited LOB mode with a defined maximum LOB size for the tables with LOB columns, and another for the remaining tables.
The correct strategy involves enabling MS-CDC on the source SQL Server database and tables without primary keys, ensuring the SQL Server Agent is running. MS-Replication cannot support tables without primary keys. Additionally, creating two separate AWS DMS tasks—one with Limited LOB mode for tables with large LOBs and one for standard tables—prevents LOB processing bottlenecks from slowing down the entire replication stream.

Step-by-Step Solution

1
Address the replication of tables without primary keys by configuring MS-CDC.
MS-CDC is enabled on the SQL Server database and the tables lacking primary keys, and the SQL Server Agent is running to read the transaction logs.
AWS DMS requires MS-CDC for replication of tables without primary keys, as MS-Replication strictly requires primary keys.
2
Configure separate replication tasks to optimize LOB handling.
Two tasks are created: one containing tables with LOBs configured with Limited LOB mode, and another containing the remaining tables.
Splitting tables prevents the slow transfer of large LOBs from blocking or delaying replication of the rest of the database, while Limited LOB mode ensures optimal throughput.

Key Concept

Replicating SQL Server databases with tables lacking primary keys and containing LOBs requires selecting MS-CDC over MS-Replication, and separating LOB tables into a distinct task using Limited LOB mode for performance optimization.
Estimated Time:2m 30s
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic