Data Management optimize

SolutionBase: Migrating a SQL Server database to MySQL 5.0

Making the jump from Microsoft SQL Server to MySQL 5.0 might sound like an intimidating process, but it's not as bad as it may seem. In this article, Bob Watkins shows you how to migrate SQL Server databases to MySQL 5.0.

Prior to version 5.0 of MySQL, migrating other databases to MySQL was largely a manual process. Version 5.0 introduces the MySQL Migration Toolkit, which provides in graphical form a step by step process for migration. This extensible tool generates scripts that recreate tables from other database systems such as Microsoft SQL Server, Microsoft Access, Oracle, and older versions of MySQL. The tool then copies the data to populate the new tables. In this article, we'll take a guided tour of using the MySQL Migration Toolkit to migrate a database from Microsoft SQL Server, as well as examine a few other alternatives.


Overview of the migration process

Migrating an existing database to another vendor's platform is all about metadata: the data dictionary entries that a database uses to define the objects within it. The structure of the existing database must be understood, including not only the data tables but other objects such as constraints, views, stored procedures, and triggers. Then equivalents must be created in the target database, or workarounds must be found. Finally, the data must be transformed into the new formats and loaded into the target database.

The MySQL Migration Toolkit

The MySQL Migration Toolkit is not a single tool, but a framework for migrating databases. Beneath its graphical interface is a Generic RunTime environment (GRT) that executes a series of modules to perform the actual migration tasks. Reverse Engineering modules extract the metadata from existing databases into GRT definitions called objects; Migration modules convert source GRT objects to target GRT objects; and Transformation modules convert target GRT objects into actual SQL CREATE statements.

The result is a structure that can be extended to support additional databases and to provide transformations for new feature sets. End users may also develop their own modules in C, Java, PHP, and Lua. Support for Python and Mono are in development.

The MySQL Migration Toolkit divides the migration process into eight steps, and presents them in wizard form (see Figure A):

  • Source/Target is the specification of connection information.
  • Object Selection defines which objects in the source database to migrate.
  • Object Mapping indicates how the translation between datatypes will be accomplished.
  • Manual Editing is a pause in the action, during which you can inspect and modify the generated migration scripts before they are run.
  • Schema Creation runs the first set of scripts, which create new objects in the MySQL database.
  • Data Mapping presents options for moving the data itself between the source and target tables.
  • Bulk Transfer actually moves the data.
  • Summary presents a log of the actions of the run.

Figure A

MySQL helps you migrate databases by using a Wizard.

Example: Migrating the sample Northwind database

In the descriptions below, the Northwind sample database from SQL Server 2000 is converted to MySQL via the MySQL Migration Toolkit. The migration was performed from SQL Server 2005 to MySQL 5.0, both running on Microsoft Windows Server 2003 systems. (Northwind does not come standard in SQL Sever 2005, but can be installed via a free download from Microsoft.)

To start the Toolkit, go to the Start menu, All Programs, MySQL, and select MySQL Migration Toolkit. The initial splash screen checks that the Java Runtime Environment is present and at the correct version. Click Next to begin the migration process.

Source/Target

The first step is to specify source and target database connection information. On the first screen of this step, indicate whether you will be performing a direct migration, or one that is agent-based. Agent-based migration enables you to have a third machine perform the migration, by installing an agent program on one of the other two machines (Source or Target). This involves a lot of network overhead, and so the direct approach is preferable.

Next, you identify the source database by type and connection parameters. The supported types, chosen from dropdown list, are MS Access, MS SQL Server, another MySQL server (such as an older release), Oracle, and a generic JDBC connection. This latter option lets you migrate data from other database systems not listed above, as long as they have a JDBC driver. Figure B shows a Microsoft SQL Server connection to Northwind.

Figure B

Connecting to the Northwind database through a Microsoft SQL Server connection.

For a SQL Server connection, you will need to enter the hostname, port number (usually 1433), username, password, and database. Note that this is a SQL Server style login, so your SQL Server instance must be set up for Mixed Authentication. Windows-only authentication will not accept a username/password combination to establish a connection.

The only target database supported in this first release is MySQL Server, using the JDBC Driver 3.1. As with the source database, enter hostname, port (usually 3306), username, and password. The database is not entered: a new catalog (schema) with the same name as the original will be created. The user you specify here must therefore have the necessary privileges to create a new schema and CREATE and DROP objects within it.

The next screen is a status report that shows any errors encountered in connecting to the source database, retrieving its schema, and testing the ability to connect to the target. If any item is marked as failing, click the "Advanced>>" button to see log messages.

Object selection

The next step is selecting which schemas and objects to migrate. On the first screen, select the SQL Server database to migrate - there will be only one, as you had to connect to a specific one earlier. (For other database systems, such as Oracle, the connection may result in multiple schemas that could be chosen.)

