Converting MSSQL to MySQL

First of all, conversion of MS SQL to MySQL is a pain, a real crying pain. Ok that said, the steps involved are to first get your MS SQL database onto a machine where you can access it. For me, this meant exporting it off a host. The host exported it as a .bak file which is a Microsoft proprietary format that we can't do much with. What we can do is import it into a MS SQL Server. So, this is where you boot up a Windows Virtual Machine or a Windows box if you have one, then install SQL Server Express 2008 which is free to download from Microsoft's website. The next step is to download MySQL Migration Toolkit as part of the MySQL GUI tools from the MySQL website.

Setup the SQL server and be sure to note the login. Now, open up SQL Server Express and then you will execute a SQL command like so where example is the name of your db (e.g., example.bak):


RESTORE DATABASE cobdb FROM disk='c:\example.bak'
WITH
MOVE 'exampledb_data' TO 'c:\exampledb_data.mdf',
MOVE 'exampledb_log' TO 'c:\exampledb_data.ldf';
GO

Ok, so now you have the SQL in the SQL Server as a database. Now you need to open up "SQL Server Configuration Manager" and edit the connection for the server from the network settings. Open TCP/IP settings, then to accept TCP/IP Connections over 127.0.0.1 by selecting Enable in the drop down for it. (Be sure to note the port at the bottom of the form as well). Now, use the MySQL Migration Toolkit to do the connection. Put 127.0.0.1 and a user for the username (can be a Windows user or you can create a user in the MS SQL Server configuration and then turn on SQL authentication then use that user to connect). Now, the tricky part for me that cost me 1.5 hours of my life. The port is probably not the default 1433, but rather a dynamically assigned one. This is why you need to be sure to note the port when setting up the TCP/IP connection.

Now, back in the Migration Toolkit, enter the info for your new MySQL database. If you don't have one then set one up, easiest way is to use PHPMyAdmin if you have it. Regardless, you will need a LAMP stack with it setup, if you don't have the pre-reqs for MySQL, then install XAMPP or something else to get you going fast. Once that is in place, you can proceed with the default export settings, and Voila, finally away from that stinking MS SQL and into the MySQL that we like.

I need to come back later and fill out some detalis here, but you get the idea. Feel free to comment with questions.

Comments

Great help dude!

I have been looking for TWO days for an ultimate solution to this.
Tanks to your help somebody without the knowledge of SQL Server can manage to update his clients to LAMP servers!
Thanks again!!!!!!

Same Hell, Got Migration Kit to work

Yeah, just went through the same. Got the kit to finally work, but had to do some extra work...

http://yakshaver.blogspot.com/2012/06/ms-sql-2008-to-mysql-5x-using-migr...

Please clarify

What does this mean "Now you need to open up "SQL Server Configuration Manager" and edit the connection for the server from the network settings. Open TCP/IP settings, then to accept TCP/IP Connections over 127.0.0.1 by selecting Enable in the drop down for it." ?

I opened "SQL Server Configuration Manager" but I cannot find any option to "edit the connection for the server from the network settings". Where exactly is "TCP/IP settings" ?

Thanks for your post. Seems promising.

re: Please Clarify

You need to open SQL Server Network Configuration. There, you will find the connection for your sql server instance protocols.

thanks so much

now i cant my mssql dbase to mysql, and enjoy with my ubuntu

Commercial tools

I tested a bunch of free and commercial tools and finally stay with soft from http://convertdb.com/mssql/mysql . It seems to be the most reliable and fastest one.

Suggestion if you are willing to pay

We have successfully migrated our MS SQL Server database, of some 50 GB in size very short time, to MySQL using Data Loader tool available at http://www.dbload.com.

It can migrate everything schemas, table data, constraints, indexes and identity columns also.

The best thing of it is speed. But as I said it is a commercial product meant for professionals.

Add new comment

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Comment using an existing account (Google, Twitter, etc.)