Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 4 down vote accepted

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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!

share|improve this answer
add comment

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.

share|improve this answer
    
I mean I want a scheduled task to run and copy all the contents from the msqldb to the sql server 2008 db once a week or so - is that possible? –  Jimmy C Jan 18 '11 at 15:52
1  
Remeber that truncating and pulling all the data over each time could be very slow depending on how much data is involved, network latency, etc. It's definitely not the way to go if you have small or slow network links between the two databases. I always try to devise a method that will allow me to pull deltas. –  AndrewSQL Jan 19 '11 at 1:25
1  
I agree, deltas are the way to go. I don't know enough MySQL to speak to triggers and dirty row bits but I am sure that something could be set up. On the SQL Server side, scheduling a job is straight forward. –  Larry Smithmier Jan 19 '11 at 3:08
add comment

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.

share|improve this answer
add comment

You can use GoldenGate for MySQL and MS SQL to do this. You would simply install the GoldenGate product on each side, then proceed as for a homogeneous replication.

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).

share|improve this answer
    
I wasn't aware this was something that you could do with GoldenDate. I'll have to read more about this; thanks for sharing! –  AndrewSQL Jan 19 '11 at 1:26
    
Now oracle provides support for this product. Oracle has acquired GoldenGate. Now this product is under oracle products. –  user4056 Oct 24 '11 at 9:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.