0

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:

  1. 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
  2. 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.
  3. 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.

1

I suspect you grab the list of rows to move, then an insert occurs, then you delete the rows. Instead...

Ping-pong a pair of tables. You are always inserting into one table, always copying from the other. Then do an atomic RENAME TABLE to switch.

See my high speed ingestion blog for something very similar. It explains the flip-flop technique.

0

SSIS is going to be your best bet in this case I believe. If you already license SQL Server, it should be included as well. An SSIS package has the ability to access and utilize multiple ODBC or OLEDB sources and destinations including MySQL. It can be run using a SQL Agent job or just using DTEXEC from a Windows scheduled job. Also you can easily build logging, error checking and validation.

0

I assume you have an auto increment primary key

Add the source server, the MySQL one, as a linked server in SQL Server. Then, add a job that runs every x minutes, and does the following:

  1. Get the last ID from the target table. call it maxID
  2. Read all rows of IDs higher than maxID from the source table
  3. insert them into the target table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.