Greetings,
We have been using only MS-CDC for ongoing replication and had the ECA IgnoreMSReplication turned on.
In order to determine the feasibility of using MS-REPLICATION as an alternative some tests were ran.
We followed the steps here -->
https://docs.aws.amazon.com/dms/latest/sbs/chap-sqlserver2aurora.steps.configuresqlserver.html
1. In Microsoft SQL Server Management Studio, open the context (right-click) menu for the Replication folder, and then choose Configure Distribution.
2. In the Distributor step, choose db_name will act as its own distributor. SQL Server creates a distribution database and log.
For more information, see Microsoft documentation.
When the configuration is complete, your server is enabled for replication. Either a distribution database is in place, or you have configured your server to use a remote distribution database.
CDC was disabled because all tables in the DMS endpoint Table Mappings have Primary Keys Defined.
The source database, configured using the steps above, is an on-prem SQL Server and the target is an RDS SQL Server. The task is set as full-load and ongoing.
When the task started, the load completed and changes at the source where captured.
However, there was no subscription(s) created and at the source, and neither the log reader nor snapshot agents was running at the source.
At the source server, all indications, just showed distribution was configured and the source databases was enabled for transactional replication (as per the AWS documentation).
We did see changes come across. We also saw the error below captured against the table the changes were made against at the source:
With CDC disabled and following the AWS documents for using ms-replication, we see no indication that this is working. Our guess is the user that replctrl is running under is polling for changes or reading the active log, but with no definitive way of knowing, we are unwilling to move forward.
Questions
- Do we need to manually create a Subscription and mark each article in the DMS table schema for this to work?
- Does the DMS user still capture replication changes even though nothing shows that replication is running at the source?
- Why did we get the error in the image above, implying that table has a primary key and the **log stated that MS-REPLICATION was well configured when the task was doing before-run checks (se image below) **?
- Are we completely missing something altogether?
One more confusing series of log entries (how does a table become enabled for replication; does it require a subscription??)
Inserts and deletes came over but not updates, however, table has PK.
Looking for guidance.
-Thanks, and have a good day.