Sync remote MySQL database (customized X-Cart) from local MS SQL, not a straight data transfer

I'm looking for suggestions regarding how I can transfer MS SQL data up to a remote Linux/MySQL server, keeping the two databases in sync on a regular basis.

While that seems fairly straightforward, what makes this more problematic is that the structure of the remote database is *not* the same as the local database. The remote DB consists of customized X-Cart tables, that will have a product & sub-product relationship (to mimic the functionality of the local database, but implemented in a different manner).

Our current solution uses a custom windows application that checks for changes on the local copy, exports to csv, uploads via ftp, and initiates a php script on the remote location which takes over, importing and parsing the csv data. This method doesn't seem streamlined enough for my tastes though. If there are huge changes across the products (which number close under 20,000), the php script becomes a noticeable resource hog on the webserver. Also, we don't like relying on the middle step (windows app + csv file), it would be nice if we could transfer the result set directly to the remote server for the sync process, and if we could negate the need for the php script (might be difficult to remove that step), that would be golden.

1. Sync 2 databases (one-way sync), 1 remote MySQL, 1 local MS SQL, with differing data structures
2. Make process as straightforward as possible, removing need for local windows export executable, and removing need for php script if possible. (Optional goal)

Anyone have any ideas? :-)    I have a fear that we're stuck with this method...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


You can have the person/party who wrote the current Windows application to modify his code so that, instead of output to csv file, make use of MySQL ODBC, connect directly to the MySQL server, and write the data directly into the server.

You can download MySQL ODBC connector from MySQL website.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
14100Author Commented:
That answer wasn't what we were looking for, but it appears no-one else has a solution.

Searching the web for products that can handle SQL to MySQL data sync, we've found this product for only $47, that appears to solve our dilemma:

No need for the custom-coded proprietary application, no need for php scripts, looks like the best solution we've found.

Thanks anyways. Since no-one else responded, I'll reward the points to you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.