What happens when a source endpoint database is restored that is part of a data migration task


Using AWS DMS there are source endpoints (On-Prem SQL Servers) and associated target endpoints (S3).

Could someone with experience tell me what happens when a source endpoint (sql-server) gets restored, and CDC is re-enabled on that database.

For the associated data migration task, I hope that DMS will see this a requiring a new snapshot/initial load (in transactional-replication parlance). If not, how does one contend with this. These restores can happen with databases from multiple servers and on a frequent basis.

Normally, I would test this out, however, our dev accounts are locked down with limited console access, everything has to be scripted in terraform and this would take some time to get a "quick test" up and running.

1 Answer

for CDC DMS relies on DDL/DML transactions captured in source database transaction log. This changes are populated when we enable ms-cdc. As the source db is getting restored the chain of transactions tracked by lsn (from active transaction log) is lost. Thus you need to restart the DMS task so that initial load is reinitiated.

answered 2 years ago
  • Thank you for the quick and thorough reply. Do you happen to know what happens to the files in the S3's dbo and cdc folders, do they get deleted and recreated as replication builds up, or overwritten?

  • it is based on task setting if it is set to do_nothing they will be retained and new files will be added. while if you use truncate or "drop and recreate" they will be cleared

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