Simple migration from MySQL to SQL Server Express 2005?
Normally I'd go and write a bunch of code for doing it manually... But instead of that, maybe there is a smarter way?
What I know is that you can get some kind of dump out of MySQL but I would be very surprised if that could be directly read into SQL Express ?
So if you have a few step guide to migrate from MySQL to SQL Express, I am all ears (and eyes)!
Thanks!
(Yes I am aware there is some DTS thingy for this but could not find it in the Express folders)
There is no real direct ways to do this that I'm aware of, but I have a few ideas I'll share with the group.
- While SQL Integration Services is not available in SQL Express, it is available in other Editions, and you should be able to use SSIS to do this.
- Creating a linked server to MySQL and then running queries against it and into your MSSQL database will work.
- You could use MySQLdump to dump scipts of you database and then use those scripts to put your database in the MSSQL. (I found this to be the most labor intesive since MySQLdump scripts can't be run directly in MSSQL, you have to do a bunch of clean up of the script file before it can be run in MSSQL.
Being that this is the Express forum, I attempted #2 with success, and it went something like this:
First, create Linked Server to the MySQL server. I used the MySQL ODBC driver to do this. Fill in the blanks with values from your configuration.
EXEC
master.dbo.sp_addlinkedserver @server = N'MYSQL_Server', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER= ; DATABASE=; UID= ; PASSWORD=; OPTION=3'
GO Next, run a SELECT ... INTO query against the table I'm interested in getting from MySQL by leveraging the OpenQuery syntax:
select
* into jokes.dbo.Puns
from openquery(<linkedservername>,'select * from puns') This example would create a new Puns table in my existing database named Jokes. You could do this for all the tables you want to transfer.
Hope this helps with things.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
After some fighting, I got it to work.
The problem I had was that I didn't recognize the spaces between = ; being relevant.
Works:
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=****; USER=****; PASSWORD=****; OPTION=3' Doesn't work:
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=**** ; USER=**** ; PASSWORD=**** ; OPTION=3' When doing it through the Administrative tools/ODBC GUI, the OPTION=3 means selecting these settings:
"Don't optimize column width"
"Return matching rows"
Mike,
Above, you say:
"While SQL Integration Services is not available in SQL Express, it is available in other Editions, and you should be able to use SSIS to do this. "
What other editions is it available in, and where can I get them?
Thanks,
Pete