After another status screen that indicates whether reverse engineering of the source database was successful, the following screen presents three different types of objects it contains: tables, views, and routines (stored procedures). See Figure C.

Figure C

Here you select the objects in the database.

For each, you can select all objects of the type with a single checkbox. Or, you can click "Detailed selection >>" under each to see a list of objects of that type. By default, all objects are selected; you can mark individual ones to be ignored during the migration.

Object mapping

Available mappings, called migration methods, depend on the source database type. For MS SQL Server, there is a specific migration method called MS SQL Default. There is also a generic migration method available for all types of source databases. You can set the migration method separately for categories (table, view, routine) on the first screen.

You can also change character set for the schema during migration. The choices are Latin1, Multilanguage (which uses Unicode character set UTF-8), or a user-defined choice.

At the end of the step is another status screen that indicates whether migration scripts for all objects were successfully generated. Scripts are generated at this point, but not executed until later.

Manual editing

After the scripts have been generated, the Toolkit enables you to manually edit them if necessary to fix errors or to add MySQL specific features you'd like to include as you can see in Figure D. A dropdown filter lets you show only objects with errors, or all objects, in the list. Clicking on any object, then the "Advanced >>" button, lets you see the actual generated script and modify it.

Figure D

You can filter SQL scripts on this screen.

In the version reviewed, only Table objects generated "live" scripts that would run. View and Routine objects generated scripts that were commented out by default. However, you can uncheck the Comment Out checkbox in the Advanced display to attempt to create them during the run.

Schema creation

The fifth step, Schema Creation, actually builds the target schema in the MySQL database. Its first screen has two choices: Create Objects Online, or Create Script File For Create Statements. You can select either or both. If the generated scripts had many errors, it may be easier to simply generate the script, hand edit it, and execute it later rather than open each object's script individually. It also serves as good documentation of the run.

The second screen runs the scripts and creates the schema. The final screen of the step shows any errors that occurred.

Data mapping

The sixth step, Data Mapping, prepares for the bulk transfer of data from SQL Server tables to the target MySQL tables just created. This step is a single options screen that lets you transfer the data online, or to create a script of INSERT statements to do it.

Bulk transfer

The seventh step, Bulk Transfer, processes the bulk transfers on a table by table basis. Clicking the "Advanced >>" tab will show you a log of the execution as you can see in Figure E. Be very careful of the log files that are created in this step! They contain logins and passwords for both source and target systems, in plain text. Be sure to remove these logs or otherwise secure them after they are created.

Figure E

You can view logs to check to see how the bulk transfers work.

The last step

The eighth and final step is a Summary of the run, which can be saved to disk. You also have one last chance to generate a migration script from the information presented during the run.

In the example run, the tables of the Northwind database were migrated to MySQL. Figure F shows the view from MySQL Administrator. Browsing the Northwind_dbo catalog (schema) shows a list of tables and their current sizes. Right-click on a table and choose Edit Table Data to bring up a query window with the data for inspection.

Figure F

You can view the results of your migration here.

Other alternatives

There are several alternatives to using the MySQL Migration Toolkit to migrate SQL Server data. These include SQL Server Integration Services (SSIS), third party data modeling tools, and manual scripting. SQL Server Integration Services and its older cousin, Data Transformation Services, can be used to migrate table definitions and their data directly to MySQL via MySQL's ODBC driver.

To do this, create an Integration Services project in SQL Server Business Intelligence Development Studio (BIDS). Either open the empty package Package.dtsx and design the data flows, or create a new SSIS package in the project using the SSIS Import and Export Wizard. (If using DTS on SQL Server 2000, just create the package using DTS Designer or DTS Import and Export Wizard.) After identifying the source and destination connections, you'll have an opportunity to map the data types and perform transformations during the transfer. Save the package, and execute it to perform the transfer.

Because SSIS (and DTS) can only copy non-table objects between two SQL Servers, you'll only be able to copy tables and views this way, not stored procedures.

Data modeling tools

Several third party data modeling tools have reverse engineering features in them. Embarcadero's ER/Studio, for example, can both reverse engineer a database on one platform, and generate object creation scripts for another. Provided you already have such tools available, they can be useful in a migration.

Manual scripting

The hardest part of a manual conversion is the creation of metadata and mappings, two things the MySQL Migration Toolkit automates. For databases with relatively few tables, SQL Server DTS or the older BCP utility can be used to export the data to flat file format.

To load this data into MySQL, use the MySQL LOAD DATA INFILE utility.

White papers and case studies are available on the MySQL AB Website, on the Migration Page.

The Bottom Line

Manageability was a key concern in the design of MySQL 5.0. In addition to support for program objects such as views, stored procedures, and triggers, the release includes tools for the administrator such as MySQL Administrator and the MySQL Migration Toolkit. Together, these tools streamline the process of getting a new MySQL database populated with existing data.

0 comments