DMS CDC for SQL Server - Replication

0

I am using MS-SQL Server 2012 and 2016 on-prem. I want to capture CDC from this database and load it into Redshift using DMS. Since our tables have primary keys, MS-Replication seems to be the only option that will help facilitate this. Currently replication is not enabled and I want to understand the performance impact, any gotchas, possibles issues that can arise when turning replication on.

AWS
EXPERT
Behram
asked 3 years ago1698 views
1 Answer
0
Accepted Answer

==>MS-Replication seems to be the only option that will help facilitate this. In our documentation [1] it's written "MS-CDC, to capture changes for tables without primary keys", but if you read in details that is mentioned only when MS-replication isn't possible due to missing primary key. However, you can still use "MS-CDC to capture changes for tables, with or without primary keys. MS-CDC must be enabled at the database level, and for all of the tables individually".

==>Currently replication is not enabled and I want to understand the performance impact, any gotchas, possibles issues that can arise when turning replication on. Kindly note in case of MS-Replication enabled on source database, DMS doesn't uses all replication components (i.e. Distributor, publisher & subscriber), but only distributor and publisher. However for ongoing changes, data is actually read from transaction log using sql server fn_dblog and fn_dump_dblog. So it won't have the same performance impact as you have it while running MS-replication(migrating data using Replication from sql server to sql server). As highlighted above, still if you identify that MS-replication is taking more resources, you can still go ahead and use MS-CDC to read changes.

[1] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html

profile pictureAWS
EXPERT
Viqash
answered 3 years ago

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