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?

Improve this question
1
  • 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 en.wikipedia.org/wiki/X/Open_XA. MongoDB doesn't support it. One solution would be to add XA-support to it but: developer.mongodb.com/community/forums/t/… so today, there's nothing builtin. Saga, like 2PC, is easy to think, difficult to build.
    – Simon Mourier
    Commented Jul 5, 2021 at 14:27

1 Answer 1

Reset to default
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.

Improve this answer
1
  • sorry I forgot to mention that we are still using MongoDB 3.4 and that does not support transactions.
    – ravi kumar
    Commented Jul 5, 2021 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.