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.
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.
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.
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.
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.
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.
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.
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.
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.
