CODEWITHSUNDEEP
sql-serveramazon-web-servicesamazon-rdsdms
John Black
John Black

Reputation: 355

AWS RDS SQL Server Restore Retain CDC tables

I'm trying to create a live replica of an RDS SQL Server database, but I need it to be a separate database, not just an RDS replica of the existing instance.

One way to achieve this would be to use AWS DMS in full load + CDC mode. This approach would perform an initial full load and then transition to CDC mode to keep the target database synchronized. While this satisfies the requirement, I’d like to optimize the initial load phase because the source database is quite large. I’m concerned about the potential load DMS might place on the source database and the potentially long runtime of full load.

As an alternative, I tested backing up and restoring the source database to the target database. However, I’m unsure how to configure the DMS CDC job after the restore, as I don’t know the LSN (Log Sequence Number) that would be ideal to use as a starting point. According to this AWS discussion, RDS backups don’t retain CDC tables, it's just not an option, unlike in regular SQL server.

Are there any alternative approaches to ensure data consistency when enabling CDC, with a correctly configured LSN or offset? Is timestamp a reliable enough option, and what timestamp to use exactly? Any other options?

Upvotes: 0

Views: 48

Answers (0)

Related Questions