Migrating to MySQL: Dmitry Tolpeko.

For successful migration of enterprise databases running on Oracle, SQL Server, Informix and base to MYSQL, you have to choose tools that automate the entire migration process and avoid the risks and hidden costs associated with partial conversion.

The latest versions of MySQL largely extend the range of enterprise applications that can benefit from the high performance, reliability, scalability and cost reduction offered by this database server. That is why more and more companies today consider switching their mission-critical enterprise databases running on Oracle, Microsoft SQL Server, Sybase and Informix to MySQL.

However, database migration is a big challenge to IT professionals because it involves a multitude of conversion issues and problems caused by considerable incompatibilities between the leading databases and the necessity to preserve the proper execution of the existing database applications. IT specialists often underestimate the complexity of database conversion and do not take into account all the issues involved in this process. As a result, they use tools that ensure only partial conversion and have to deal with the remaining tasks manually. But due to the large number of objects (tables, procedures etc.) in enterprise databases, the manual handling of any unexpected problems appearing in the ongoing projects can lead to long delays and dramatically raise the migration cost.

The purpose of this article is to describe the problems and tasks involved in the migration of enterprise databases to MySQL, and to help database professionals choose the tools that ensure the most comprehensive and seamless conversion and make it possible to complete the migration project on time and on budget.

Enterprise Database Migration Requirements

Besides the significant differences between the SQL syntax and functionality of different database management systems, you have to take into account the following factors heavily influencing the complexity of enterprise databases migration and the choice of migration methods and tools.

Database and Application Conversion--Not Only Data Transfer

Enterprise-class database management systems like Oracle, SQL Server and others provide rich functionality for application development. Being tightly bound with applications, databases presently implement a significant part of business logic and rules, improving the performance, security and modularity of mission-critical applications. For this reason, database migration should not be considered merely as data transfer. It also involves the conversion of database schema (tables and views definitions, integrity constraints etc.) and server-side business logic (stored procedures and functions).

All these database features and database objects are very important for applications and must be fully converted in order to preserve applications? behavior and functionality.

Large Number of Database Objects-Costly Manual Efforts

Corporate databases contain from hundreds to thousands of database objects (tables, views, procedures etc.). Due to this large number of objects and the variety of used features, a migration tool should resolve as many conversion tasks as possible. Manual fixing, even of some minor issues, involves many objects and, therefore, becomes extremely time-consuming and costly.

Another problem is the great interdependence of database objects. Almost every procedure makes references to tables, views and other procedures and such dependences must be maintained in the course of conversion. For example, if a source identifier (table or column name etc.) is a reserved word in MySQL, it must be enclosed by special characters or changed when migrating to MySQL. This modification must also be done in all views, procedures and applications that refer to this identifier.

Large Volumes of Data

Enterprise databases usually store large volumes of data. Due to this fact, transferring data to MySQL can take from several to dozens of hours, depending on the chosen method of data import. Time difference in this case is considerable and it is important to use the most appropriate tool to transfer data as quickly as possible

Requirements for Migration Software

Taking into account the specifics of enterprise databases and applications described above, we can define the particular requirements for migration software due to be used for automating most of the conversion tasks and avoiding manual effort.

Comprehensive and Integrated Solution

Migration software must convert not only data, but also the entire database schema and business logic (stored procedures e.g.).

This software should support converting data including LOB columns (images, video etc.), table definitions including data types, NULL and identity properties, default values, primary and foreign keys, unique constraints, views, stored procedures and functions.

Besides the SQL syntax differences, the software must also solve such specific issues like reserved word and identifier conflicts. Every database has a list of reserved words that are not allowed to use as identifiers without quoting. The problem arises when an identifier is not a reserved word in the source database, but is a reserved word in MySQL. For example, LIMIT is not a reserved word in Oracle, but is a reserved word in MySQL. While converting to MySOL, such identifiers must be quoted or changed everywhere they are used (in views, stored procedures etc.).

The tool should be also aware of the unique MySQL syntax as well as such requirements as the necessity to generate indexes for foreign key columns in child tables not required in other databases. The migration tool should be integrated in order to automatically maintain changes in the related database objects. For instance, if any change is made to a table, it must be automatically reflected in all the dependent objects (views, stored procedures) that refer to this table. If the migration software does not meet these requirements and converts only data e.g., you will have either to complete the conversion manually or build a more complicated migration solution combining several tools. In the latter case, such tools are unlikely to be integrated, so any changes made to the database in one tool are not considered in the others.

Flexible Solution providing Global Rules

Generally, it is not required to considerably transform the database structure and data during the conversion since the consequent need to fully rewrite the applications would make the migration very expensive. In most cases, the migrated databases are not modified in order to fully preserve the functionality of applications. Yet, quite often you may require slightly changing the database schema or setting specific options to meet new requirements of applications.

For example, you may require changing the data type mapping between the source database and MySQL or specifying the InnoDB table type for supporting foreign keys and so on.

You may also want to specify rules for converting the identifiers which are reserved words or contain special characters. By default, such identifiers are enclosed with quoting characters (backtick in MySQL), but it is unusual and inconvenient for developers to use quoted identifiers when writing SQL queries or procedures, and you may want to change such identifiers for avoiding obligatory quoting.

