Examkin

Question

Difficulty: HardDatabase Migration and Schema Conversion using DMS and SCT

A company is migrating an on-premises Microsoft SQL Server database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration strategy requires a heterogeneous schema conversion followed by an initial full load and ongoing replication to minimize application downtime during the cutover window. The solutions architect is setting up the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) tasks for this migration. Which TWO actions must the solutions architect perform to ensure the schema is successfully converted and ongoing changes are replicated correctly? (Select two.)

  1. Enable Change Data Capture (CDC) on the source SQL Server database and all tables scheduled for migration, and ensure that the SQL Server Agent service is running on the source server.Answer
  2. Use the AWS Schema Conversion Tool (AWS SCT) to convert the source schema, apply the converted DDL to the target Aurora PostgreSQL DB cluster, and install the AWS SCT Extension Pack on the target database to emulate SQL Server features that do not have direct PostgreSQL equivalents.Answer
  3. C
    Configure the AWS DMS replication task to capture changes using database transaction logs directly without enabling MS-CDC or MS-Replication, assuming AWS DMS will automatically read the transaction logs of any SQL Server configuration.
  4. D
    Configure AWS DMS to automatically convert the SQL Server database schema, stored procedures, and triggers on-the-fly during the Full Load phase of the replication task.
  5. E
    Set up an AWS DMS target endpoint with the target engine configured as Microsoft SQL Server, then use PostgreSQL compatibility mode in the DMS task settings to allow the schema to convert dynamically.

Answer

Enable Change Data Capture (CDC) on the source SQL Server database and all tables scheduled for migration, ensure that the SQL Server Agent service is running on the source server, use the AWS Schema Conversion Tool (AWS SCT) to convert the source schema, apply the converted DDL to the target Aurora PostgreSQL DB cluster, and install the AWS SCT Extension Pack on the target database.
To migrate from Microsoft SQL Server to Aurora PostgreSQL-Compatible Edition (a heterogeneous migration), the AWS Schema Conversion Tool (AWS SCT) must be used to convert the source schema and apply it to the target database. Since PostgreSQL does not natively support some SQL Server built-in functions, the AWS SCT Extension Pack is installed on the target database to emulate these features. Additionally, for ongoing replication (CDC) using AWS DMS with a SQL Server source, you must enable MS-CDC on the source database and tables, and the SQL Server Agent must be running to process CDC logs.

Step-by-Step Solution

1
Use AWS SCT to perform schema conversion and generate a migration assessment report.
Identify incompatibilities and obtain SQL DDL scripts tailored for the target Aurora PostgreSQL DB cluster.
AWS DMS does not convert schema objects like stored procedures and triggers, requiring SCT for heterogeneous migrations.
2
Apply the converted DDL schema to the target Aurora PostgreSQL database and install the AWS SCT Extension Pack.
Create the target schema structures and provide emulation for non-native SQL Server functions in PostgreSQL.
Ensures that application queries and database logic converted from SQL Server can execute correctly on PostgreSQL.
3
Enable MS-CDC on the source SQL Server database and tables, and verify the SQL Server Agent service is running.
Prepare the source database to track ongoing data changes and expose them in log tables.
AWS DMS requires Change Data Capture (CDC) or MS-Replication to capture ongoing changes from a SQL Server source.
4
Create and execute an AWS DMS replication task configured for Full Load and ongoing Change Data Capture (CDC).
Initial data is copied, and incremental changes are continuously replicated to the target.
Allows near-zero downtime migration by syncing the source and target databases until the cutover window.

Key Concept

Heterogeneous database migration from SQL Server to PostgreSQL using AWS SCT for schema conversion (with Extension Pack emulation) and AWS DMS for data migration (requiring MS-CDC setup on the source for ongoing replication).
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic