AWS DMS two way data replication

Question

I created RDS for 1 way data replication

"(Source(Sql_server) to Destination(MySql server)" ,

How this will work two way data replication

Explanation :-

Step 1: I created AWS DMS(Relational Database Service) into aws account

Step 2: Login RDS into SQl server 2017.

Step 3: then i followed this link

https://aws.amazon.com/blogs/database/introducing-ongoing-replication-from-amazon-rds-for-sql-server-using-aws-database-migration-service/

Step 4:after this link , one way Data replication is working fine.(Means now when i update into sql server table its is reflecting into mysql database table)

i Need a two way replication

Any Idea or suggestion Thanks

Amandeep

Answer 1

This is supported as of 2024 (possibly earlier): https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.CDC.html#CHAP_Task.CDC.Bidirectional

You can use AWS DMS tasks to perform bidirectional replication between two systems. In bidirectional replication, you replicate data from the same table (or set of tables) between two systems in both directions.

For example, you can copy an EMPLOYEE table from database A to database B and replicate changes to the table from database A to database B. You can also replicate changes to the EMPLOYEE table from database B back to A. Thus, you're performing bidirectional replication. Note

AWS DMS bidirectional replication isn't intended as a full multi-master solution including a primary node, conflict resolution, and so on.

Use bidirectional replication for situations where data on different nodes is operationally segregated. In other words, suppose that you have a data element changed by an application operating on node A, and that node A performs bidirectional replication with node B. That data element on node A is never changed by any application operating on node B.

Answer 2

The Amazon Database Migration Service does at this time support bidirectional replication with SQL Server. I believe that this is a recent feature addition. Amazon documentation:

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.CDC.html#CHAP_Task.CDC.Bidirectional

This is not transactional bidirectional replication. That is, let's say App 1 modifies the salary of "John Smith" and sets it to $150,000 on SQLNode1 and App 2 modifies the salary of "John Smith" and sets it to $100,000 on SQLNode2. There is no way to catch this conflict in the replication task. These tasks are not atomic with commit across both databases. So the application designer will need to build in data checking and data consistency logic for these situations.

Answer 3

According to Amazon, it's not a recommended practice with DMS:

Bi-directional replication is not recommended with DMS. A typical replication scenario has a single source and a target. When the source and target endpoints are distinct, DMS guarantees transactional integrity. In bi-directional replication these source and targets can be reversed and lead to unintended consequences if the same row is updated by two different replication tasks. Two-way replication works best when the tables being updated from the source to the target are logically independent from the tables being updated from the target to the source.

(from https://aws.amazon.com/dms/faqs/)

Of course, you could try and set up two DMS jobs pointing in different directions. (You DID say you were looking for ideas; I don't think this is a particularly good one.) You might also consider setting up a SQL Server RDS instance (instead of MySQL) as you might be able to use some of SQL Server's built-in bi-directional replication. The nice part here is that it takes care of issues you might run into going between disparate database systems (such as loops). Microsoft has this to say about it:

Bidirectional transactional replication is a specific transactional replication topology that allows two servers to exchange changes with each other: each server publishes data and then subscribes to a publication with the same data from the other server. The @loopback_detection parameter of sp_addsubscription (Transact-SQL) is set to TRUE to ensure that changes are only sent to the Subscriber and do not result in the change being sent back to the Publisher.

(from https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/bidirectional-transactional-replication?view=sql-server-2017)

Good luck!