How to Install SQL Server Migration Assistant for Oracle – Part 1

By:   |   Updated: 2018-06-13   |   Comments (3)   |   Related: > SQL Server and Oracle Comparison


Problem

These days many organizations are moving their applications from Oracle to SQL Server to reduce their TCO. SQL Server is a very mature database system that can run any kind of enterprise applications. To help with the migration, Microsoft has developed the SQL Server Migration Assistance for Oracle to migrate databases from Oracle to SQL Server. In this tip I am going to describe how to install SQL Server Migration Assistance for Oracle.

Solution

Heterogenous migration of databases is not an easy task. There are lot of things we need to take care of during the migration like: object to object mapping, data type conversions, data loading, etc. To overcome these complexities Microsoft has designed an awesome tool called SQL Server Migration Assistant. This tool takes care of most of the complex things and you just need to review and verify the settings.

SQL Server Migration Assistant for Oracle is used to perform a migration from Oracle to SQL Server and Azure SQL DB. SSMA for Oracle also has an additional extension pack that supports data migration and the use of Oracle system functions in your migrated databases.

Prerequisites

Before installing SQL Server Migration Assistant for Oracle, you need to go through the below points.

  • We should install SSMA for Oracle on a server that will host a migrated database on SQL Server.
  • We should also first install SQL Server instance before installing SSMA for Oracle client.
  • SSMA for Oracle does not support SQL Server Express edition.
  • We must use sysadmin account to install SQL Server Migration Assistance for Oracle.
  • You need to install Oracle client software to support this migration.  You can install either Oracle Client Provider or the OLE DB provider for Oracle, and connectivity to the Oracle database that you want to migrate. You can install providers from the Oracle product media or Oracle Web site.
  • The SQL Server Browser service must be running during installation. This is used to populate a list of the instances of SQL Server in the setup wizard. You can disable the SQL Server Browser service after installation.
  • Make sure that you have proper connectivity between the source Oracle system to the machine where you are installing SSMA for Oracle. Although, it will not require it during installation, but once SSMA for Oracle will be installed we need to connect to the Oracle system to migrate the data.

NOTE: BE SURE TO TEST THIS SOLUTION IN A LOWER LIFE CYCLE FIRST. DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER LIFE CYCLE ENVIRNOMENTS.

Installing SQL Server Migration Assistant for Oracle

Step 1: Download SQL Server Migration Assistant

To install SQL Server Migration Assistant, we need to first download the installation package from the Microsoft website. Open this link and download the packages. Two packages will be downloaded as shown below.

  • SSMA for Oracle.7.3.0
  • SSMA for Oracle Extension Pack.7.3.0

SSMA for Oracle is a mandatory package to perform any migration from Oracle to SQL Server although the second package SSMA for Oracle Extension package is an additional package that is required to support data migration and Oracle providers to enable server-to-server connectivity. Here, I will display how to install SSMA for Oracle. I will explain the extension pack installation in next article.

Step 2: Launch SQL Server Migration Assistant for Oracle

Once SSMA for Oracle packages will be downloaded on your machine then you should first launch SSMA for Oracl.7.3.0 package to install its client application. You can see both packages have been downloaded on my machine in the below image.

ssma for oracle installer

Step 3: Begin Installation

Right click on the “SSMA for Oracle.7.3.0” package and click Run as Administrator to launch SQL Server Migration Assistance installation window. Once you click at Run as Administrator, you will get the below option to proceed with the installation. Click Run to launch the installation window.

ssma for oracle installer

Step 4: SSMA Oracle Wizard

Once you click Run, you will get the below SSMA for Oracle setup window. This is a welcome page to install SSMA for Oracle. Click Next to proceed.

ssma for oracle installer

Step 5: Required Oracle Client

Once you will click Next, you will get the below window if you have not installed the Oracle client software on your system where you are installing SSMA for Oracle. You can install the Oracle client later as well, but without the Oracle client software you will not be able to install the extension pack, so I suggest installing before starting with the SQL Server Migration Assistant. You can download the required client software by clicking on the Downloadoad button as shown below.

ssma for oracle installer

Step 6: Licensing Terms

Once you have installed the Oracle client, the next screen will ask you to accept the license terms. If you decide to install the Oracle client later, you will get the same window as shown below. Accept the license terms and agreement and click Next to proceed with the installation.on.

ssma for oracle installer

Step 7: SSMA for Oracle Setup Type

Once you click Next, you will get the below window to choose the setup type. I chose the Complete option, so I won’t need to install it again for any missing components.ts.

Once you choose your option, the Next button will be enabled to proceed. Click Next to proceed.

ssma for oracle installer

Step 8: Ready to Install

Now you are at final window of this installation. If you want any changes, you can go back by clicking the Back button. If you are done with everything then click Install to start the installation of SQL Server Migration Assistance for Oracle.le.

ssma for oracle installer

Step 9: SSMA for Oracle Installation Complete

Once SSMA for Oracle is installed, you will get below window as a confirmation. Click Finish to close this window.ow.

ssma for oracle installer

Once you will click Finish, you will see new icons on your desktop. Now the SSMA for Oracle installation is complete.

Next Steps
  • We will install SQL Server Migration Assistance for Oracle Extension Pack as part of the SSMA installation. Installation of SSMA for Oracle Extension pack is a little lengthier than this, that is why I have decided to write it as another tip. Keep an eye on this website for the second part of this tip.
  • Check out the SQL Server Database Administration Tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-06-13

Comments For This Article




Friday, August 18, 2023 - 3:05:16 AM - Ravi Shanker Back To Top (91495)
Very useful tips.

Tuesday, September 21, 2021 - 9:29:23 PM - Manu Mohan Back To Top (89249)
Hello,

I am having a question. Can we use an RDS instance to migrate data? For example from an Oracle RDS on-premise database to an RDS SQL server database instance? Also, How good is SSMA in handling huge databases?

Monday, August 12, 2019 - 9:49:40 AM - Dimitris Stathoulis Back To Top (82037)

hi. I followed your steps and managed to connect to the Oracle database, everything is fine. I haven't pressed the convert schema button. I cannot find anywhere if the migration affects the original oracle database which is up and running and i don't want to ruin something. Can you please inform me if database migration with sssma is safe without affecting the original database?

Dimitris














get free sql tips
agree to terms