Migration from MySQL to SQL Server 2005 Express Edition

15. March 2008, 6:03 UhrSQL Server, database, mysqltonex

I was searching the Net (googling) for tips on how to migrate from MySQL to SQL Server 2005 Express Edition. I found this free trial software named MySQL MS SQL Server Import, Export & Convert Software 7.0. First of all, the name is so long. That should’ve given me a hint that this software sucks.

I tried it out and abandoned it abruptly for three reasons:

  1. A splash screen opens up containing various links. This screen doesn’t go away and it floats on top of your other windows. Good thing you can drag it away.
  2. The software launches a web page asking you to get the paid version.
  3. It doesn’t copy all your MySQL table’s data. I guess the trial meant see if it works but not if it works completely.

In the age of Web 2.0 and the Google phenomenon, I’m sure there’s a cheap (read: free) way to do this migration. Enter Lucian. Here’s how to do it:

  1. Download and install MySQL ODBC Driver. (Luckily, the free trial software I installed automatically installed this driver.)
  2. Use this query on your SQL Server 2005 Management Studio Express (change values appropriately): 
    EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL',
@provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver};
SERVER=127.0.0.1; DATABASE=Db_NAME; USER=myusername; PASSWORD=mypass;
OPTION=3'
  3. Then, use this query to copy table by table (): 
    select * into MSSQL_DB_Name.dbo.TableName
from openquery(MySQL, 'select * from mysqltablename')

It’s cool to be cheap.


Related Articles
Bookmark and Share

3 comments:

  1. Lucian, 19. March 2008, 20:35

    I was surprised to see that there is not too much material on the web when I had to do this transition from mysql to sql server express. I’m glad I could be of help.

     
  2. Machinista, 20. March 2008, 3:19

    Maybe it’s because anybody who’s willing to use MySQL and MS SQL (not a hard core fan of, but is willing to use either, depending on the project) knows that you can download a pirated version of SQL Server 2005 Enterprise Edition. This edition, of course, does have automated MySQL data and structure import.

    Anyways, thanks Lucian.

     
  3. protected static, 29. March 2008, 4:53

    Very useful, thanks…

    Here’s a thought - instead of doing it all by hand, you could link to INFORMATION_SCHEMA, too.
    Declare an nvarchar @table_name to hold table name, instantiate a cursor on this:

    select [table_name] from tables where table_schema = “Db_NAME”

    Use the cursor to loop through all the table names, setting @table_name with each fetch and generating your ’select * into’ statements which you can then execute with sp_executesql.

    If you want to be really snazzy about it, you could generate ALTER TABLE commands for each table name - select * from columns where table_name = @table_name will get you a lot of information you can use, as would select * from constraints where table_name = @table_name

    Of course, you’d need cursors within cursors to iterate through these additional resultsets, but without a terrible amount of work you could replicate a lot more of the MySQL database’s objects in your MS SQL database…

     

Write a comment: