0

What would be the best way to replicate a database with several tables from a MySQL server to a MSSQL server, seeing as they are in different devices on the same network with a set interval of 10 minutes?

By best way, I mean a way that wouldn't completely copy all the rows with each iteration, a way that would only copy the latest entries since the last iteration.

  • 3
    "Best way" based on what requirements? That's a "what's the best car" kind of question – Panagiotis Kanavos Mar 13 at 13:27
  • 2
    In any case, the term is replication, not migration – Panagiotis Kanavos Mar 13 at 13:27
  • Your definition of best doesn't really explain anything, it's the minimum requirement and depends on MySQL features, or lack of – Panagiotis Kanavos Mar 13 at 13:33
  • Thank you for the tips, will give the dba.se a try. – BryceSoker Mar 13 at 13:33
  • 1
0

As listed in the comments Linked Server is the best connection method, it integrates into your views and stored procedures.

The way I have done it in situations like this is create a view or a job on the source database that filters out the changed rows. I have gone as far as using temporary tables on the MySQL side, then from the MSSQL side read the temporary table you created. If you created joins in a query on the MSSQL side looking at the MySQL, the MySQL will just send all of the data over and MSSQL will have to process it, that's what you are trying to avoid. You just want the changed data.

Another method probably is the easiest is to create matching tables on the MSSQL side and use a product list SQL Data Examiner, then set the command line utility to run a project in the task scheduler. http://www.sqlaccessories.com/sql-data-examiner/ This will compare the actual data on both tables. If you are talking millions of rows or a slow connection this isn't the way. Just like doing it all in MSSQL you will have the issue where it pulls all of the data for the comparison.

So the best way depends on how many tables, how much data, and the connection capabilities. The easiest would be SQL Data Examiner.

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.