Examkin

Question

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

An enterprise is migrating an on-premises Microsoft SQL Server 2019 database (Enterprise Edition) configured with a two-node AlwaysOn Availability Group to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The database contains several tables with primary keys, and some tables contain large XML columns. The migration must achieve near-zero downtime. A solutions architect is setting up AWS Schema Conversion Tool (SCT) for schema conversion and AWS Database Migration Service (DMS) for continuous data replication (Change Data Capture). Which of the following actions are required to configure the source SQL Server database and the AWS DMS replication task to support ongoing replication and minimize data transfer issues? (Select two.)

  1. Enable Microsoft Change Data Capture (MS-CDC) on the source database and on the specific tables, and ensure the SQL Server Agent service is running on the active primary replica.Answer
  2. Configure the AWS DMS source endpoint to connect to the AlwaysOn Availability Group listener, and set the replication task to use Limited LOB mode with an appropriate maximum LOB size for the XML columns.Answer
  3. C
    Configure the AWS DMS source endpoint with the ApplicationIntent=ReadOnly connection attribute to direct CDC log reader queries to the secondary replica, reducing the transaction log IOPS on the primary replica.
  4. D
    Enable the SQL Server Replication feature on the passive secondary replica, and configure the AWS DMS task to use the read from backup option with a shared backup folder on Amazon S3.
  5. E
    Install the AWS SCT extraction agents on both SQL Server host nodes, and configure them to write transaction log backups directly to the DMS replication instance local storage.

Answer

Enable Microsoft Change Data Capture (MS-CDC) on the source database and tables while ensuring the SQL Server Agent is running, and configure the AWS DMS source endpoint with the Availability Group listener while setting the task to use Limited LOB mode for the XML columns.
To replicate ongoing changes from a SQL Server AlwaysOn Availability Group, MS-CDC must be enabled on the source database and tables, and the SQL Server Agent must be active on the primary replica to run the CDC capture jobs. The DMS source endpoint should connect to the Availability Group listener to maintain connection state during failovers, and Limited LOB mode is required to optimize the transfer of XML columns (treated as LOBs by DMS).

Step-by-Step Solution

1
Enable Change Data Capture on the source database and the tables slated for migration.
SQL Server generates the CDC system tables and capture jobs.
AWS DMS requires either MS-CDC or MS-Replication to identify and capture incremental changes for CDC migration.
2
Ensure the SQL Server Agent service is running on the active primary replica.
The CDC capture jobs execute and populate the change tables from the active transaction log.
If the agent is stopped, CDC change tables will not be populated, and DMS will not replicate any ongoing changes.
3
Configure the AWS DMS source endpoint to use the AlwaysOn Availability Group listener.
DMS connects to the active primary replica and automatically reconnects to the new primary after a failover.
Connecting directly to a node IP would break the replication task if a failover occurs.
4
Configure the DMS task to use Limited LOB mode for LOB columns, including the SQL Server XML data type.
DMS transfers XML data inline up to the defined maximum LOB size, enhancing throughput.
Full LOB mode is slower because it queries LOB data in a separate lookup step, while Limited LOB mode optimizes transfer speeds by pre-allocating memory.

Key Concept

Continuous replication (CDC) from SQL Server AlwaysOn Availability Groups using AWS DMS requires connecting to the primary replica, enabling MS-CDC with SQL Server Agent running, and handling XML columns as LOBs with appropriate LOB settings.
Estimated Time:3m 0s
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic