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