CODEWITHSUNDEEP
mysqlsql-serverdatabasemigration
dawn lewis
dawn lewis

Reputation: 61

MySQL to SQL Server migration

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

I know end to end where the data should go, table to table but I have no idea how to go about moving the data. I've looked around the web but it seems there are 'solutions' which you have to download and run. I'd rather if possible do something myself in terms of writing scripts or code.

Can anyone recommend the best way to do this please?

Upvotes: 6

Views: 5317

Answers (5)

Jeroen Coupé
Jeroen Coupé

Reputation: 1404

I did it once, some time ago. First you could couple your mssql server to the mysql server using the odbc mysql connector

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

After the connection is made you can write you database procedure as you would if it were two mssql db's. Probably easiest to write some sql batch scripts including a cursor where you run through every every row of a table an decide on a field basis where you will need the field in the future.

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

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

Upvotes: 0

udog
udog

Reputation: 1536

You can use the Import/Export Wizard that comes with SQL Server Standard Edition.

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

A bit more automated means would be by using the SQL Server Migration Assistant for MySQL, also free. It has the benefit of recreating the relationships and indexes automatically for you. Probably your best bet.

Upvotes: 0

Gallop
Gallop

Reputation: 1405

Try this tutorial it is very easy to perform migration to SQL Server from Mysql and is straightforward as mentioned

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

Thanks

Upvotes: 0

JackLock
JackLock

Reputation: 1168

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

You have several options here:

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

Regardless of which you choose, non-data artifacts like indexes, foreign keys, triggers, stored procedures, and security will have to be moved manually.

Upvotes: 7

Related Questions