How to securily Synchronize selected data from specific tables in a local SQL database with a distant MySQL database

Question

I am a Linux guy who is lost between the "windows"!

I have an ERP that saves data to a local Windows SQL Server. For some reason we need to periodically synchronize some selected data from specific tables, with a MySQL instance hosted on a distant Windows Server. knowing that the distant MySQL is operated by a Zend2 Web Application.

According to my knowledge and understanding, the native option I suggest is the following:

Create an API using PHP on the distant server, and submit my data using PUT or POST through a simple script on the local machine, in this case, I need the following:

• Build an API on the distant server
• Configure an extra PHP server on the local Windows Machine.
• Build a small script to call the API providing the selected data.
• handle all the cases of the synchronization process on the distant server.
• Find a Periodic trigger on the local Windows (Something like Linux Cronjob)

This solution seems complicated, and the many layers would result in a big probability of problems, the reason why I am sure there are other ways to do it.

A Windows guy recommended BizTalk as a middle-ware that would handle the local part job, either using a normal API or any other low level interactivity with the distant server.

He also recommended using Windows Tasks as a periodic trigger.

Do you think my solution is the optimal one? if not what do you suggest?!

Answer 1

I would look at a third party tool like DBSync (Not an endorsement) I have used a few different 1 way and 2 way sync tools in the past that create a (master-slave) replication service between different databases. I'm sure there are some open source solutions as well, but a licensed tool might work best with MSSQL to MySQL.

http://download.cnet.com/DBSync-for-MSSQL-and-MySQL/3000-10254_4-10784422.html