Oracle to SQL Server Migration Convert Database Objects

By: Manvendra Singh | Comments | Related: 1 | 2 | 3 | 4 | > SQL Server and Oracle Comparison

Problem

In the first part of this series, I explained how to start migrating an Oracle database to SQL Server. In this section, we cover the conversion phase from an Oracle to SQL Server database and how to convert relevant objects.

Solution

Microsoft has developed a tool called SQL Server Migration Assistant (SSMA) for Oracle to help migrating Oracle databases to SQL Server. This tool can be used to migrate databases to SQL Server as well as Azure SQL databases in the cloud. For this example, I am using Oracle 10G and SQL Server 2014.

Below is the high level approach for this migration:

First you need to install SQL Server on target server where your migrated database will be hosted. Ensure you have proper connection and accessibility between the source Oracle server and target SQL Server system
Next you need to install the Oracle Client and SQL Server Migration Assistance (SSMA) on that server
Launch SSMA and connect to the source Oracle database then make sure to check the connectivity due to port or firewall issues
Connect to the target SQL Server that you installed in step 1
Map the Oracle schema which you want to Migrate to the SQL Server database
Convert Oracle objects to SQL Server
Load converted objects to SQL Server
Finally, migrate the loaded data to the target database

For related articles for Steps 1 - 5 see links below:

In this article we will cover Step 6.

Review Conversion Report

We already performed a mapping between source Oracle and target SQL Server databases and then we generated a conversion report to evaluate any issues or no convertible objects in our last article. If you do not find any issue in the assessment report then you can go ahead and start the conversion, but there is still a possibility to have some of the conversion problems. I would like to explain a little about dealing with conversion problems.

The Conversion Statistics pane shows the conversion statistics. If the percentage for any category is less than 100 percent, you should determine why the conversion was not successful.

To view conversion problems, expand schemas or folders that have a red error icon on the left side pane. Continue expanding items until you select an individual item that failed conversion. Review any error messages, and then determine what you want to do with the object that caused the conversion problem.

If all statistics show 100% then the next step is to perform the conversion.

Conversion Principle

Converting database objects takes the object definitions from Oracle, converts them to similar SQL Server objects, and then loads this information into the SSMA metadata. It does not load the information into the instance of SQL Server. You can then view the objects and their properties by using the SQL Server Metadata Explorer.

I have copied the below table from MSDN to let you understand how SSMA will convert Oracle database objects to similar or identical database objects in SQL Server. The following table shows which Oracle objects are converted, and the resulting SQL Server objects:

Oracle Objects	SQL Server Objects
Functions	If the function can be directly converted to Transact-SQL, SSMA creates a function. In some cases, the function must be converted to a stored procedure. In this case, SSMA creates a stored procedure and a function that calls the stored procedure.
Procedures	If the procedure can be directly converted to Transact-SQL, SSMA creates a stored procedure. In some cases, a stored procedure must be called in an autonomous transaction. In this case, SSMA creates two stored procedures: one that implements the procedure, and another that is used for calling the implementing stored procedure.
Packages	SSMA creates a set of stored procedures and functions that are unified by similar object names.
Sequences	SSMA creates sequence objects (SQL Server 2012 or SQL Server 2014) or emulates Oracle sequences.
Tables with dependent objects such as indexes and triggers	SSMA creates tables with dependent objects.
View with dependent objects, such as triggers	SSMA creates views with dependent objects.
Materialized Views	SSMA creates indexed views on SQL server with some exceptions. Conversion will fail if the materialized view includes one or more of the following constructs: - User-defined function - Non deterministic field / function / expression in SELECT, WHERE or GROUP BY clauses - Usage of Float column in SELECT*, WHERE or GROUP BY clauses (special case of previous issue) - Custom data type (incl. nested tables) - COUNT(distinct <field>) - FETCH - OUTER joins (LEFT, RIGHT, or FULL) - Subquery, other view - OVER, RANK, LEAD, LOG - MIN, MAX - UNION, MINUS, INTERSECT - HAVING
Trigger	SSMA creates triggers based on the following rules: - BEFORE triggers are converted to INSTEAD OF triggers. - AFTER triggers are converted to AFTER triggers. - INSTEAD OF triggers are converted to INSTEAD OF triggers. Multiple INSTEAD OF triggers defined on the same operation are combined into one trigger. - Row-level triggers are emulated using cursors. - Cascading triggers are converted into multiple individual triggers.
Synonyms	Synonyms are created for the following object types: - Tables and object tables - Views and object views - Stored procedures - Functions - Synonyms for the following objects are resolved and replaced by direct object references: - Sequences - Packages - Java class schema objects - User-defined object types - Synonyms for another synonym cannot be migrated and will be marked as errors. - Synonyms are not created for Materialized views.
User Defined Types	SSMA does not provide support for conversion of user defined types. User Defined Types, including its usage in PL/SQL programs are marked with special conversion errors guided by the following rules: - Table column of a user defined type is converted to VARCHAR(8000). - Argument of user defined type to a stored procedure or function is converted to VARCHAR(8000). - Variable of user defined type in PL/SQL block is converted to VARCHAR(8000). - Object Table is converted to a Standard table. - Object view is converted to a Standard view.

Step 6 - Convert Oracle Database Schema to SQL Server Schema

As mentioned earlier, step 1 to step 5 are out of scope of this article, so we are starting from step 6 that is about converting schemas from Oracle to SQL Server using SQL Server Migration Assistant for Oracle.

Right click your source schema in the Oracle Metadata Explorer (top left pane) and click “Convert Schema” option as shown in the below image. You can see all database objects have been selected in the below screenshot.

If you want to convert a database table or object, then you can do it as well by deselecting unwanted database objects and only choose the one you want to migrate. You can right click the object and then click the Convert Schema option.

The conversion process will start after clicking “Convert Schema”. See the black rectangles below which says “Converting table TF10.BTXDSET…” along with a green progress bar displaying 33% completion.

Once conversion completes, you can see in the output window in the bottom section of the SSMA for Oracle tool. If there are any warnings or errors you will see them here. As there are no errors or warnings for this conversion, we can say our conversion process has completed successfully.

If you look at the SQL Server Metadata Explorer in the above image, you will notice two new schemas named “ssms_oracle” and “TF10” whereas these schemas were not there before starting the conversion process.

Have a look at the screenshot below that was taken from SQL Server Management Studio for database TF10.

If you expand these two schemas, you can see a lot objects have been created.

You can see all source tables have been converted and are shown in the SQL Server Metadata Explorer under the TF10 schema in the below image. This was the conversion process. You might face issues if you have any conversion problem during this phase, so once complete you should save.

Ensure to save your work to keep for future access.

Next Steps

We have converted all source objects from Oracle to SQL Server objects. The next step is to perform the load phase for all these converted objects. Stay tuned for my next article to learn how to load these converted objects to the target database.

About the author

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

How to Convert Database Objects from Oracle to SQL Server using SQL Server Migration Assistant for Oracle – Part 2