I am a danish programmer living in Bangkok.
Read more about me @ rasmus.rummel.dk.
Webmodelling Home > Database migration
Do good

Database Migration

Mar 2012. I had an SQL Server Express database that hit the size limit of 10 GB and since it supported a non-profit project I did not want to move the database to a licensed SQL Server. Instead I decided to try to migrate it to MySQL and that turned out to be relatively easy.


Migrate from SQL Server to MySQL

Conversion problems : (relatively easy I said - not without a few problems)

  • Some data types are not or cannot be converted properly :
    SQL Server MySQL Comment
    nvarchar(MAX) VARCHAR(-1) VARCHAR(-1) is not a valid data type and you need to manually edit the MySQL Migration Toolkit script result to use TEXT instead (this is very easily done as part of the Toolkit process flow and also covered below).
    uniqueidentifier VARCHAR(64) Problem with the .NET Entity Framework that access columns as typed objects - all that code needs to be rewritten.
  • MySQL table names are default lower case and case insensitive : this is a problem with .NET Entity Framework or any other context in which table names are referred as case sensitive objects. To be able to name tables with upper cases in MySQL, you need to :
    1. Locate the my.ini file.
    2. Edit or add the following entry to my.ini file :
      On Windows : lower_case_table_names=2
      On Unix : lower_case_table_names=0
    3. Restart the MySQL service.
  • MySQL InnoDB can use maximum 767 bytes for keys, which is very small. In my SQL Server I had a table using a nvarchar(1000) field as a unique index, which by MySQL Migration Toolkit was translated to UNIQUE INDEX `ix_searchterm` (`SearchTerm`) and therefore then trying to create the table gave error #1071 - Specified key was too long; max key length is 767 bytes. A fast solution was to delete the index from the table creation script.

How to migrate from SQL Server to MySQL using the MySQL Migration Toolkit - step-by-step :

  1. Download the MySQL GUI Package which contains the MySQL Migration Toolkit
  2. Install the MySQL Migration Toolkit - if possible on the same machine as either the source SQL Server or the target MySQL server.
  3. Start the MySQL Migration Toolkit
  4. System check :
    • If "Initialized Java Loader" fails, you need to install the Java Runtime Environment (JRE) :
      1. Download JRE and install it.
      2. Restart MySQL Migration Toolkit - "Initialized Java Loader" should now pass.
  5. Configuration : choose "Direct Migration".
  6. Source Database : specify connection details to the database you want to migrate
    • Database System = MS SQL Server
    • Hostname = localhost : if MySQL Migration Toolkit is on the same server as the SQL Server.
    • Port = 1433 : this is the default port, however it may not have been defined or another port may have been defined.
    • Username = sa : any user with access to the database you want to migrate.
    • Password = YourPassword
    • Database = YourDatabase : the name of the database you want to migrate.
    Click the Databases Button next to the database field to confirm a connection can be established. If no error is displayed, MySQL Migration Toolkit can connect to the specified database. However typical errors are :
    • If you get error : No suitable driver found for jdbc:sqlserver, then :
      1. Download Microsoft jdbc driver for SQL Server.
      2. Unzip in %programfiles%
      3. Add the path to jdbc4.jar to your classpath environment variable.
      4. Restart your machine.
      5. Re-run MySQL Migration Toolkit.
    • If you get error : Network error IOException: Connection Refused, then go here.
  7. Target Database : specify connection details to the MySQL server on which you want to create the target database
    • Database System = MySQL Server
    • Hostname = localhost : if MySQL Migration Toolkit is on the same server as the MySQL Server.
    • Port = 3306 : this is the default port, however another port may have been defined.
    • Username = YourUsername : any user with access to the database you want to migrate.
    • Password = YourPassword
  8. Connection Check.
  9. Schema Selection.
  10. Reverse Engineering.
  11. Select the tables you want to migrate (default all tables of selected schema are selected).
  12. Mappings : possible to finetune the mappings.
  13. Migration : the sql script to create the objects is now created.
  14. Object Creation Options : choose if you want to actually create the objects on the target MySQL server and you may want to dump the object creation script to file.
  15. Creating Objects : the target database, it's tables and other objects are now created (data are not yet transferred). Even though it seems successfull, there may actually be problems.
  16. Creation Results : One of the tables, SearchResults, could not be created :
    1. Press the Advanced Button to show an sql script editing area.
    2. Press the SearchResults table to load the sql script snippet used to create the SearchResults table.
    3. Edit the part of the snippet that is faulty - in my case SQL Server nvarchar(MAX) data types have been converted to illegal MySQL varchar(-1) data types.
    4. Press the Apply Change button (that have become clickable after editing the script snippet).
    5. Press the Recreate Objects button to restart the object creation process based on the updated script snippet.
    6. You are back at the Object Creation Options (though the top of the window shows Creation Results).
    7. Objects created again.
    8. This time it seems all objects was created.
  17. Data Transfer : this can take quite a long time
  18. Migration Completed.
  19. Check that the objects was created, here I use MySQL Workbench. Unfortunately MySQL Migration Toolkit seems to postfix _dbo to the target database, let's solve that :
    1. Executing the following 4 sql statements against the MySQL server : (here I use Workbench)
      • create database DBName;
      • rename table DBName_dbo.TBLName1 to DBName.TBLName1; : using your own database & table names of course.
      • rename table DBName_dbo.TBLName2 to DBName.TBLName2;
      • drop database DBName_dbo;
      • If you are using Workbench then remember to click the Refresh icon.
    2. Indeed, a new database without the _dbo postfix now exist with the 2 table objects.

Congratulation - your SQL Server database should now have been successfully migrated to MySQL.


Comments

You can comment without logging in
 
 B  U  I  S 
Words: Chars: Chars left: 
 Captcha 
 Nickname
Facebook