MySQL to SQL Server migration

Question

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?

Answer 1

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.