How AWS DMS CDC is working successfully without CDC On-premise MSSQL CDC prerequisites config?-CodePudding

We're using DMS for CDC Only migration for the time b/w point in time restore and current DB state, i.e AWS DMS to replicate changes as of the point in time at which you started your bulk load to bring and keep your source and target systems in sync.

We've configured AWS DMS (CDC Only) with source endpoint to On-premise SQL Server 2012 (Standard Edition) and Target endpoint with AWS RDS MSSQL 2019 (Standard Edition). By looking into AWS CDC pre-requisites documentation

Could you please suggest if there any other workaround to complete CDC without upgrading our on-premise MSSSQL Standard Edition 2012 to Std Edition 2016 / Enterprise Edition?

However, without applying this CDC prerequisites config settings at on-premise DB instance, we can see the ongoing and replication b/w on-premise and RDS DBs instances statistics that shows sync updates of Inserts and Deletes. (Based on the testing target RDS DB instance sync. happening only for Insert and Delete operations of on-premise source db not for any updates)

Could you please confirm/clarify if those CDC pre-requisites config are mandatory since we could see the replication successfully on DMS and why we're not getting any error /warning messages on AWS DMS for missing CDC prerequisites config. settings?

CodePudding user response：

I've got the correct answer from AWS rePost as shown below. Thanks. Ref: https://repost.aws/questions/QU4N24P3_XTZqlI5I1lXRv5w#ANsHAA6SXsSBuzi1IHIa0TrA CDC is needed for the tables don't have the primary keys. If all of the tables have primary keys, we don't need to setup CDC. (i.e. To capture changes for tables without primary keys. Enable MS-CDC at the database level and for all of the tables individually.)

If your database isn't configured for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but only INSERT/DELETE DML events are captured. UPDATE and TRUNCATE TABLE events are ignored.

CDC is needed if you are planning to migrate tables not having primary key. As a workaround, may be not necessarily feasible, you can add a temporary primary key to tables without primary key. once migration you can remove this additional primary keys.