2

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?

2 Answers 2

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.

3
  • What if we are using our On-Premises SQL Server as the source?
    – Sal
    Oct 23, 2019 at 14:48
  • Not sure sorry, we had a number of problems running DMS on SQL and used CloudBasic for our migration from RDS->Self Hosted instead. It worked really well. Oct 24, 2019 at 4:36
  • 1
    In my case, I have enabled RDS CDC on RDS SQL Server which is a source for my DMS but it is getting failed with "E: Failed to access LSN '0000009a:0000016d:0001' in the backup log sets since BACKUP/LOG-s are not available." any idea?
    – Pand005
    Jan 4, 2021 at 20:08
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.