Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL

While database vendors such as Microsoft and Oracle provide database utilities that assist the database migration process, there are times when a manual process is either required or optimal for completing the database migration. Manual processes include the export and import method, the server level copy and cloning method, the schema copy and DDL generation, and the new Oracle 10g Database Workbench.

The following is an excerpt from chapter one of the upcoming Rampant Press technical book, Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL by Ben Prusinski.

Overview of Oracle Migration

Oracle migration is the process of moving from one database platform to a different database platform. In contrast, a database upgrade would be a movement from one particular database version to a new and later release of database software. There are many similarities between a database migration and a database upgrade. However, for all reasonable purposes, database migration would be for example to move from Microsoft SQL Server 2000 to Oracle 10g. Most database vendors provide upgrade tools such as the Oracle database upgrade assistant (DBUA) and custom scripts to perform upgrades.

However, the in house tools to perform the more difficult and challenging migration process from one particular database vendor to a new platform are extremely limited and require senior database technical knowledge. Database migrations may be performed due to budget constraints such as the licensing costs would be saved with a migration from SQL Server 2000 to Oracle or from IBM DB2 to MySQL platforms.

Another factor that currently drives the requirement for database migrations is that a particular database vendor may not support specific features such as a standby database or real time replication and the client has a need to migrate off of a database platform that lacks these features and to move to one that provides real time replication and disaster recovery features.

For example, the MySQL database environment currently lacks the robust capabilities of disaster recovery standby database and replication that are available with Oracle 10g Streams based replication and Oracle 10g Data Guard standby database. For a large high performance transaction environment that processes millions of transactions per second, a database migration to Oracle 10g from MySQL would make a lot of sense.

Manual Procedures for Database Migration

While database vendors such as Microsoft and Oracle provide database utilities that assist the database migration process, there are times when a manual process is either required or optimal for completing the database migration. For example, there may be cases in which the application environment has legacy and home grown code that must be ported from Teradata to Oracle and to use an automated tool would prove near impossible. Another reason for the usage of manual procedures to complete a database migration would be the fine grain level of control and customization that is possible with a bare bones custom process of migration using scripts and manual conversion methods. Yet another reason may be due to cost and budget. Third party tools have their costs that may be out of range of a customer's budget.

In order to provide a comprehensive list of manual procedures for database migration to Oracle, the following methods will be discussed below.

Export and Import Method for Database Migration (Data Pump)

In this method, the database technician would apply the native database utilities for export and import to complete a manual data pump migration to Oracle. For example, with Microsoft SQL Server 2000, a custom bcp script or DTS package could be used to export the database definitions and data for later import into an Oracle database. The database structure and schema for all of the database objects would need to be generated using MS SQL Server transact SQL (T-SQL) scripts and stored procedures and then a new database structure would need to be created within the future Oracle 10g database environment.

The downside to this approach is that experienced development and database time would be required to complete the manual translation of T-SQL scripts and data types to Oracle environment. Transportable tablespaces can also be used with Oracle 10g migrations either from within export or recovery manager (RMAN) scripts. However, the limitation is that a direct migration from MySQL or Microsoft SQL Server to Oracle is not possible through transportable tablespaces.

Server Level Copy and Cloning Method

In this technique of manual database copy and clone, operating system level and disk based utilities may be used to complete part of the migration to Oracle from another environment such as IBM DB2. For example, if the particular environment uses EMC or Hitachi SAN based storage, then an image copy of the entire operating system and data files can be taken as a snapshot and cloned to a new platform.

The downside is that a great deal of manual coding and database changes must still be performed to migrate the database structures and schemas to Oracle. This is not a recommended approach for migration. Server level copy at the SAN or NAS level is best used with like to like cloning of Oracle to Oracle databases rather than as a pure migration solution.

Replication for Database Migrations to Oracle

This method is possibly one of the best approaches for completion of manual database migrations to Oracle from SQL Server and MySQL. Using the built in facilities in Oracle 10g, database replication methods using Oracle Streams and Oracle Transparent Gateways offer a viable manner to complete database migration. Changes can be captured from the source database platform across a TCP/IP network connection and queued to Oracle Streams queues for processing. However, due to the level of complexity and resources to deploy replication and gateways, it poses special challenges for the busy database administrator.

Schema Copy and DDL Generation

This manual process provides a potential way to complete the manual migration to Oracle. The DBA would need to utilize the native database system stored procedures and functions within MS SQL Server or MySQL to copy out the database structures, mappings and schema DDL for the current environment and map to the new Oracle database platform. This process is time consuming and contains a large margin of possible error.

The new Oracle 10g Database Workbench Migration (OMWB)

With all of the challenges of completing a complex and difficult database migration from MySQL or MS SQL Server, what alternative is available to third party tools and manual custom procedures? Enter the new Oracle 10g Migration Workbench (OMWB) tool now available from Oracle to complete your most difficult database migrations from MySQL and MS SQL Server to Oracle 10g. The Oracle Migration Workbench tool provides a rich suite of robust utilities that automate a majority of the database migration tasks that consume large amounts of time with manual processes.

Dig Deeper on Oracle database administration