|
|
|
The migration process is indeed very straight forward (video); however I ran into a few minor issues with my migration from SQL Server 2005 to MySQL.
At first, the MySQL Migration Toolkit didn’t appear to have SQL Server 2005 to MySQL database support. Now, I’ve used the MySQL Migration toolkit for data migration in the past (with SQL Server 2000) and it worked flawlessly for me.
However, when I tried using the MySQL Migration Toolkit to import my SQL Server 2005 database (using “SQL Server” as the source database with the default of port 1433), the MySQL Migration Toolkit would throw a “Connection Refused” exception. As a result, I changed the port to 1434 (the correct port) and then the MySQL Migration Toolkit would proceed further, but then it would throw an exception at the “reverse engineering” step.
After a brief Internet search, I saw that this “SQL 2005 to MySQL” migration issue had been submitted at the MySQL bug tracking site: Bug #20674.
Basically, to solve my SQL Server 2005 to MySQL database migration problem, I had to change the SQL Server 2005 configuration in the SQL Server Configuration Manager. You have to set SQL Server 2005 Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP to Enabled.
After I made this change to the SQL Server 2005 configuration (and restarted the MSSQL server), I was able to use the MySQL Migration Toolkit with port 1433 and the SQL Server 2005 to MySQL database import worked as intended! WooHoo!! 
Additional notes…
You may need to enable MS SQL Server Mixed Mode (SQL Server and Windows Authentication mode) so that the MySQL Migration Toolkit can establish a connection.
Related links:
MySQL Migration Toolkit – the official site from MySQL.
How to: Change Server Authentication Mode – How to enable MS SQL 2005 Server Mixed Mode.
Migrate data from SQL Server to MySQL – useful article for SQL Server to MySQL migration.
VIDEO: SQL Server Migration using the MySQL Migration Toolkit – From the MySQL folks, this video explains how to migrate data from SQL Server to MySQL.
SQLYog Enterprise – MySQL database synchronization and more.
If you’re coming from another database environment such as Microsoft SQL Server and you’re looking to generate create table scripts on Windows with MySQL, you may want to consider the following options …
mysqldump.exe
From the command prompt, navigate to your MySQL bin directory and use the mysqldump.exe command-line application as follows…
mysqldump -u USER --password="MYPWD" DBNAME --no-data > create_tables.sql
If you leave off the –no-data argument, mysqldump will dump the entire database into the create script. The mysqldump.exe documentation explains all the possible arguments and there are very useful user comments/scripts at the bottom of the online documentation page.
To bring a database script back into MySQL:
mysql.exe DBNAME < create_tables.sql
Within MySQL Query Browser, right click on a particular table (under a particular database in the Schemata treeview) and select "Copy CREATE statement to clipboard." This option will allow you to copy and paste the MySQL create table script into your favorite text editor.
Also, while inside the MySQL Query Browser, you can open a new Script tab under "File-> New Script Tab" (or by pressing Ctrl + Shift + T). Once the script tab is open, drag tables onto the script tab to generate the CREATE TABLE script.
More useful links and information concerning how to generate MySQL create table scripts...
