Oracle Schema to SQL Server Database Migration - Physical Design

An Oracle instance can run only one database, and schemas are widely used to logically group database objects in Oracle.

A Microsoft SQL Server instance can run multiple databases, and although SQL Server also supports schemas, an Oracle schema is usually migrated to a SQL Server database, not to a schema.

Oracle Schema to SQL Server Database Migration:

Benefits of Migrating Schemas to Databases

In Oracle, each schema usually contains logically independent objects, often used by different applications, so their migration to separate databases in SQL Server gives the following benefits:

  • Flexibility to back up and restore of a databases independently from other databases
  • Independent performance tuning and optimization

Disadvantages of Migrating Schemas to Databases

This approach also has a disadvantage: if there are cross-schema object references, you have to modify your queries from schema.object to database..object syntax:

Oracle:

   -- Reference a table in schema SALES in Oracle
   SELECT * FROM sales.products;

SQL Server:

   -- Reference a table in database SALES in SQL Server
   SELECT * FROM sales..products;

Physical Design - Oracle Schema and SQL Server Database

In Oracle, CREATE USER statement in used to create a database user and associated schema. Optionally, schema data can be stored in a separate tablespace.

Oracle:

   -- Firstly create a tablespace to store the user data
   CREATE TABLESPACE sales_data DATAFILE 'sales_data.dbf' SIZE 1M AUTOEXTEND ON;  
 
   -- Create a user and assign the default tablespace
   CREATE USER sales IDENTIFIED BY password
      DEFAULT TABLESPACE sales_data
      QUOTA UNLIMITED ON sales_data;

When a table is created in the sales schema, it is stored in the sales_data tablespace by default.

Oracle:

   -- Table is stored in sales_data tablespace by default
   CREATE TABLE sales.regions
   (
       id NUMBER(10),
       name VARCHAR2(90)
   );

You may also have a separate tablespace to store indexes and LOB data.

Oracle:

   -- Create a tablespace to store indexes
   CREATE TABLESPACE sales_idx DATAFILE 'sales_idx.dbf' SIZE 1M AUTOEXTEND ON;  
 
   -- Allow SALES user to store data in SALES_IDX tablespace
   ALTER USER sales QUOTA UNLIMITED on sales_idx;
 
   -- Create an index and store it in sales_idx tablespace
   CREATE UNIQUE INDEX sales.regions_id_idx ON sales.regions (id) TABLESPACE sales_idx;

Filegroup in SQL Server

In SQL Server, you can use CREATE DATABASE statement to create a database and assign PRIMARY filegroup to store the database data.

A filegroup in SQL Server is similar to tablespaces in Oracle, it is a logical storage for table and index data that can contain one or multiple OS files.

SQL Server:

    -- Create database and define the default PRIMARY filegroup to store the data
   CREATE DATABASE sales
      ON PRIMARY
        (NAME = sales_data, FILENAME = 'C:\MSSQLData\sales_data.ndf', SIZE = 3MB);

When a table is created in the sales database, it is stored in the PRIMARY filegroup (sales_data) by default.

SQL Server:

   -- Table is stored in PRIMARY filegroup by default
   CREATE TABLE sales..regions
   (
       id INT,
       name VARCHAR(90)
   );

In SQL Server, you may also have a separate filegroup to store indexes and LOB data.

SQL Server:

   -- Add a filegroup to the database
   ALTER DATABASE sales ADD FILEGROUP sales_idx;  
 
   -- Add a OS file to the filegroup
   ALTER DATABASE sales ADD FILE (NAME = sales_idx, FILENAME = 'C:\MSSQLData\sales_idx.mdf', SIZE = 3MB)
      TO FILEGROUP sales_idx;
 
   -- Create an index and store it in sales_idx filegroup
   CREATE UNIQUE INDEX regions_id_idx ON sales..regions (id) ON sales_idx;

Resources

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012

You could leave a comment if you were logged in.