Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
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 '09 at 14:14

4 Answers 4

up vote 1 down vote accepted

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

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

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!

share|improve this answer
    
Thanks Joel - hadn't heard the term ETL before (welcome to serverfault btw) –  Dan Jun 15 '09 at 10:47

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!

share|improve this answer

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

share|improve this answer

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.