3

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?

Improve this question
1
  • 1
    I spent months with our MSSQL and SSIS guys trying to get various "connectors" to allow MS to insert/update data on my MySQL servers. We got a lot of implementations functioning, but none were production-safe. I had one of my Python developers right a command line app that uses the pymssql and MySQLdb libraries. It is fast and reliable (cron-safe in production). We haven't FOSS'ed it yet because every now and then we find a datatype that needs mapping. We make all of our offerings under the MIT license. Are you interested? I can introduce it as a 0.x release.
    – Bruno Bronosky
    Jun 12, 2009 at 14:14

4 Answers 4

Reset to default
1

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

Improve this answer
1
  • In the end, we're not moving to a WAMP/LAMP environment, so we never actually put anything into production.
    – Dan
    Sep 2, 2009 at 10:31
2

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!

Improve this answer
1
  • Thanks Joel - hadn't heard the term ETL before (welcome to serverfault btw)
    – Dan
    Jun 15, 2009 at 10:47
1

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!

Improve this answer
1

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

Improve this answer
2
  • OP asked for MSSQL to MySQL and the link you have posted is for MySQL to MSSQL
    – bilal.haider
    Oct 24, 2016 at 0:28
  • @bilal.haider you are correct - I was mislead by the sentence "If we had a MySQL server and SQL Servers (during a transition period) - what options exist for transferring data between the two?". I found one solution but I did not want to post it because somebody may think that I am advertising a product.
    – Ognyan Dimitrov
    Oct 24, 2016 at 7:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .