The method described in this topic comprises an alternative to synchronizing data between different DBMSs using a linked server and custom queries. As custom queries cannot be synchronized, you can not use a database connected via linked server as a target. Below, we offer a practical workaround to help you overcome this limitation.
Note
You can learn how to install and configure ODBC Driver for MySQL here.
First, on SQL Server, create an intermediate database. Then, in this database, create views that will contain custom queries against the MySQL database.
CREATE VIEW view-name AS
SELECT
*
FROM [linkedserver_name].[database_name]..[table_name];
In Data Compare for SQL Server, click New Data Comparison. In the New Data Comparison wizard that will open, as a source, select a database you want to synchronize with a MySQL database. As a target, select the recently created database containing views based on custom queries against that MySQL database. Click Next.
On the Options page of the wizard, select the Compare views option. Click Next.
On the Mapping page, map source columns to the columns in the target’s views. Click Compare.
Data Compare will display the comparison results. Select the objects you want to synchronize and click Synchronize.
Data Compare for SQL Server allows you to open the synchronization script in the internal editor for further modifications, save it to a file, or execute it directly against the target database.
If you refresh the comparison results after synchronization, you’ll find that the data in the source and target databases is now identical.
Note
If a MySQL database contains foreign keys, data synchronization may fail. In such cases, you may need to manually disable and then re-enable foreign keys.