By using AWS re:Post, you agree to the Terms of Use
/How AWS DMS CDC is working successfully without CDC On-premise MSSQL CDC prerequisites config?/

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

0

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 https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Prerequisites

Running below query on on-premise MSSQL 2012 instance returns an error, ref: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Prerequisites

use uat_testdb
EXEC sys.sp_cdc_enable_db

Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db, Line 14 [Batch Start Line 0] This instance of SQL Server is the Standard Edition (64-bit). Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions. It looks ongoing replication CDC feature is supported only from MSSQL standard edition from 2016 SP1 and later.

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? Thanks.**

1 Answers
1
Accepted Answer

SQL Server as source can be configured for onprem db using SQL Server replication please note ms-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.

answered 17 days ago
  • Thanks, Its helpful. Also, noticed that, 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.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions