Confusion on Setting up MS-REPLICATION for Ongoing DMS Task Replication

0

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:

Enter image description here

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

  1. Do we need to manually create a Subscription and mark each article in the DMS table schema for this to work?
  2. Does the DMS user still capture replication changes even though nothing shows that replication is running at the source?
  3. 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) **?
  4. Are we completely missing something altogether?

Enter image description here

One more confusing series of log entries (how does a table become enabled for replication; does it require a subscription??) Enter image description here

Inserts and deletes came over but not updates, however, table has PK.

Enter image description here

Looking for guidance. -Thanks, and have a good day.

1 Answer
2
Accepted Answer

Initial testing indicated that the ECA flag IgnoreMSReplication was meant to treat the condition for when using MS-CDC for replication and MS-REPLICATON was disabled for the source database as a warning instead of an error. It appears this flag has two meanings.

  1. Treat instances where MS-REPLICATION is disabled as a warning instead of an error.
  2. Bypass MS-REPLICATION altogether when enabled.

We did not realize step 2 above until we were able to redeploy the tasks

profile picture
answered a month ago
profile picture
EXPERT
reviewed 16 days ago
profile picture
EXPERT
A_J
reviewed a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions