When migrating Oracle databases to Microsoft SQL Server, the first problem you will face is mapping Oracle schema to SQL Server. You can hardly underestimate the importance of this architecture-level question because the wrong approach will lead to significant efforts in rewriting the code.
In Oracle, the database server contains an Oracle database and an Oracle instance. At the same time, every Oracle database contains schemas. By the way, you can learn more about the Oracle architecture here. Basically, a database is a physical component. Oracle does not use the database name as a part of the object’s name.
Microsoft SQL Server databases are organized in a different way. SQL Server instance contains multiple databases, and each database contains several schemas. A SQL Server database is both a physical and a logical component. SQL Server uses the database name in the name of the object.
So, what happens if you don’t define correct mapping before starting the conversion of the database code? Well, you will need to rewrite most of your code, and the changes will affect the logic. Here’s what I mean. By default, you will have the following SQL code:
You will need to replace it with the following structure:
The image below clearly illustrates the difference between Oracle and SQL Server approaches to database schemas.
You can use 2 following approaches to schema mapping when migrating from Oracle to SQL Server:
- Schema to database
In this case, Oracle [TEST] schema converts to a default SQL Server [dbo] schema in the [TEST] database.
- Schema to schema
In this case, Oracle [TEST] schema converts to SQL Server [TEST] schema in the [database_name] database.
Finally, be sure to define a naming convention at the very beginning of your migration project as this is also an architecture-level decision. In addition to that, we suggest taking care of mapping Oracle data types to SQL Server. Also, be sure to check our best practices related to converting Oracle sequences to SQL Server.