CODEWITHSUNDEEP
sql-serveramazon-web-servicesreportingdatabase-migrationdisaster-recovery
DevOps One
DevOps One

Reputation: 21

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

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 ?

Upvotes: 2

Views: 2768

Answers (2)

EnterpriseCloud Guru
EnterpriseCloud Guru

Reputation: 1

Any workarounds or alternative solutions ?

You can create RDS SQL Server read-replicas to offload primary for reporting, as well as to continuously geo-replicate RDS SQL Server cross region from US East to West coast with CloudBasic's RDS SQL Server HA/DR tool available on the AWS Marketplace: https://aws.amazon.com/marketplace/pp/B00OU0PE5M


All SQL Server editions, including Web Edition are supported.

Launch it in the same AWS VPC as your RDS SQL Server source instance. In the New Replication setup section, ensure to select SQL Server-to-SQL Server replication (as the tool also streams data from SQL Server to Redshift and S3 data lakes).

Upvotes: 0

Adnan
Adnan

Reputation: 21

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

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

Upvotes: 1

Related Questions