1. Migrate a Microsoft SQL Server to Oracle Cloud
  2. Configure the Users and Environment

Configure the Users and Environment

The Migrations Repository is a database schema that stores the metadata collected and transformed in the migration process.

Create a User for the Migration

Create a user for the migration.

  1. Log into the Autonomous Database as an admin user.
  2. Create a migration repository user for the migration metadata and grant the user unlimited space quota for metadata.
    This example uses the user mwrep. 
    SQL> CREATE USER MWREP 
IDENTIFIED BY Welcome112233 
DEFAULT TABLESPACE DATA
TEMPORARY TABLESPACE TEMP;
ALTER USER MWREP QUOTA UNLIMITED on DATA;
  3. Grant permissions to the mwrep user.
    SQL> GRANT 
    CONNECT, 
    RESOURCE, 
    CREATE SESSION, 
    CREATE VIEW, 
    CREATE MATERIALIZED VIEW 
    TO mwrep;

Create the Migration Repository

To convert the Microsoft SQL Server database to Oracle, create a repository to store the required repository tables and PL/SQL packages and create an SQL Developer connection to the mwrep user.

  1. In the Oracle Cloud Console, click the Connections tab, right-click Connections, then select New Connection.
    A New / Select Database Connection window will appear.
  2. Complete the following fields for the connection in the New / Select Database Connection window
    1. Name: Enter mwrep_adb (or the name that identifies your connection).
    2. Database Type: Select Oracle.
    3. Authentication Type: Select Default.
    4. Username: Enter mwrep.
    5. Password: Enter the password, then select Save Password.
    6. Connection Type : Select Cloud Wallet from the drop down menu.
    7. Configuration File: Browse to where the autonomous wallet zip file is saved and select the wallet_adbName.zip file.
      When the zip file is entered, the default database service is automatically populated.
  3. Click Test to test the connection.
    Verify that the status of the connection on the lower left side of the page shows Status: Success.
  4. Click Save to save the connection, then close the window.
    The connection is saved and it is listed under Connections in the Connections tab.
  5. Verify that you have the following connections listed in the Connections tab.
    • admin_adb
    • mwrep_adb
    • SQL Server 2016
  6. Right-click the mwrep_adb connection, select Migration Repository, then select Associate Migration Repository.
  7. Click OK.

Capture a Snapshot of the Microsoft SQL Server Database

Use the SQL Developer Migration Wizard to capture a “snapshot” that includes the Microsoft SQL Server database metadata and data.

In this example, the source SQL Server is deployed with two sample databases: AdventureWorks (OLTP DB) and AdventureWorksDW (Oracle Autonomous Data Warehouse). You can download both of these sample databases from the Microsoft SQL Server Github repo.

As part of the offline migration, it is recommended to quiesce all applications connected to MS SQL Server or switch SQL Server Database to ReadOnly mode in order to capture a consistent state of database.

  1. Introduction: Select Tools , Migration , then Migrate.
    The migration wizard will walk you through the steps to convert SQL Server databases to Oracle.
  2. Repository: Select a connection for the migration repository. Select the mwrep_adb connection, then click Next.
  3. Project: Provide a Name for the project and specify the Output Directory (typically wkdir where mssqlservermigration.zip was unzipped). Click Next.
    A Project is a container for the migration entities.
  4. Source Database: Provide the Source Database details and accept all defaults. By default, the Mode is Online and the Connection is SQL 2016. Click Next.
  5. Capture: Select the AdventureWorks database for definition capture, then click Next.
  6. Convert: Specify the conversion options. You can add a new rule. Click Next.
  7. Translate: Select SQL Objects to translate. The tool automatically selects sql objects. Click Next.
  8. Target Database: Provides the Target Database details. For the Mode, select Offline. You can select to drop the target objects here. Save the Generated Script Directory location, typically wkdir\ProjectName\generated\YYYY-MM-DD_HH-MM-SS format. Click Next.
  9. Move Data: Provides details about the Data Move. The Mode should be Offline. Save the location of the Data Move Script Directory, typically wkdir\ProjectName\datamove\YYYY-MM-DD_HH-MM-SS format. Click Next.
  10. Summary: Provides a summary of the entire Project. Save the details. To make modifications, click Back. Click Finish.
    The Database Migration Status box appears.

When the migration is completed and the master.sql file loaded up in SQL Developer, a pop-up message will appear: Migration actions have successfully completed. The objects and data are loaded and migrated to the autonomous database through SQL Developer.

Create the Oracle Database Objects

Oracle SQL Developer Migration Workbench generates DDL statements to create the Oracle Autonomous Database objects based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle Autonomous Database. You can use the SQL Developer or the sqlplus command-line to execute the sql scripts to create the SQL Server converted objects on Oracle Autonomous Database.
  1. Review the “Generated Script Directory” for the desired outputs.
    1. Use Windows File Explorer, Windows command, or Windows powershell to navigate to the Generated Script Directory.
      Typically, it's located in the wkdir directory that was defined during the install of the mssqlservermigration.zip file.
    2. Expand the wkdir directory tree.
      The directory should look similar to the following:
      Wkdir
	[projectName]
		datamove
		generated
			YYYY-MM-DD_HH-MI-SS
    3. Open the date and time stamped directory, you should see following oracle sql scripts:
      dropallschemas.sql
master.sql
passworddefinition.sql
reportallstatus.sql
  2. Create the SQL Server converted objects on Oracle Autonomous Database by executing master.sql script either through sql developer or straight sqlplus command-line interface.
    • To use SQL Developer to create the SQL Server converted objects on Oracle Autonomous Database, execute the master.sql scripts:
      1. Select admin_adb from the drop-down on the right icon. Then click Run Script (F5).
      2. (Optional) Run following SQL query to view the new Oracle Schemas created as a result of conversion process. 
        SQL> select * from dba_users where created > sysdate - 1;
    • To use the sqlplus command-line to create the SQL Server converted objects on Oracle Autonomous Database:
      1. Execute master.sql to create the metadata and schema structure.
        $ sqlplus admin/[password]@atpdb_tp @master.sql
      2. Create a Schema Validate Status report by running reportallstatus.sql. 
        $ sqlplus admin/{password]@atpdb_tp @reportallstatus.sql

        This produces an output file for each schema, in this case 7 log files. Review to make sure that there are no errors and that all objects are showing a VALID status.

At this point, the process has converted MS SQL Server Database AdventureWorks database to an Oracle Database structure on Oracle Autonomous Database. The conversion process created 7 new schemas on Oracle:
  • ADVENTUREWORKS
  • SALES_ADVENTUREWORKS
  • PERSON_ADVENTUREWORKS
  • PURCHASING_ADVENTUREWORKS
  • EMULATION
  • HUMANRESOURCES_ADVENTUREWORKS
  • PRODUCTION_ADVENTUREWORKS