Home / server / Questions / 24625
Accepted
Dan
Asked: 2009-06-13 01:59:09 +0800 CST

Transferring data between SQL Server and MySQL

  • 772

We host a couple of SQL Servers where we are planning to upgrade the environment (still classic asp). We are looking whether to move to a LAMP (or WAMP) environment instead of just following the default route to .net.

If we had a MySQL server and SQL Servers (during a transition period) - what options exist for transferring data between the two?

I don't mean doing a one-off migration - if for example data were still being captured on the SQL Server and we needed to move this data onto the MySQL daily (or ideally more frequently) for some reports process, what options are open to us? Are we limited to scripts to export to csv/txt?

And if we needed to move data the other way, what's available?

sql-server mysql

4 Answers

  1. I would investigate ETL tools. SSIS unfortunately has no MySQL driver, I have heard that you can use the OLE DB .NET driver and somehow get it working but I've not had any success with it.

    Checkout Pentaho Kettle which I believe has MySQL support out of the box and then you can get SQL Server support by using the MS JDBC driver.

    Commercial tools like Pervasive Data Integrator may also be worth a look if you have budget.

    Sorry I don't seem to be allowed to post hyperlinks!

    • 2
  2. Best Answer

    See http://www.mysql.com/products/connector/

    You could use some VBScript on the SQL Server and write data to MySQL using an ODBC or ADO.Net link. You'd have to manage the "replication" manually though i.e. your script would have to figure out what to create/update/delete in the MySQL database.

    I believe there are SQL Server drivers for unix, so you could run a script on the unix box. However I'm outside my comfort zone here.

    JR

    • 1

  3. Do you think that replication would be an option? Configure your MySQL server as a slave and replicate from the SQL Server (as master).

    I'm bringing this up as an option because you say that you need more than a one-off migration, and replication may be well-suited to that goal, since doing manual exports at intervals has two drawbacks: it's clumsy, but more importantly, in order to preserve state between the two machines, you need two exports (one form each machine) and then have to deal with potential conflicts.

    You'll have to assess its usefulness in your particular application, though, it may not be what you need. Do comment here and let us know what you did!

    • 1

  4. SQL Server Migration Assistant (SSMA) for MySQL lets you quickly convert MySQL database schemas to SQL Server or SQL Azure schemas, upload the resulting schemas into SQL Server or SQL Azure, and migrate data from MySQL to SQL Server or SQL Azure.

    You can use this small app and guide on msdn. Get started here : http://msdn.microsoft.com/en-us/library/hh313129%28v=sql.110%29.aspx

    • 1