SQL Server RDS Replication Failure – Troubleshooting with AWS DMS

amazon-dmsamazon-rdsreplicationsql serversql-server-2016

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?

Best Answer

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.

Related Question