I have one large table in a MySQL table that when updated, I want to synchronize with a duplicate of the table on a Microsoft SQL server. Here is the process that happens when a new row is inserted into the MySQL table, and how it is sent to the MS SQL server table:
- When a new row is inserted into the MySQL table, there's a trigger that inserts the same row into a temp table on the MySQL server
- A job on the MS SQL server periodically checks the temp table, and from there takes any rows and inserts them into the MS SQL using a MERGE statement.
- Temp table is cleared out of any processed rows.
I have recently found out that very randomly some rows have just not been transferred over to the MS SQL server. What I am wondering is, is there a better way to do this, with reliability being the first priority, and speed being less of a priority, what other methods are out there? Thanks for any help, feel free to ask for clarification if I did not explain it well.