Migration from Oracle to SQL Server

In this article, we will understand the step by step Migration from Oracle to SQL Server. To complete this configuration we need to follow below steps.

I am using MSSQL Server 2012 and Oracle 12c R1 versions for this activity. You can use any version as per your requirements.

  • Download the Microsoft SQL Server (version which meets the prerequisites).
  • Download the SQL Server Migration Assistant 8.8 for Oracle.
  • Download the Oracle Client Software for windows same as Oracle Database Version.
  • Installation of Oracle Client Software.
  • Installation of Microsoft SQL Server.
  • Installation of SQL Server Migration Assistant for Oracle.
  • Connect to the Oracle Database.
  • Connect to the SQL Server Instance.
  • Convert Schema from Oracle to SQL Server.
  • Synchronize with the database.
  • Migration from Oracle to SQL Server.

Download the SQL Server Migration Assistant for Oracle: –
Advertisement

Please use below link to download the SQL Server Migration Assistant 8.8 for Oracle software.

Link: – https://www.microsoft.com/en-us/download/details.aspx?id=54258

System Requirements for SQL Server Migration Assistant 8.8 for Oracle: –

System Requirements for SQL Server Migration Assistant 8.8 for Oracle

Download the Oracle Client Software for windows same as Oracle Database Version: –

As I told you earlier that I am using Oracle 12c R1 for this activity, so I will also download Oracle Client and Oracle Database software of same versions as well.

Please use below link to download the Oracle Client Software software.

Oracle Client Software Link: https://www.oracle.com/database/technologies/database12c-win64-downloads.html

Windows Client 12c R1 software

Oracle Database Software Link: – https://www.oracle.com/database/technologies/database12c-linux-downloads.html

Oracle Database 12c R1 software

Installation of Oracle Client Software: –

Step 1) First you need to extract the zip file and then execute the setup.exe file.

Execute the setup file

Step 2) when you execute setup.exe file then GUI will get invoked, Choose Instant Client Option to install the basic client utilities.

Choose Instant Client Option

Step 3) Choose the Software Location where binaries will get installed.

Choose Software Location

Step 4) Look at the summary and if all looks good, press the install tab to proceed with the Installation.

Migration from Oracle to SQL Server

Step 5) After Client Installation again execute the setup.exe file to install the OraOLEDB.Oracle.1 provider which is mandatory during the connection of Oracle database.

Note: – Now this time choose Custom option.

Choose Custom option to install OraOLEDB provider

Step 6) Select Use Windows Built-in Account option under Oracle Home User tab.

Select Use Windows Builtin Account

Steps 7) now specify Oracle Base and Oracle Software location.

Note: – It will automatically detect Oracle Software Location path because we have already mentioned during Client Installation.

Migration from Oracle to SQL Server

Step 8) Now choose the below components and make sure you don’t miss to check mandatory component Oracle Provider for OLE DB.

1) Oracle Database Utilities.

2) SQL*Plus.

3) Oracle Net.

4) Oracle Provider for OLE DB.

Oracle provider for OLE DB

Step 9) Look at the Summary and if all looks good then press the install tab to proceed with the Installation.

Summary part Oracle OLE DB

 

Installation of Microsoft SQL Server: –

After Installation of Oracle Client, now we need to install the Microsoft SQL Server. I am using 2012 version.

Step 1) Extract the files and execute the setup file.

Migration from Oracle to SQL Server

Step 2) Click on the New SQL Server standalone installation or add features to an existing installation link.

New SQL SERVER standalone installation link

Step 3) Now First it will look for product updates and after that you can proceed further for Installation of setup files.

Installation is in progress

We can see from above output that 2 steps have been skipped because we have already downloaded the setup files and extract it.

Step 4) Select perform a new installation of SQL Server 2012.

Migration from Oracle to SQL Server

Note: – I have already Installed SQLEXPRESS instance, but in your case you have to choose first option.

Step 5) Accept the License Terms and Click on Next tab.

Step 6) Select all the options under Feature Selection and click on Next.

Feature Selection

Step 7) now you can choose Default Instance or Name instance as per your requirement, I am choosing named instance.

Migration from Oracle to SQL Server

Step 8) Keep Service Accounts setting as default one, if you want to change collation then you can change it as per your requirements.

I am using default collation: – SQL_Latin1_General_CP1_CI_AS

Step 9) Specify Database Engine authentication security mode, administrators and data directories as per your requirements.

Migration from Oracle to SQL Server

Step 10) Installation is in progress now.

Migration from Oracle to SQL Server

Step 11) Installation has been completed successfully.

Migration from Oracle to SQL Server

 

Installation of SQL Server Migration Assistant for Oracle: –

Now we need to install the (SSMA) SQL Server Migration Assistant for Oracle which helps in Migration from Oracle to SQL Server.

Step 1) when you have downloaded the SSMA, you got below two files. Now we need to install both the files one by one.

Migration from Oracle to SQL Server

Step 2) when you ran the first file then you will get below SSMA for Oracle setup wizard.

SSMA Oracle setup wizard

Step 3) Accept the License terms and click on Next.

