Is MS CDC possible as an ongoing replication for RDS managed SQL Server without sysadmin permissions?

Question

My industry is planning to upgrade from SQL Server 2012 to SQL Server 2016 (standard editions both), after that we plan to do replication of our prod db. We went through few AWS docs which says:

AWS DMS provides comprehensive ongoing replication of data, although it replicates only a limited amount of data definition language (DDL). AWS DMS doesn't propagate items such as indexes, users, privileges, stored procedures, and other database changes not directly related to table data.

Also:

Unlike self-managed SQL Server sources, Amazon RDS for SQL Server doesn't support MS-Replication. Therefore, AWS DMS needs to use MS-CDC for tables with or without primary keys.

Can anyone suggest what our options are given this limitation? How do we get it to work?

Is MS CDC possible on RDS managed SQL Server Standard Edition? I also read in same AWS document like:

Also, Kindly suggest on how to proceed on above or if someone has already been through similar experiences.

I read that MS replication is not possible.

Answer 1

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.

--Enable CDC for RDS DB Instance

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

--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