synchronize nosql and relational databases

Question

synchronize nosql and relational databases

Asked 3 years, 9 months ago

Viewed 66 times

0

I have an Asp.net application that uses the CQRS architecture standard and two databases one relational with Entity framework and one with mongodb. The question is at the end of the day, or in parts of the day, I must have an application to check whether the data in the two databases are equal, how best to do this by creating a log table in the two databases identifying which table, which record and date of update and creation were created the items or has another way to perform better this synchronization?

"creating a log table in both banks" this would be an alternative, now without knowing the banks, tables, infra and more other factors, any response will be based on opinions and will not help much

– Ricardo Pontual

2021/02/03 at 11:16
Well Ricardo had not seen his questioning before, the CQRS architecture standard is in it are already synchronized the two banks when there is data change, so there is already a synchronization, but as they are distinct servers there may be a failure to insert in mongodb, if there is a fault I was thinking of sending to a message service, when reading the queue it would update the mongodb and the Entity framework allows you to create a log in this way knowing which tables have been changed https://stackoverflow.com/questions/25245351/entity-framework-6-Audit-log

– Anderson Tavares

2021/02/04 at 23:36

1 answer

Browser other questions tagged sql-server asp.net mongodb

You are not signed in. Login or sign up in order to post.

by user3646143 • 1 point · Answer 1 · 2021-02-03T13:36:52+00:00

Anderson, if you are doing this insertion of data into SQL Server for Mongodb in a single transaction, you already ensure that the data is equal, ie only enter into Mongodb if the data has been successfully entered into the relational database and then do the commit transaction. You can do this by using a Transactionscope.

If you are doing this operation asynchronously, that is, you insert it into the SQL database, play in a queue for example and then consume it from that queue to play in Mongodb. You can use a standard to work with messaging called Outbox, adapted to your problem. This way you will have a table of everything that came out to your synchronization queue, you can record that the data has not been consumed successfully, the process that will take the data and enter in Mongodb is who will signal that the data consumption has been done.

So you have a table that marks the output of the data from your SQL base to Mongodb, as if it were an "output box". One benefit of this approach is that you can see what hasn’t been synchronized yet and you can have an application just looking at this sync table, which will tell you what has been synchronized or not, giving you the benefit of making a new attempt to synchronize the data, if you make a structure for it.