Today, I am going to discuss about Oracle to SQL Server Database Migration. This article will give you step by step method to install Microsoft SQL Server Migration Assistant for Oracle. We also call it SSMA for Oracle. Microsoft has designed this tool to automate data migration from Oracle to SQL Server or Azure SQL DB. SSMA for Oracle is very advanced tool that converts Oracle database schemas to SQL Server Schemas and then migrate the data from Oracle to SQL Server database.
The very first step to start the data migration is to setup and install Microsoft SQL Server Migration Assistant for Oracle on the system where you want to host your migrated database. Here, I will give you step by step process to proceed with the installation. First, we should check all the prerequisites for SSMA for Oracle.
Prerequisites
Make sure to validate all steps mentioned below before going to install Microsoft SQL Server Migration Assistant for Oracle.
- Make sure you have already installed SQL Server instance that will host the migrated database. Also keep in mind that you are not installing SQL Server Express edition to host the migrated database.
- You must have sysadmin account to install SQL Server Migration Assistant for Oracle.
- Make sure to install SSMA for Oracle on the server that will host newly migrated database on SQL Server.
- It is recommended to install Oracle client software on your target system where SQL Server Instance is running.
- Make sure your windows server has Microsoft Windows Installer 3.1 or a later version.
- Port 1434 should be open.
Step by Step Process to Install SQL Server Migration Assistant for Oracle
There are three steps you should go through if you want to install Oracle to SQL Server Migration Tool SSMA for Oracle.
- Oracle to SQL Server Migration Tool Download
- Install SSMA for Oracle
- Install SSMA for Oracle Extension Pack
Let’s go one by one and complete the installation of Microsoft SQL Server Migration Assistant for Oracle tool.
Oracle to SQL Server Migration Tool Download
First step is Oracle to SQL Server migration tool download. We will first download its installation package from Microsoft website attached in the link. As of now latest version of Microsoft SQL Server Migration Assistant for Oracle is v7.8. Download the required packages from attached link. Microsoft SQL Server Migration Assistant for Oracle contains three packages as shown in below screenshot.
First two packages are same; one for 64 bit system and another one for 32 bit system. Third one is SSMA for Oracle extension pack. We need to download two packages (any one from first two based on your system architecture and one extension pack) for Oracle to SQL migration. The name of packages that i have downloaded are given below.
- SSMAforOracle_7.8.0.msi
- SSMAforOracleExtensionPack_7.8.0.msi
You can see both packages have been downloaded on my machine in below screenshot.
First package SSMAforOracle is mandatory to install for oracle to SQL migration. We will be installing both packages in sequence one after another. You cannot install extension pack if you have not installed SSMA for Oracle base package. Here, I will show you how to install Microsoft SQL Server Migration Assistant for Oracle and SSMA for Oracle Extension Pack.
Install Microsoft SQL Server Migration Assistant for Oracle
As we have downloaded both required packages for SSMA for Oracle in above section. Now we will start the installation. Right click on first package “SSMAforOracle_7.8.0” and click at “Run as Administrator” to launch the installation window. Installation of this package is very easy and smooth whereas installation of second package requires some configurational parameters that I will describe in next section of this article.
You will get below welcome page window to start the SSMA for Oracle installation. Click on Next button to go to next window.
Next window will ask you to validate whether you have installed Oracle client software or not as I have described in prerequisite section. You can also install it later post this installation. I would suggest you to first install Oracle client before going with this installation. You can also download the Oracle client package from this window by clicking at Download button. Click Next to go to next window.
Here, accept the License agreement and click on Next button to proceed.
Here, we need to choose what type of setup we should install for SSMA for Oracle. I have chosen first option that is “Typical”.
When you click at Typical option to select it, the setup will move you to final window of this setup. Here, click at “Install” button to finish the SSMA for oracle installation.
Once Microsoft SQL Server Migration Assistant for Oracle will be installed, you will get below image. Click on Finish button to close this window.
Now SSMA for Oracle is installed, next we will configure Microsoft SQL Server Migration Assistant for Oracle Extension Pack to support server to server migration from Oracle to SQL Server migration.
Install SSMA for Oracle Extension Pack
Microsoft SQL Server Migration Assistant for Oracle Extension Pack creates two databases sysdb and ssmatesterdb on the target SQL Server Instance. The database sysdb contains the tables and stored procedures that are required to migrate data, and the user-defined functions that emulate Oracle system functions. The ssmatesterdb database contains the tables and procedures that are required by the Tester component.
Also, when you migrate data to SQL Server, SSMA creates SQL Server Agent jobs when server side data migration engine is used for migrating the data. Now let’s go ahead and start the installation of SSMA extension pack.
Installation of SSMA extension pack has two steps. First step is very much like the installation of first package that we did in above section so I am not going to attach the screenshots for this step because all are similar to the installation of first package. Second step of this installation is to configuration part where installation will install few SQL scripts that will support this migration. I will show the screenshots for this configuration part post installation of extension pack. Both Steps will come during running extension pack installation package.
Right click on second package SSMAforOracleExtensionPack_7.8.0 and click at “Run as Administration”. Again, you will get welcome page of installation.
Click Next button to proceed. If you have not installed Oracle client yet then you will get to notify oracle client is missing and you should install it before getting license agreement window. If you have already installed Oracle client then you will get next window to accept the license agreement. Accept the License agreement and click on Next button to proceed.
Now you will get next window to choose setup type. I have chosen Typical as I did in above section.
Once you click at typical option it will ask you to install the extension pack. Click on Install button to start the installation.
Once installation will be done you will get below window to configure the SSMA for Oracle tool. Now click on Next button to configure and install required SQL scripts.
Here, you need to enter the SQL Server Instance name on which you want to host your migrated database. Remember, SSMA system databases sysdb and ssmatesterdb both databases will be created on this instance only.
Click Next button to enter connection parameters post entering the SQL Server instance name.
Enter Connection details that you are using to connect to database instance in above screenshot. Click Next button now.
Enter password to protect the passwords stored in databases sysdb and ssmatesterdb. Again, click next now.
Here, setup will ask you to create the system databases sysdb and ssmatesterdb on your given database instance. Choose options accordingly on below page and click on next button.
A processing will happen just like below image once you click at next button.
Once processing will be completed, SSMA for oracle extension pack will be installed. And a icon will be created on your server desktop.
You can also validate it by connecting to database instance that you have given during configuration of extension pack. You will see both system databases sysdb and ssmatesterdb will be created there for this migration.
Once both packages will be installed, make sure to run below command to enable clr.
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
If CLR is not enabled, you will receive the following error when SSMA connects to SQL Server:
SSMA could not retrieve the extension pack assembly version information. Reinstall the extension pack on the database server.
You will see many database objects created on system db sysdb. Such objects are ssma_oracle.bcp_migration_packages, ssma_oracle.db_storage, and ssma_oracle.db_error_list table along with many stored procedures and user-defined functions in the ssma_oracle schema.
Every time that you migrate data to SQL Server, SSMA creates a SQL Server Agent job. These jobs are named ssma_oracle data migration package {GUID}, and are visible in the SQL Server Agent node of SQL Server Management Studio in the Jobs folder.
Here we have installed Microsoft SQL Server Migration Assistant for Oracle and its Extension pack to migrate a database from Oracle to SQL Server. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
Read More:
Leave a Comment