RDS SQL Server Restore Retain CDC

Question

Our PROD database ran into disk space issues and we had a lot of CDC data that was not created and was being retained in the SQL server transactional logs.

CDC was enabled at the table level.

When the PROD database was rebooted, CDC was able to consume the transaction logs again and produce the needed CDC data.

However the Microsoft default retention of 7 days has now caused some of that CDC data to be lost before we can process it.

Before the RDS instance was rebooted a snapshot was taken to preserve the state of the database.

However when restoring that snapshot a new RDS instance the CDC data tables are not present.

Microsoft only retains CDC data in a recovered database when the KEEP_CDC option is specified from our research.

As this is an RDS snapshot and not a SQL Server native backup I cannot use that option.

Is there a way to restore from an RDS instance snapshot and instruct RDS/SQL Server to retain the CDC data?

karthikeya SS · Answer

Dear AWS Customer

Thank you for your question.

Unfortunately, during any restores in AWS like snapshot restores, point-in-time restores, and SQL Server Native restores from S3 the CDC is disabled and metadata is dropped during the restore , there is no direct way of accessing that data. But once any restore completed you can re-enable CDC and re-specify tables to track.

[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.CDC.html

For enabling the CDC for a DB instance, run the msdb.dbo.rds_cdc_enable_db stored procedure.

exec msdb.dbo.rds_cdc_enable_db 'database_name'

To track the tables :

exec sys.sp_cdc_enable_table   
   @source_schema           = N'source_schema'
,  @source_name             = N'source_name'
,  @role_name               = N'role_name'

For viewing the CDC configuration for your tables

exec sys.sp_cdc_help_change_data_capture 
--  'schema_name', 'table_name’;

I can understand the inconvenience this might bring. It is unfortunately a trade off for RDS being a managed service  with limitations and constraints, regrettably this is one of them. I would take this as a possible feature request for our internal teams to review.

RDS SQL Server Restore Retain CDC

Relevant content