Migrating a SQL Server Database to Oracle 11g

After attending a session with an Oracle consultant this week, I thought I’d share with you some tips for migrating an SQL server database to the Oracle platform.

Firstly, let’s get one thing clear. I love SQL Server. I use it for 99% of projects I do. However, for a particular project I’m working on just now, it makes sense to migrate to Oracle for two reasons; rapid scalability and redunancy. Migration is simply put the conversion of an existing database into a new architecture by preserving data and functionality of the old architecture. A typical migration consists of the following steps:

Analysis
Migration
Testing
Deployment

SQLDeveloper is a wizard-driven java tool which will attempt to convert the following database elements for you:

Tables and Data
Primary Keys
Check Constraints
Foreign Keys
Indexes
Views
Groups / Users
Databases
Stored Procedures
Triggers
Grants
Rules
Defaults
User Defined Types

SQLDeveloper performs the following tasks to help you migrate your database to Oracle and is designed to execute tasks in the following order (I have outlined the exact steps at the bottom of this post).

Capture the source database structure into Source Model (online/offline)
Convert to Oracle Model
Create a representation of the structure of the destination database
Migrate the source database
Create the schema online or offline
Transfer the data online or offline

The steps below assume you have followed the steps to complete an OFA-complinant installation of Oracle 11g database on your server. I’ll cover this process in another post soon.

Connect to SQLPlus using the following command: > sqlplus / as sysdba
Create a new user called “sqlmig” with a password of “sqlmig” as follows > create user sqlmig identified by sqlmig default tablespace users temprary tablespace temp
Give permissions required to the “sqlmig” user: > grant resource, create view, create session, create users, dba to sqlmig;
Launch SQLDeveloper 1.5
Create a new connection called “sqlmig” to the Oracle 11g database as sqlmig user
Configure the JDBC Driver for SQL server > Tools -> Preferences -> Database -> Third Party JDBC Drivers > Map to jtds-1.2.2.jar > Copy ntlmauth.dll from the jtds directory to e:jdevelopersqldeveloperjdkjrebin
Add a new connection to the SQL Server Database to your database > User: Windows Authentication and select your database
Right click the SQL connection and select ‘Capture Microsoft SQL Server’
Right click the captured model and select ‘Convert to Oracle Model’
Right click on the converted model and select ‘Generate’
Run the script that is produced
Create a new connection to your new ‘dbo_xxx’ database
Choose Migration -> Migrate data Watch it happen 🙂

Here are the slides from the day:

Leave a Reply Cancel reply