Finally, database migration software should be flexible enough to allow you performing any required changes and defining various settings. In addition to being able to change every individual object, the tool should also offer you a way to globally set options or rules effective for all objects. Otherwise it can be very time consuming to set the same options for multitude objects.

Facilitating Application Conversion

Most frequently, the application must be modified after the database conversion. Usually, these modifications do not affect the application structure and it is required to change native SQL statements, take into account changes made to the database (identifiers, data types etc.) as well as resolve possible interface incompatibilities between the application and MySQL database.

It is important for the migration software to provide a report about all the changes made in the database during the conversion. There is no need, though, to report about all the database objects and columns since such information can be obtained from any database management tool and does not allow analyzing changes that affect application execution and require its modification.

The migration software should facilitate the migration of applications and provide utilities to convert SQL statements and resolve other conversion issues.

High Performance

Since enterprise databases store large volumes of data, it takes a long time to transfer data from the source database to MySQL. For this reason, it is important that migration software provides the fastest method of importing data to MySQL.

Like Oracle or IBM DB2, MySQL offers a high-performance tool that is especially efficient for loading large volumes of data. The MySQL LOAD DATA INFILE, command is usually 20 times faster than the import of the same data with the help of SQL INSERT statements.

The migration software should offer an option to migrate data with help Of LOAD DATA INFILE in order to dramatically reduce the time of data transfer (that can take several hours even using this high-performance command).

Transferring data using ODBC interface and SQL INSERT statements can be unacceptable for enterprise databases. In order to increase the performance of data import, the conversion software should also allow creating integrity constraints and indexes after importing data.

Migration Software Customization Services

In general, database migration is a very complex undertaking to be automated by 100% for all possible databases and applications.

Nevertheless, provided that each database and application use a limited set of features, the presently available migration tools can automate the conversion of up to 95% of them.

As described above, due to the large number of objects and any manual effort of migrating even 5% of what is remained after the automated conversion increases the total cost and time of the project to a great extent.

For this reason, it is very important that the vendor of the migration software provides services to promptly tailor the software to the particular project needs and avoid manual effort.

Overview of Migration Tools for MySQL

Presently, there are several tools on the market that can convert enterprise databases to MySQL.

Ispirer SQLWays

SQLWays is the most comprehensive migration tool for MySQL. It converts data, database schema (table and view definitions, data types, primary and foreign keys, unique constraints, identity and NULL properties, default values, comments) and business logic (stored procedures and functions) from Oracle, SQL Server, Sybase, IBM DS2 and Informix to MySOL.

SQL Ways is specialized migration software, flexible and effective for use in various conversion projects. The tool allows you to redefine data type mapping globally and for each table, choose table type to store data (innoDB e.g.) and specify other options. SQL Ways automatically resolves identifier and reserved word conflicts. In the latter case as well as when the user modifies database objects, the tool reflects all changes in dependent tables, views, stored procedures etc.

SQL Ways exports the source database to SQL scripts, so you can extend the migration process using third party tools or script languages (Perl e.g.)

SQL Ways creates report on modifications made during the conversion and facilitates the conversion of applications. Like the migration tools provided by Oracle and IBM for their databases, SQL Ways uses the proven the export/import process effective for migrating enterprise databases, and internally uses the high-performance MySQL LOAD DATA INFILE, command to provide the fastest data import to MySQL.

For more information about SQL Ways, see http://www.ispirer.com

Embarcadero DT/Studio

DT/Studio is a high-performance ETL tool (Extraction, Transformation and Loading) that can be used for transferring data from various databases to MySQL.

DT/Studio is intended for using in data warehouse projects and focused mostly on data transfer and transformation. But since DT/Studio has data modeling and reverse engineering capabilities, it also allows you to convert database schema to MySQL.

Although it is not typical for migration projects, DT/Studio is highly effective if you need to significantly re-design database structure and transform data during the conversion, since it provides a lot of transformation features and functions.

DT/Studio does not support conversion of server side business logic (stored procedures and functions etc.) and applications to MySQL.

For more information about DT/Studio, see http://www.embarcadero.com/produedts/dstudiol

Microsoft DTS 

Microsoft DTS is an ETL tool (Extraction, Transformation and Loading) that can be used to transferring data from various databases to MySQL.

DTS is also mostly focused on data transfer and transformation and allows you specifying data transformations using Visual Basic scripts.

DTS allows you to redefine data types for each table, modify table and column names and specify CREATE TABLE statements for tables. You have to set options for each table individually; DTS does not provide options affective for all converted tables.

DTS provides limited capabilities for the schema conversion. It does not support primary and foreign keys, unique constraints, default values and identity columns. OTS does not convert views and stored procedures to MySQL.

For more information about DTS, see http://www.microsoft.com/sql

Conclusion

You have to thoroughly assess the requirements of your migration project and choose the most appropriate tool that will cover all the conversion issues and eliminate the risks and hidden costs associated with partial conversion. While converting enterprise databases to MySQL, you should reduce the manual effort to the greatest possible extent; otherwise the migration project may be very time consuming and incur unexpected additional costs.