One-way sync between MySQL and SQL Server

Question

I have a table A on MySQL and a table B on SQL Server.

Now, I need to keep B in sync with A (one-way), but B should "always" remain accessible for querying (now, one second of downtime won't hurt anyone...). The sync should occur once every hour.

Some scenarios come to mind:

Check A for updated/inserted/deleted rows, then update these in B (might get complicated);
Truncate B, then fill it again with data from A (might take a while and lock B);
Fill C with data from A, then drop B and rename C to B (seems dirty);
...

What to do?

For reference: The first solution I tried, was making B a view, using OPENQUERY to show A's data, but performance wasn't great.

There are probably some commercial products that can do exactly this, but I would just like to do this in a SQL Server job.

I would question the requirements... do you absolutely have to sync between 2 different DBs? Can the application that is using B be modified to consume A? If the table and data that needs to be synced is large, trying to compare the 2 tables and merge differences will always be a slow operation. My best advice would be to try to change the app that is consuming the data or try to change the app that is writing the data. Perhaps you can make the app that is writing data insert to both tables at once. — PressingOnAlways
– PressingOnAlways, Commented Oct 30, 2014 at 16:34
All valid points, but for technical reasons (and some politics) I need both. For example, it's not a single app that is consuming B, but multiple apps, sites, reports, ... — Stijn
– Stijn, Commented Oct 30, 2014 at 16:51

dpw · Accepted Answer · 2014-10-30 17:19:48Z

1

Rather than check A for changes at sync time, I would create a trigger on A that populated another table A1 with just the changes. Then your sync job only reads the changes from A1, and only has to update B with those changes. When your sync job is done reading a row from A1, it deletes that row.

Thus A1 is a queue for pending changes from A to B, and should (hopefully) scale gracefully if you decide you need to sync faster than once an hour.

answered Oct 30, 2014 at 17:19

dpw

1,5869 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stijn Over a year ago

As this would certainly be faster than checking the whole table for changes, this "update changes" solution can get quite complex in comparison to, for example, just dropping and recreating B. So it's not high on my list of favorites. Also, I have no direct control over the MySQL database. Adding a trigger to a table would require me to submit a change request to another team, which could take a while...

PressingOnAlways Over a year ago

This would only work if Table A is receiving INSERTs. Any DELETE or UPDATEs will not properly be synced. You're going to have to record the transactions to replay if you want to duplicate it exactly into Table B.

dpw Over a year ago

If you're not the owner for the mysql table, I'd be inclined to say that it's the other team's responsibility to get you the changes to replay. I realize it's probably not going to work like that as you've already stated you're stuck with this task for political reasons.

dpw Over a year ago

@PressingOnAlways:, no, you just need to structure your triggers and delta table correctly. I use a trigger and a EAV table to do something similar in my database.

Collectives™ on Stack Overflow

One-way sync between MySQL and SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related