How to Replicate Data Between MySQL and SQL Server with CData

How Can I Replicate and Create a Backup of MySQL Data into SQL Server?

Our best solution that works very well for this particular scenario is DataSync:

1. Via CData Sync

   CData Sync is a web application that you can use to easily set up scheduled replications between several sources (including MySQL and SQL Server) and destinations (including MySQL and SQL Server).
   For an overview of Sync as well as a list of supported data sources and destinations, refer to the link below:

   CData Data Sync

   To get started, you'll first need to install Sync: https://www.cdata.com/sync/download/.

   You will need to set up a source and a destination and then you can configure a job to replicate the data automatically from the source into the destination.

2. Via the ODBC Drivers
   • With the CData ODBC Driver for SQL Server you will be able to read / write SQL data. You can also replicate SQL Server data into MySQL. A typical cache provider string should look like: Cache Provider=System.Data.CData.MySQL;Cache Connection='Server={localhost};Port={3306};Database={mySqlDB};User={test};Password={test}';
   • With the CData ODBC Driver for MySQL, you will be able to read / write MySQL data. You can also replicate MySQL data into SQL Server. A typical cache provider string should look like: Cache Provider=System.Data.SqlClient;Cache Connection="Server={localhost};Database={SqlDB};Integrated Security=true;";

   Note: If you don't specify Integrated security you can set user/password credentials.

   The CacheProvider and CacheConnection connection properties will need to be set under the Other property field.
   Once you've set up a successful connection, you can run a cache statement like so: CACHE SELECT * FROM Accounts

   This will select the data from the source ODBC connector and cache it into the cached provider. More information about the cache settings is available here.

3. SQL Gateway

   You may use the MySQL or TSD remoting services of the CData SQL Gateway. With the Gateway, you can expose any ODBC source (SQL Server or MySQL) as if it was a SQL Server or MySQL Database. Here is an article where this is explained via a Linked Server.

4. SSIS Components

   You could also make use of the SSIS components if you're familiar with SSIS workflows. MySQL SSIS Component: https://www.cdata.com/drivers/mysql/ssis/ This would allow you to replicate MySQL data into SQL Server (via the MySQL Source component) and you can also write data back to MySQL from SQL Server (with the MySQL Destination component.)

   Both of these solutions do require some setup and configuration (like column mapping.)

Please note that, with the exception of CData Sync, all our trials are fully functional for a period of 30 days. For a full-featured CData Sync license, please contact our support team.