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.
The problem
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.
Possible risks
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:
1 | [DB_NAME].[TEST].[CUSTOMER] |
You will need to replace it with the following structure:
1 | [TEST].[dbo].[CUSTOMER] |
The image below clearly illustrates the difference between Oracle and SQL Server approaches to database schemas.
The solution
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.
