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?
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:
Hope the tips help!
The same question was addressed on StackOverflow here: Replication from MySQL to MSSQL.
It seems that there are some workarounds, but not a very easy solution.
I believe that you should definitely try building an SSIS package to import necessary data from MySQL DB to MSSQL DB. SSIS allows one to import data from various sources. Then you should be able to schedule the package using windows task scheduler or sql jobs.
If by replication you mean log shipping or something like that I believe you are out of luck. However you can certainly set up a MySQL database as a linked server and roll your own replication scheme. Simplest is just to do periodic snapshots of all the tables using truncate and insert statements. Add complexity as your requirements dictate.
Alternatively, for a "snapshot" replication, you can use a Python (or similar) script to simply connect to both data sources (using UnixODBC and FreeTDS to connect to MS SQL), loop through the tables doing
SELECT on one side, for each row
INSERT on the other. As MSSQL has transactions and is your target, you can start a transaction,
DELETE everything from all tables, do the copy, then
COMMIT and it will appear instantaneously as far as connected users on the target are concerned, there will be no inconsistencies (unless these exist on the source of course).