MSSQL and MySQL database two way synchronization (chrone job)

One Star

MSSQL and MySQL database two way synchronization (chrone job)

I need an implementation of a two way synchronization between a Microsoft SQL server database and one MySQL database. The synchronization has to be bidirectional and whenever any table updates the mapped table is updated to. The sync must also run every 10 minutes or less to keep the databases updated. 
I would like to know if any of Talend's softwares (free or enterprise) can cover our needs. Can any1 help me because i am lost?
Employee

Re: MSSQL and MySQL database two way synchronization (chrone job)

Hi,
The Enterprise product provide Change Data Capture capabilities for Incremental load you can execute through our Talend scheduler with a 5 or 10 minutes frequency (more or less you choose it).
Then to really design and architecture something stable, reliable it might be useful to leverage our DataServices offering in addition to our ActiveMQ features so the Changes you consume from each of your sources gets publish into a Message Queur Broker and then propagated to your subscribed systems. It's important into a bi-directional Sync approach to TAG the record origin to make sure you don't create an Infinite for record which will travel from A to B and then B to A and then A to B for ever. You can use different TOPIC in ActiveMQ to manage that properly and then use the TAG to determine if the data has to go into the Queue/Topic or not.
Definitely something that our Data Services platform can deliver for you.
Best Regards;
Sixteen Stars

Re: MSSQL and MySQL database two way synchronization (chrone job)

The biggest question you should ask before looking into this is, "Is a bi-directional sync really necessary?". If so, for how long? As mentioned by cantoine, this is entirely possible with Talend. But it will be a long and laborious process setting this up, fraught with problems. 
For example,  what do you do if record A on system 1 is updated by someone at 10:00am and on system 2 record A is updated differently by someone else at 9:59am? I imagine the easy answer is to take the latest update. OK. But what if you need a complete audit history of every change? OK, well you update system 2 with the system 1 update and then amend the audit functionality to include the change on system 2 at 9:59 on the system 1 audit. Great. But what happens if this scenario happens on both machines on different records and in different orders (so system 1 then 2 for record A, system 2 then 1 for record B, etc, etc)? In which direction do you run your updates first? How do you incorporate deletes given a similar scenario? 
There are times when this sort of thing may be needed (parallel running before decommissioning an old system, etc), but this really isn't a simple task, no matter what tool you use. If this is intended for an ongoing solution and it is necessary, I would urge you to consider a more realtime (ESB) solution rather than batch. This will also be a lot of work to implement (depending on the size of the schemas), but it would reduce the possibility of some of the issues I mentioned above occurring.
One Star

Re: MSSQL and MySQL database two way synchronization (chrone job)

Thank you for the responses. From Cantoine's answer we solve the issue of automation, since the enterprise edition can update an entry on 'change data' event. The Dataservices part i dont really get. Is this a service that kepps track of the changes and then do them all together? And ActiveMQ is the way to now which comes from syncing? So your suggestion is enterprise data services and not data integration.
 Truth be told the problem rhall_2.0 describes could prove an issue. What would you suggest as a realtime (ESB) solution? A different implementation with Talend or did you have something else in mind? 
At this point i will describe our project a litle more. We need to sync one ecommerce MySQL database (Prestashop) with one MS SQL Database (our ERP's database). We need to sync orders, product, stock levels and users between them. I imagine that a batch solution would work since our orders will go one way (from prestashop to ERP), our products and stock levels will go another way (from ERP to prestashop) and our only issue would probably be clients. 
Sixteen Stars

Re: MSSQL and MySQL database two way synchronization (chrone job)

This knowledge simplifies it a little. If you can isolate tables that you know will only be updated by one of the two systems, then the original suggestion by Cantoine should work quite well. You can set up the CDC capability on each database to accommodate the tables that will be updated and assume a one direction sync is required for that data. 
You say your only issue would be your clients. I assume that means that they are stored and processed on both systems at the same time. If that is the case, then this is where I would be tempted to use an ESB solution for this. This does not sound highly transactional and I am assuming that you would want the changes to be reflected in both systems in as close to real time as possible. If that is the case you could use Camel Routes in conjunction with a CDC solution to ensure that there is as little time between replicating the changes between systems as possible. There are also other ways, this is just off the top of my head.
One thing to aware of CDC solutions is that they implement a fair number of triggers into your databases. If you do not want to do this, there are still ways, but they will take a lot more thinking about.