Forum Discussion

Gohulan's avatar
Gohulan
Copper Contributor
Mar 30, 2020

Migrate Data from Oracle to MS SQL Server

Nowadays people in IT are considering to migrate here and there, on premises to cloud sometimes cloud to on premises base on their requirements and resources. When we come to database maintenance here also we are facing the same problems. In the database world there are lot of databases available in the market based on their requirement they will move over to different plat form, the issues when then plan to move over is the data. Whatever the data they used in the past since they can't easily wipe off.

 

In this document I am showing you the way of migrating the Oracle 12c database to latest Microsoft 2019 SQL server without any lost, at the same when we considering the migration we have to make sure that our application also supported with Microsoft SQL server, As I am working on my own application I am pretty sure that I have written the same code to Microsoft as well. 

 

Will start the migration, before starting this I am requesting you to these prerequisites done earlier to the migration 

Oracle Database 12c Installed 

Microsoft SQL Server 2019 Installed  

Microsoft Data Migration Assistant tool downloaded 

Please allow the following ports through your firewall 1521 - Oracle , 1443 - MS SQL

 

Here from my Oracle Database I have several schema, but only migrating the COREDB schema, as we know that An Oracle database contains schemas. An instance of SQL Server contains multiple databases, each of which can have multiple schemas.

Creating the COREDB database in the SQL Server 

 

Create New database in the Destination Server

Login to the SQL Management Studio and Create the new database 

Login with your server name, user credentials and create new database named COREDB

Fresh COREDB database is created, It's blank database 

Will Migrate the Data from the Source Oracle database now 

Open Migration Assistant tool and create a new project 

Connect the Oracle database to the project using Oracle database parameters 

Once it's connected it will load all user schema to the project 

In the mete data explorer will confirm that all the schemas has been loaded to the project 

Now select the schema which you need to migrate and select all other objects as well, if your database is connected with application sever you must select the all objects Views, Functions, Tables and etc

Now we need to connect the destination SQL server with Server credentials, 

Once it's connected check with the meta data explorer on the database 

Once done click on the source Oracle schema and convert the schema to SQL server

After it's finished on the destination server click the database and synchronize the schemas with source database verify that the Arrow is correctly showing from where it needs to be synchronize 

 

Once done login to Management studio and verify that some of the system tables and schema views has been created

After verified, we have finished all the schema creation and the other part of system function views.

Now it's time to migrate the tables and data. Go to the source server schema and click on the migrate data

It will ask the Oracle and Microsoft credentials again, give the correct parameters and click on ok

After verified the source and destination server credentials, it will start the migration of the tables with the data

See the logs and report to verify is there any migration errors or failures â€ƒ

Login to Management studio and see the tables and data has been migrated successfully

To make sure all the data from the source server has been migrated successfully, I have ran a query on the both databases,

On the source server, 

On the destination server,

Yes, I'm done. Both the results are same means its migrated succesfully.

Happy Migration!! 

 

 

No RepliesBe the first to reply

Resources