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

Posted on 2006-04-06
Last Modified: 2006-11-18
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...
Question by:14100
    LVL 14

    Accepted 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.


    Author Comment

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now