Problem
Organizations are moving their applications from Oracle to SQL Server to reduce their TCO and Microsoft has developed a tool called the SQL Server Migration Assistant for Oracle to migrate databases from Oracle to SQL Server. I demonstrated how to install SSMA for Oracle in my last tip and this tip I will show how to install the SQL Server Migration Assistant for Oracle Extension Pack that is also needed during migration.
Solution
SQL Server Migration Assistant for Oracle is used to perform a migration from Oracle to SQL Server or 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. Let’s start the SSMA for Oracle Extension Pack installation in this article.
Prerequisites
Before installing the SQL Server Migration Assistant for Oracle Extension Pack, you need to do the following:
- Make sure all prerequisites mentioned in my last tip are set.
- Make sure you have already installed the SQL Server Migration Assistant for Oracle.
NOTE: BE SURE TO TEST THIS SOLUTION IN LOWER LIFE CYCLE FIRST. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER LIFE CYCLE ENVIRNOMENTS.
Installing SQL Server Migration Assistant for Oracle Extension Pack
Step 1:
To install SQL Server Migration Assistant, we need to first download its 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.
Step 2:
Once the SSMA for Oracle packages are downloaded you should first install SSMA for Oracle.7.3.0 package. Once this package will be installed our next step will be to install SSMA for Oracle Extension Pack.7.3.0. Here, I will display how to install SSMA for Oracle Extension Pack because we have already installed SSMA for Oracle in our last tip.
Step 3:
Right click at “SSMA for Oracle Extension Pack.7.3.0” package and click on Run as Administrator to launch. Once launched you will get the below option to proceed with the installation. Click “Run” to start he installation.
Step 4:
Once you click Run, you will get the below SSMA for Oracle Extension Pack setup window. Click Next to proceed.
Step 5:
Once you will click Next, you will get the below window to accept the license terms. Accept the ;license terms and click Next.
Step 6:
Now you will get three setup types in the next window. You must choose one to proceed with this installation. I have chosen “Typical” and clicked Next to install the extension pack.
Step 7:
Once you will click Next, you will get the below screen to proceed. Click Install to start the installation.
Step 8:
When you get the below screen, it means you have installed the SSMA for Oracle Extension Pack. Now setup will guide us to configure the extension pack, so click Next.
Step 9:
Once you click Next, you will get the below window to select the database instance on which you want to migrate your database from Oracle to SQL Server. Then click Next.
Step 10:
Now you will get another window to enter connection parameters to establish a connection with the SQL Server instance that you entered in the above screen.
Step 11:
I have selected Windows authentication to connect to the database instance. Click Next to proceed. Now, setup will ask you to enter a password to protect the extension pack master key. The master key protects the password stored in the extension pack database. Enter a password and proceed by clicking Next.
Step 12:
Now, setup will ask you to install system databases that are required for this migration. Select the “Install Utilities Database v7.3.3602 and Install Extension Pack libraries” and click Next to proceed.
Step 13:
The execution will start to setup the extension pack and will end with the below image. We can see the installation completed successfully in the below screenshot. You can click No to proceed or Yes if you want to install to another instance.
Step 14:
The tools is now installed and you are ready for the Oracle to SQL Server Migration using the SSMA for Oracle tool. You can see Microsoft SQL Server Migration Assistant for Oracle has been installed and there is shortcut created on your desktop.
Step 15:
To validate the installation, you can launch SQL Server Management Studio and connect to the database instance on which you want to migrate the database. Here, you will see two system databases that were created during installation of SSMA for Oracle Extension Pack. You can also see that some of database objects are also shown inside these system databases. The name of these databases will be ssmatesterdb and sysdb as shown in the below image.
Step 16:
You can expand the database to see the Oracle to SQL Server migration related objects.
Next Steps
- Now you have installed SQL Server Migration Assistant for Oracle and its extension pack on your database server where you want to migrate Oracle to SQL Server.
- Perform a rehearsal migration from a lower life cycle system first and then repeat for your production database.
- Explore these other SQL Server Database Administration Tips.