Step 4) Choose setup types as Complete.

Migration from Oracle to SQL Server

Step 5) Click on the Install button to proceed further.

Step 6) First file has been successfully installed, now need to install extension file.

Step 7) when you ran second file then also you will get Oracle setup wizard, click on next to proceed further

OracleSetup Wizard—->Accept License Terms—-> Choose Complete setup—->Install

Step 8) after click on the Install tab you will able to see the below window.

Choose Installation type as Local Instance (Windows)

Installation type as Local Instance

Step 9) Choose database instance, I am using my old instance (SQLEXPRESS) which I was created earlier.

Choose database instance

Step 10) Enter Parameter Connection as per your requirement. You can choose either Window or Server Authentication.

Migration from Oracle to SQL Server

Step 11) Enter extension pack database master key password which is used at the time of server side migration.

Migration from Oracle to SQL Server

Step 12) Install the utilities databases for SSMA Oracle Extension Pack.

Migration from Oracle to SQL Server

Step 13) Now you can see the below Execution Process.

Migration from Oracle to SQL Server

Step 14) Installation has been completed.

Migration from Oracle to SQL Server

 

Connect to the Oracle Database: –

Now we need to connect Oracle database using SSMA, but before connecting make sure you have done the proper entries in tnsname.ora file.

Step 1) Open the SSMA tool.

Note: – For connection you first need to create the project. Click on the below left icon which has marked in red square.

Migration from Oracle to SQL Server

Step 2) Save your project and make sure you in Migrate To tab select that version which you have installed like in my case I have installed Microsoft SQL Server 2012 so I have selected the same version.

Migration from Oracle to SQL Server

Step 3) now Click on Connect to Oracle tab and please have a look below tnsnames entry of my Oracle database and Oracle Client.

Connect to Oracle

tnsnames.ora file entry of Oracle Database: –

Migration from Oracle to SQL Server

tnsnames.ora file entry of Oracle Client: –

Migration from Oracle to SQL Server

Step 4) Specify the parameter for connect to Oracle database.

Migration from Oracle to SQL Server

Connection has been established successfully and objects are loading, you can see the status at below right corner.

Connection has been established

Step 5) As we can see in below output we are able to see the schemas in Oracle under ORCL database.

Migration from Oracle to SQL Server

 

Connect to the SQL Server Instance:-

Step 1) now we will connect to the SQL server Instance, Click connect to SQL Server tab and specify the parameter for connection.

Migration from Oracle to SQL Server

Step 2) Connection has been successfully established and able to see databases inside SQLEXPRESS Instance.

Connection od SQL has been established successfully

 

Convert Schema from Oracle to SQL Server: –

Before Migrating Intern Schema first we need to create Intern user and then give below mandatory  permissions.

User Creation: –

SQL> CREATE USER intern IDENTIFIED BY “intern”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

ACCOUNT UNLOCK

User created.

Permissions: –

GRANT CONNECT to intern ;

GRANT CREATE ANY PROCEDURE to intern ;

GRANT EXECUTE ANY PROCEDURE to intern ;

GRANT SELECT ANY TABLE to intern ;

GRANT SELECT ANY SEQUENCE to intern ;

GRANT CREATE ANY TYPE to intern ;

GRANT CREATE ANY TRIGGER to intern ;

GRANT SELECT ANY DICTIONARY to intern ;

GRANT CREATE,CONNECT,RESOURCE,DBA to intern;

 

SQL> conn intern/[email protected]

Connected.

 

Now I will Migrate Intern Schema from Oracle to SQL, to perform Migration from Oracle to SQL Server first we need to Convert Schema from Oracle to SQl Server.

Right click on Intern Schema—>Convert Schema

Note: – This should be performing from Oracle Server side.

Migration from Oracle to SQL Server

 

 

 

Synchronize with the database: –

Note: – This should be performing from SQL Server side.

Right click on Intern Schema—>Synchronize with database

Migration from Oracle to SQL Server

Migration from Oracle to SQL Server: –

Step 1) Now Final step is the Migration from Oracle to SQL Server.

Note: – This should be performing from Oracle server side.

Right click on Intern Schema—->Migrate Data

When you click on Migrate Data option then you need to put credentials of Oracle and SQL server.

Migration from Oracle to SQL Server

Migration from Oracle to SQL Server

After Migration you will get success report.

report

Migration from Oracle to SQL Server

Step 2) now connect to the SQL Server Management studio and check whether Intern table has been migrated successfully.

Migration from Oracle to SQL Server

Step 3) Migration from Oracle to SQL Server has been completed successfully.

Migration from Oracle to SQL Server

Hurray!!

Hope this blog will help you!!

If this article is useful for you please comment, share with your friends.

In case of any issues please send your query in below comment section.

For more articles please do visit below links: –

Invoke Linux GUI On windows using Xming software

Oracle Enterprise Linux 8.1 Installation

Oracle Virtual Box Download

Advertisement

Karan

Hi, Myself Karan Rajpoot, I am an Oracle DBA and Professional Blogger. I have created this blog to share all the useful content related to the Technology world.

Leave a Reply