Recently, I had to work on a migration project where the first step was to migrate the MS SQL server database to MySQL database.
As expected, I started up by searching the internet for a migration tool. I found many some free and some paid. However, the free ones were useless for the job while the paid ones were too costly to purchase a license for one time activity.
So, kept looking for a fool proof solution. Then I stumbled about a post that said that MySQL Workbench itself had a migration utility inbuilt.
So I started off with the migration progress. But I got stuck at the last step where the data had to be finally migrated from MS SQL express database to MySQL. With some more search; I came to know that it was due to a "Collation" conflict between the two. I tried changing the collation on MS SQL Express but it did not allow me to. It then when I stumbled on this simple workaround that I want to share with you guys...
The images below explains all the steps. I will feed in where required;
1. Start MySQL Workbench and select "Migration Wizard" option from the "Database" menu
As expected, I started up by searching the internet for a migration tool. I found many some free and some paid. However, the free ones were useless for the job while the paid ones were too costly to purchase a license for one time activity.
So, kept looking for a fool proof solution. Then I stumbled about a post that said that MySQL Workbench itself had a migration utility inbuilt.
So I started off with the migration progress. But I got stuck at the last step where the data had to be finally migrated from MS SQL express database to MySQL. With some more search; I came to know that it was due to a "Collation" conflict between the two. I tried changing the collation on MS SQL Express but it did not allow me to. It then when I stumbled on this simple workaround that I want to share with you guys...
The images below explains all the steps. I will feed in where required;
1. Start MySQL Workbench and select "Migration Wizard" option from the "Database" menu
2.
3. On this step, make sure that you select Connection Method as ODBC (native) else SQL Express database will not connect
4.
5.
6.
7.
8.
9.
10.
11. This step will create the Schema and the tables in the MySQL database.
12.
13.
14. This step is important... Select the option to "Create a batch file to copy the data at another time"
15.
16.
17. This is the batch file that gets created at the location you selected.
18. When you execute the batch. It will create a folder that will have a "import_DB.cmd" batch file
You need to execute that.
19. When you execute the batch cmd in previous step; it create a folder names "DB_#####_import"
20. You need to place this folder in the MySQL database data folder. In my case since I am using MySQL through Xampp; I placed at following location i.e.
H:\xampp\mysql\data
21. After placing the folder at proper location; go to import data option in MySQL workbench
22. Select the option to "import from self-contained file" and select the "import_db.sql" file from the folder created in step 17. And click on import button to successfully import the data to the Migrated Database.
It took me two days to figure out a successful way to migrate MS SQLExpress database to MySQL. Hope this tutorial helps you get things done quicker.. :-)
0 comments:
Post a Comment