Migrations between databases are quite simple especially when there are some really simple tools. But sometimes it does become hectic with a lot of problems. I wanted to migrate a MS SQL Server 2005 database to Mysql and ran into some problems for which the solutions was damn simple. If you google there are a lot of articles or blogs which mention migration steps, i just wanted to have every thing in one place staring from scratch.
MS SQL Server 2005 & SQL Server Management Studio [Express versions]
Mysql Server 5.5
Mysql Migration Toolkit comes with Mysql GUI Tools 5.0 [End of Life]
OS: Windows 7 Pro.
Step 1: Setting up MS SQL Server 2005:
Open your MS SQL Server management Studio and connect to the database either by using Windows Authentication or SQL Server Authentication.
Then right click your server in the Object Explorer and click Properties.
In the Server Properties Dialog go to Security and then ensure that in Server authentication “SQL Server and Windows Authentication Mode” also know as Mixed Mode is selected. Following is the snap
[optional] You can also add a new user if you dont have one. By right clicking Security in the Object Explorer and clicking New -> Login. Also permit the user to the database engine.
Then open SQL Server Configuration Manager which comes as a part of the Configuration Tools. Expand the SQL Server 2005 Network Configuration and select Protocols for <SQLEXPRESS>.
Now Ensure that TCP/IP is enabled, if it is disabled right click and click enable. If you are using Named Pipes you have to enable that too.
Then right Click TCP/IP and click Properties, it opens up a TCP/IP Properties dialog. In that dialog box select IP Addresses and in that section put for IP1 and IP All, TCP port as 1433 (this sql server default you can use any other if you like). and click OK. Following is how it looks
Then restart the SQL Server for the above changes to take effect. You can do that in Configuration Manager in the SQL Server 2005 Services. right click restart.
Thats it your MS SQL Server setup is finished.
Step 2: Mysql Migration Toolkit: More pictorial steps are provided by this blog entry here http://www.techrepublic.com/article/solutionbase-migrating-a-sql-server-database-to-mysql-50/6060626 , either you follow this or else continue below.
Since the latest Mysql Workbench does not include the Migration toolkit hence we have to use the GUI Tools even though it has EOL.
Now download and install Mysql GUI Tools (Complete install) you can get that here http://dev.mysql.com/downloads/gui-tools/5.0.html.
Mysql Migration Toolkit requires java version 1.5 or latest. i had already 1.7 installed on my machine. Please check your java version my typing “java – version” in a command prompt.
Open Mysql Migration Toolkit and follow the onscreen instructions till you reach Source Database selection. In this, you have to select from the combo MS SQL Sever. Then put in the hostname and port as we have configured earlier. Enter username and password. if you dont have a user please add a user and give it rights as indicated in Step 1. Then click the button “…” and select the database you want to migrate.
Some times the above does not work, In that case click the Advanced button at the bottom and paste the Connection String. following is the connection string
Then click next.
Enter Destination Mysql Server details
Click next, then select SQL Server 2005 database and click next.
Select the objects that you want to migrate, i am going for only tables here. Click next.
Go on clicking next with default selections till you reach Creation Results section. Where you have to correct the errors occurred during migration.
Correct the error by clicking on each table and click Advanced button, change the script and click “Apply Changes”. And after doing all the changes click “Recreate Objects”. This will try to recreate Objects again.
Once you have no errors you get the success screen shown as follows:
Thats it the Migration is complete!