Avatar of edukate
edukate
 asked on

best way to migrate a MySQL DB to MSSQL 2005

Hi there,
I'm migrating an application from an older Mac Server onto a new Win 2008 server to be shared with sharepoint. Hence we need to migrate the DB.

I've created a test VM with same SQL 2005 config and installed MySQL with source DB.

Have been trying for a couple of days now to use SSIS to import the data but are having many problems with this.

I've just done a mysql dump using phpmyadmin and noticed / set compatibility option to MSSQL and saved it to a .sql file. I'm assuming this option is only to help minimize problems with the datatype differences such as varchar length etc..

What would be a good way to import this?
DB is about 600MB and contains ~240 tables
PHPMySQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
edukate

8/22/2022 - Mon
Ian Meredith

Hi Edukate,

Here is a straight forward guide from a reliable source....  non SSIS method...
http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

SQL Server White Papers: Guides to Migration from MySQL, Oracle, Sybase, or Microsoft Access to Microsoft SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?FamilyID=3f0f2a3f-dff9-49cd-8ea7-581aa7a303a4&displaylang=en

hope it helps...
edukate

ASKER
thanks I tried the codeproject guide before I got into the SSIS method but ran into an error 'Cannot get the column information from OLE DB provider'.
I was trying to use the mysql ODBC driver so is there any way to do this?
edukate

ASKER
OK I've got the mysqldump.sql now too whether this could make things any easier??
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Faiga Diegel

I had this issue last June 2008. I was trying to test migrate our MySQL to MS SQL 2005 wasn't successful. When it was geting complicated, I then opened a case to Microsoft showing how I am doing it, until we later found out that we cannot migrate directly to MS SQL 2005. What we did was migrate to MS SQL 2000 then upgrade to SQL 2005.

They might have updated or probably had included the fix on the service packs nowdays.

This is what we did before (see if it might help though):

1. Downloaded MySQL Connector/ODBC 5.1.
2. Installed on a box having SQL 2000 instance.
3. Created a user DSN by using Mysql ODBC driver.
4. Started Import and export wizard of SQL 2000.
5. Used other(ODBC data source) and selected the user DSN created.
6. Went to the next step and selected Microsoft OLE DB provider for SQL server.
7. Under server list selected 2005 instance and created a new database for the import.
8. Ran the import wizard.




edukate

ASKER
That sounds similar approach to the codeproject solution though I haven't tried the import wizard for this.. Not sure if it's capable as I couldn't find the MySQL provider in list for this.. Will test out further
edukate

ASKER
OK i tried just browsing the mysql db from the linked servers > mysql  > catalogs > dbname > tables > script table as.. select to.. and could see all the existing tables in mysql db... (235 of them) however I cannot select * from any of them.
I instead get this error
"[MYSQL].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object"  (note the .. between database and table names)
which looks like an old issue here:
 http://kbalertz.com/971261/table-reports-error-linked-servers-created-using-MSDASQL-provider-Server-Server.aspx

This blog also mentions how to get around it but as far as I can tell I've done exactly that but the error still persists:
http://legalalien.org/graveyard/index.php/2007/11/26/contains-no-columns-that-can-be-selected-or-the-current-user-does-not-have-permissions-on-that-object/
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
edukate

ASKER
Ok after spending most of yesterday and today working on this the only way I've found to do this is using SSIS with DataReader Source then creating then configuring a new OLE DB destination for each table with mappings.
This will take a very long time to do and I have to repeat it again on production system but I haven't found any other way that works :(
edukate

ASKER
Actually I've just found likely an easy way to do this:
In PHPMyAdmin you can export to different formats such as SQL.
I've opened up the first 1000 or so lines of the Insert Query generated (this is about one tables worth of metadata and data)and parsed it in SQL.
MSSQL returned half a dozen errors which I assume is just differences between how MySQL and MSSQL use SQL Syntax..

Does anybody know of any tools that can convert the syntax or maybe just the differences between them (likely the same errors repeating for each table)..

Below are the errors:
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 'adodb_logsql'.
Msg 102, Level 15, State 1, Line 49
Incorrect syntax near 'mdl_assignment'.
Msg 102, Level 15, State 1, Line 308
Incorrect syntax near 'mdl_assignment_submissions'.
Msg 105, Level 15, State 1, Line 866
Unclosed quotation mark after the character string '<table class="MsoNormalTable" style="border-right: medium none; border-top: medium none; margin: auto auto auto 5.4pt; border-left: medium none; border-bottom: medium none; ...

Open in new window

Faiga Diegel

This is not hte best approach especially when you have hundred thousand rows on your table. This would only be applicable to static data or small tables. What I did on that style is scan all the insert statements and see to it that the synbtax is SQL 2005 compatible.

Here is a documentation to migrating from mySQL to MS SQL 2005
http://www.microsoft.com/Sqlserver/2005/en/us/white-papers.aspx

Look for "Guide to Migrating from MySQL to SQL Server 2005". It talks about detiled convertion issues and how to handle them.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Faiga Diegel

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
edukate

ASKER
Thanks faiga16
Did you try all the approaches 1-5 and none of them worked?
I have spent much time trying 1, 3, and 4 - all without success

Number 2 - would you suggest dumping from MySQL as .csv or similar format, then importing? If so what would be lost such as relationships etc?

Number 5 - do you mean this wouldn't work if DB has more than 64 tables?

Number 6 - I have access to another server with SQL2000 - do you think it would be a good idea to try replicate the DTS tool solution then export the SQL 2000 DB for import into our SQL 2005 server?
Faiga Diegel

Yes, I tried all of them and ended up with approach #6.

#2 fortunately, we had only few relationship tables and I didn't encounter any issues with that. The only draw back here is you migrate csv file one at a time

#5 At that time, YES! I didn't look into that issue when trying to figure out what's wrong on this approach, it took me a while to research and found out that it only support 64 tables?!? I am hoping I'm doing something wrong here. But you can try it for yourself and see. They might have added fix to this (I did the project migration June 2008)

#6 I ended up using this approach. It has direct mapping of objects from MySQL to SQL 2005, you can select multiple tables to import at a time, and applicable to all data sets (static, log, transaction tables) .
With this approach SQL 2000 is just a middleman between MySQL and SQL 2005.

I don't understand why they don't have a direct migration from SQL 2005. But you can see it for yourself (as I've said I did the migration 2 years ago). Try using SQL 2005 Import/Export Tool, see if you can do a direct import from MySQL. I do not have MySQL test database right now so I cannot replicate this using SQL 2005.


edukate

ASKER
We have a SQL 2000 box that we'll try to clone to a VM to try out this migration soon.. Unless anybody else has other suggestions that has worked for them?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
edukate

ASKER
Thanks for the help / suggestions however we ended up sticking with MySQL for the time being.
If we get time we'll try the approach using MSSQL2000 in future
edukate

ASKER
Havent had chance to try this yet