Corporate Wide Database Migration

Moving from SQLServer to Oracle as part of a corporate wide strategy

The challenge

• Schema Object incompatibilities - Major incompatibilities between built-in schema objects in SQLServer and Oracle – including USERS, PRIMARY and FOREIGN keys, CONSTRAINTS, TABLES, STORED PROCEDURES, ROLES and PRIVILEGES.
• Unicode characters migration - a key requirements - since existing data contained Kanji (Japanese) characters
• Several million records (several GBs) of data would need to be migrated along with the schema objects
• Database Drivers for code accessing database would need to be selected and optimized – OleDB providers, DataDirect, ODBC drivers for optimal, unicode data migration. 
• Minimize production downtime during actual schema and data migration.

The Solution

• Performing several trial migrations (using several tools including Oracle's Migration Workbench), ANUJ Technologies identified potential migration issues - including data type mismatches, schema object incompatibilities and database driver issues.
• Based on results of the trial migrations, a two step phased migration was conducted - phase one involving just the schema objects in the various databases. This phase could work off the staging database and did not need to touch the production database (since the production schema was synchronized with the staging schema).
• The second phase involved migrating the actual data from the production databases into the newly created Oracle instances. This step utilized an offline database transfer mode which had minimal impact on the existing production database load.
• The migration plan included all UNICODE data as well as all schema objects – including USERS, PRIMARY and FOREIGN keys, CONSTRAINTS, TABLES, STORED PROCEDURES, ROLES and PRIVILEGES.