Migrating from MySQL to SQL Server

Question

I have a mysql database full of data that I need to save but migrate to SQL Server 2008.

I know the end to the end where the data should go, table to table, but I have no idea how to navigate through the data. I browsed the web, but there seem to be “solutions” that you should download and run. I would rather, if possible, do something myself in terms of scripting or code.

Can anyone recommend a better way to do this?

Answer 1

You have several options:

• On the sql server side, you can configure the connection to your old mysql db using something called a linked server . This will allow you to write sql code for a SQL server that returns data from mysql tables. You can use this to create INSERT or SELECT INTO statements.
• You can write queries for mysql to export your data as csv, and then use the BULK INSERT functions for SQL Server to efficiently import csv data.
• You can use Sql Server integration services to set data movement from mysql.

Whatever you choose, artifacts other than data, such as indexes, foreign keys, triggers, stored procedures, and security, must be moved manually.

Answer 2

I did it once, a while ago. First you can connect your mssql server with mysql server using odq mysql connector

http://dev.mysql.com/downloads/connector/

After the connection is established, you can write the database procedure as if it were two mssql db. Probably the easiest way to write some SQL scripts, including a cursor where you run through each row of the table, and choose a field base where you will need a field in the future.

cursor example: http://www.mssqltips.com/tip.asp?tip=1599

If you decide to go with the cursor, you can play with the parameter to improve performance. I especially remember the FORWARD_ONLY parameter giving a big boost.

Answer 3

Have you tried a tool from MSFT called "SQL Server Migration Assistance for MySQL"? https://www.microsoft.com/download/en/details.aspx?id=1495

Answer 4

Try this tutorial, it is very easy to migrate to SQL Server from Mysql and is simple as indicated

http://www.codeproject.com/Articles/29106/Migrate-MySQL-to-Microsoft-SQL-Server

thanks

Answer 5

The Import / Export Wizard can be used with SQL Server Standard Edition.

Select a data source from MySQL using an ODBC data source. Note. You will need to first install the ODBC driver for MySQL ( ODBC Connector ). Then select your SQL Server destination. Select all tables and run it. You will need to add your primary and foreign keys and indexes manually.

A bit more automated tools will be available through the SQL Server Migration Assistant for MySQL , also free. This allows you to automatically update relationships and indexes for you. Probably your best bet.