Connect DMS to SQL Sever Read-Replica for CDC Ongoing Replication

While it's understandable that you're looking for ways to optimize your replication process, connecting AWS DMS directly to a SQL Server read replica for Change Data Capture (CDC) and ongoing replication is not a supported or recommended configuration.

AWS DMS is designed to work with the primary SQL Server instance for ongoing replication. This is because CDC relies on reading the transaction logs of the source database, which contain the record of all changes made to the database. These transaction logs are only fully available and up-to-date on the primary instance.

Read replicas in SQL Server are typically asynchronous, meaning there can be a delay between when changes occur on the primary and when they are reflected on the replica. This delay is likely what you're observing when you check the replica after seeing updates on the primary.

To address the issue of delayed data on the replica, you might consider the following approaches:

1. Optimize the replication process between your primary SQL Server and its read replicas. This could involve adjusting network settings, improving hardware resources, or fine-tuning SQL Server's replication settings.

2. If the load on your primary server is a concern, you could look into optimizing your AWS DMS configuration. This might include adjusting the task settings, modifying the replication instance size, or reviewing your table mappings to ensure you're only replicating necessary data.

3. Ensure that your SQL Server is properly configured for CDC. This includes setting the recovery model to Full or Bulk-logged, enabling full backups, and configuring the CDC capture job parameters appropriately.

4. Monitor the transaction log size and ensure it's not growing too large, which could impact performance.

Remember, AWS DMS is designed to work with the primary instance to ensure it captures all changes accurately and in real-time. While it may seem appealing to use a read replica, doing so could lead to inconsistencies and missed updates in your replication process.
Sources
Capturing data changes for ongoing replication from SQL Server - AWS Database Migration Service
Using a Microsoft SQL Server database as a source for AWS DMS - AWS Database Migration Service
Creating tasks for ongoing replication using AWS DMS - AWS Database Migration Service