0

I have 2 databases. One is a SQL Server and other is MongoDB. Both these DBs are hosted on AWS.

I need to make a .NET background service that runs periodically (maybe once a month) and update a huge amount of data in both SQL Server as well as MongoDB. And I need to do it in a single "distributed transaction" so that there is no data inconsistency between the 2 DBs and the data is in sync.
I have read about 2 Phase transactions but I think that won't be possible here because MongoDB does does not support transactions?(correct me if I am wrong, but we are using MongoDB 3.4 and I think that does not support transactions).
So I was planning to see if I can implement the saga pattern to implement the distributed transaction?
Is there any other pattern/techniques that I should look into to get this done?

ravi kumar
  • 1,548
  • 1
  • 13
  • 47
  • MongoDB has some transaction level, but for N "storage systems" (whatever that is) to support distributed transactions, they must share a common distributed transaction protocol. SQL Server supports "two phase commit" (2PC) through the DTC ("Distributed Transaction Manager") which also understands XA https://en.wikipedia.org/wiki/X/Open_XA. MongoDB doesn't support it. One solution would be to add XA-support to it but: https://developer.mongodb.com/community/forums/t/any-update-on-xa-support/3106 so today, there's nothing builtin. Saga, like 2PC, is easy to think, difficult to build. – Simon Mourier Jul 05 '21 at 14:27

1 Answers1

0

Saga pattern is naturally choice for distributed transactions.

MongoDB currently supporting transactions - https://docs.mongodb.com/manual/core/transactions/, so you can consider also your first idea, because it's could be easier.

pkantorowicz
  • 41
  • 5