• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 849
  • Last Modified:

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...
1 Solution

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.

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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now