Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.

Is this possible? Can anyone outline the steps required in order to achieve this?

Thanks.

Personally I'd pull into MS SQL method vs. the push from MySQL method.
Why? Well Windows has 32bit and 64bit MySQL ODBC drivers ready to go and setting up a linked server is trivial. I have plenty of MySQL servers linked to from MS SQL. Also, connecting to MS SQL from linux/unix is not always great and you're ususally not able to use all features. FreeTDS has limitations; you may hit them sooner rather than later so why not just skip it. This all assumes you're running MySQL on *nix. If not, it gets a little closer to 50/50 but I'd still choose pulling from MS SQL as it sounds like it's not the "live" database thus putting the load on it for any ETL or processing is more ideal.
The GoldenGate solution sounds interesting, but I'm sure it's not free.

Considering I've setup this sort of scenario with both MySQL and Oracle databases replicating to MS SQL I'll provide some tips that have worked best for me:

• If you can, do your best to determine how you can ensure you're only bringing across delta changes. Merge commands can help with this. Truncating a table and then inserting it all in again bloats your log, uses network bandwidth, and just generally wastes time.

• If dealing with lots of data be sure to break the transactions up so as not to require a massive log file. Use explicit commits or checkpoints when you've reached a step you know you won't need to roll back from.

• If the MSSQL db will be for reporting only, do ETL work there so as not to impact the MySQL server. Use a staging database or schema + filegroup to make things easy.

• Break the data importing into steps. This makes it easy to re-start the import where it failed and/or troubleshoot. An all or nothing approach becomes annoying quickly.

• Use variables wherever possible to assist the remote db with query plans & index use. Also pay attention to what sort of transaction isolation you're in on the host box and what impact the "replication" queries will have. You don't want to block writers on a live db if you're just pulling data over for reporting or sandbox use.

Hope the tips help!

StackExchange Database Administrators Q#688, answer score: 10