AWS DMS does not support CDC/Change tracking for RDS SQL Server.

Question

It does not seem that AWS DMS (Database Migration Service) supports CDC (change tracking) for RDS SQL Server. I am trying to create RDS SQL Server read-replicas to offload primary, for reporting. Also need a solution to continuously geo-replicate RDS SQL Server cross region from US East to West coast.

Any workarounds or alternative solutions ?

CDC is different to Change Tracking. msdn.microsoft.com/en-us/library/bb933994(v=sql.110).aspx CDC is "Change Data Capture" and you're right, it's not supported on RDS docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… " Amazon RDS currently does not support the following SQL Server features... Change Data Capture (CDC) - Consider using Change Tracking as an alternative to CDC. " I am looking at Change Tracking, but not sure if that will work with DMS. I think it will be fairly manual to set up, but potentially a good start for ETL of staging data. — Davos

Adnan Adnan · Accepted Answer · 2018-05-02T10:35:32

As a legacy method, SQL CDC does not work as it require a user with sysadmin privs in RDS which is impossible.

So, there is a workaround by Amazon to get this done. Follow below and use user who has db_owner. 1. --Enable CDC for RDS DB Instance

exec msdb.dbo.rds_cdc_enable_db '<database name>'

2.--Begin tracking a table

use <dbname_where_cdc_enabled>
exec sys.sp_cdc_enable_table   
@source_schema           = N'<source_schema>'
,  @source_name          = N'<source_name>'
,  @role_name            = N'<role name>'
,  @capture_instance     = '<capture_instance>'

--View CDC configuration

exec sys.sp_cdc_help_change_data_capture

That should do the work. For further clarification follow link. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.CDC.html

AWS DMS does not support CDC/Change tracking for RDS SQL Server.

2 Answers