Replication from MySQL to Ms SQL

Replicate Microsoft SQL to other databases

It's possible with SymmetricDS, an open source solution that can replicate changes between different databases, like SQL-Server and MySQL. Some features are:

• Uses change data capture and continuous synchronization
• Low-bandwidth transfer over web-based protocols
• Asynchronous operation in the background
• Recovers automatically from network downtime

It has an initial load feature to get your databases in sync, then it continuously sends changes as they are captured. There are a lot of configuration options, so you can set how often it syncs.

Replication from MySQL to MS SQL

Triggers in MySQL could be used to catch changes and call a UDF, which could then execute ODBC queries to MSSQL. Likely terrible for performance, though.

If immediate replication isn't required:

• Write triggers in MySQL that capture insert, update, and delete statements in a log table.
• Poll the log table from MSSQL using ODBC and execute them, then delete those log entries.

Of course, T-SQL and MySQL's variant of SQL isn't exactly the same, but it should be close for trivial CUD operations.

MySQL to SQL Server, How to replicate data in a set interval?

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.

Replication between SQL Server and MYSQL

The documentation is very clear that only Oracle and DB2 are supported in any way, and only when using OLE DB, not ODBC. Therefore, trying to set up replication to MySQL using ODBC is almost certainly a waste of time.

Having said that, native SQL Server replication is not the only way to copy data between databases, and if you can give some more information about your requirements then someone may have an alternative solution.

For example, if your goal is some form of reporting or data warehousing then you can look into the many ETL tools that are available and work with many different databases. (I'm assuming your goal is not redundancy or availability, because you're using two different platforms.)

---

Related Topics