Failure with ongoing replication from SQL Server RDS using AWS DMS

Question

I'm trying to replicate from an SQL Server RDS database using DMS, and running into problems.

I am able to load all data from one table into the destination (~1M rows) as a single 'Migrate existing data' Task. When I run a second 'Replicate data changes only' Task, it runs for a few minutes and then fails.

No rows are copied into the destination by the second task before it fails.

These lines in the error log seem relevant:

[SOURCE_CAPTURE ]W: Capture functionalities could not be set. RetCode: SQL_ERROR SqlState: 42S02 NativeError: 208 Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'sysarticles'. Line: 1 Column: -1 (sqlserver_log_utils.c:1712)

[SOURCE_CAPTURE ]E: Bad Envelope : , Lsn=00050649:000a3cb1:0166,operation=5,TxnId=0000:34915a92,Tablename=COMMIT,PageId=,slotId=0,timeStamp=2018-08-01T08:15:30.233,dataLen=0, LCX=99, , >Invalid data context / LCX Code encountered for TXN operation. [1020203] (sqlserver_log_processor.c:401)

The source database is Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)

I've tested this against a number of destination databases (MySQL, PostgreSQL, SQL Server) but always get the same result.

How can I get ongoing replication to work? Is there anywhere else I should look?

Answer 1

Need to disable SQL Server Change tracking and enable RDS Change Tracking.

Find any tables which are enabled for CDC with:

SELECT s.name as Schema_name, t.name AS Table_name, tr.* FROM sys.change_tracking_tables tr INNER JOIN sys.tables t on t.object_id = tr.object_id INNER JOIN sys.schemas s on s.schema_id = t.schema_id;

Then for each of those tables table run:

ALTER TABLE dbo.Products DISABLE CHANGE_TRACKING;

Disable CDC on the database:

alter database AdventureWorks set change_tracking = off

Enable RDS CDC on the database:

exec msdb.dbo.rds_cdc_enable_db 'AdventureWorks'

Enable RDS CDC for each table:

exec sys.sp_cdc_enable_table   
   @source_schema           = N'dbo'
,  @source_name             = N'Products'
,  @role_name               = NULL;

Finally rerun your DMS tasks and they should work.

Answer 2

I had the same issue as the original post. SQL Server Change tracking has never been enabled and both the database and all the tables are setup as outlined above.

Solution on my end was to restart the Replication Instance.