[DB MIGRATION] Database Migration from MS SQL Server 2005 to Mysql 5.5 – Step by Step procedure


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.

Software Used:

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.

Migration Steps:

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

jdbc:jtds:sqlserver://<localhost>:1433/<dbname>;user=sa;password=test;

Then click next.

Enter Destination Mysql Server details

Click next, then select SQL Server 2005 database and click next.

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!

Advertisements

About Dominic

J for JAVA more about me : http://about.me/dominicdsouza
This entry was posted in Thechy Stuff. Bookmark the permalink.

One Response to [DB MIGRATION] Database Migration from MS SQL Server 2005 to Mysql 5.5 – Step by Step procedure

  1. clezer says:

    HE ALSO CONVERTS MY PROCEDURES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s