Schema mapping in migration from Oracle to SQL Server

The migration of Oracle databases to Microsoft SQL Server brings many challenges. One of the most significant is associated with mapping Oracle schema to SQL Server. At the architecture level, the wrong approach will result in significant effort in the future. For example, you may even need to rewrite your app’s entire code from scratch.

The problem

Oracle database server includes an Oracle database and an Oracle instance. You may learn more about the architecture of the Oracle server here. In Oracle, a database is a physical component without a name defined as a part of the object’s name. Meanwhile, a database contains schemas as its principal components.

Contrary to Oracle, the Microsoft SQL Server instance contains multiple databases, each including several schemas. Another valuable distinction between Oracle and Microsoft SQL is that an SQL Server database has both a logical and a physical component. Also different from Oracle, SQL Server uses the database name in the object’s name. Such differences may lead to significant inconsistencies pertaining to database schema mapping from Oracle to SQL Server.

Possible risks

You define correct mapping before you start the conversion of the database code. This will prevent you from rewriting most of your code. And apart from being time-consuming, this process may also negatively affect your software logic.

Let’s illustrate this with a practical example. By default, your SQL code looks as follows:

You should replace it with the following structure:

As you see, the distinctions are principal, so you will need to apply relevant schema mapping approaches.

The solution

There are 2 basic approaches to schema mapping that will help with migrating from Oracle to SQL Server.

Schema to database

This approach requires you to convert Oracle [TEST] schema to a default SQL Server [dbo] schema in the [TEST] database.

Here are the most significant benefits of this solution:

• Additional flexibility because you can back up and restore a database independently from other databases.
• Independent performance tuning and optimization.

Schema to schema

In this case, you convert Oracle [TEST] schema to SQL Server [TEST] schema in the [database_name] database.
The most significant benefits of this approach are:

• The customizability of schema conversion is provided by SSMA.
• An option to simply revert to the default database and schema.

Final thoughts

Successful schema mapping is essential for your Oracle to SQL Server migration. Meanwhile, you should never forget the value of defining a naming convention, mapping Oracle data types to SQL Server, and converting Oracle sequences to SQL Server. Approach these architecture-level decisions with extreme caution and contact us if you have any questions or need help with these processes.