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.

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