4

I have created custom script in Express that actually migrates SQL Server database to MongoDB.

But I am facing problems in live syncing between the two databases.

Currently I have added a column updated_by in both the databases.

Then I fetch the latest updated_by row from MongoDb and SQL Server database.

Then I check the date difference and based on it I update my MongoDB database.

There are lots of db tables and I am finding it difficult to identify that, which table is being updated.

Is there any log in SQL Server 2008 R2 that states which table is updated and at what time?

I need a mechanism like, any data update in the db table should immediately sync that rows into my MongoDB.

Any more suggestions on live data syncing is also welcome.

Thanks in advance. :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Viren Ajmera
  • 101
  • 2
  • 6
  • I have the same problem with live sync of mssql to mongodb ,please help with above issue. – Prasad Bhosale Mar 18 '15 at 09:13
  • 2
    Check out the [Change Data Capture feature](https://msdn.microsoft.com/en-us/library/cc645937.aspx) in SQL Server which should offer that kind of information – marc_s Mar 18 '15 at 09:27
  • Another option would be to use Apache Camel for iterating over The SQL data, filter out the datasets already present and write the new ones to MongoDB. Note that there will be quite a delay. As for real time sync, I don't see an option other than writing it to both databases. Without knowing more about your use case, a solution is hard to find, – Markus W Mahlberg Mar 18 '15 at 09:35
  • @marc_s - I gave a try for CDC feature.. 1. I change data capture enabled for my database using `sys.sp_cdc_enable_db` 2. Enabled table tracking using `sys.sp_cdc_enable_table` Then I tried with some more insert command on my source table, but I was not able to find any entries under generated CT table. Please suggest more on this. I think I am missing something.. – Viren Ajmera Mar 18 '15 at 12:42
  • 1
    @marc_s - CDC worked for me.. thanks a lot.. 3 cheers for you :) Due to some reason, SQL Server Agent was OFF.. I just turned it ON and it worked for me.. – Viren Ajmera Mar 19 '15 at 13:10

1 Answers1

0

When i have such requirement to Sync between Relational DB say (MYSQL) and Non-Relational DB (Mongodb).

I had followed following steps which may help others in future. and the concept is generally called as Change Data Capture

  1. Capture changes (For MYSQL iam using triggers.)
  2. Transform changes to a suitable changes ie RDBMS to Non RDBMS
  3. Update changes
  4. Remember to sync the structural changes of database and corresponding implementaions.

Following links may help

https://www.flydata.com/blog/what-change-data-capture-cdc-is-and-why-its-important/

SAMUEL
  • 8,098
  • 3
  • 42
  • 42