Oracle to Microsoft Database Migration

Last updated: 2021-11-11 17:10

0. INTRODUCTION

This article shows how to carry out a database migration from ORACLE (ORCL) to Microsoft SQL (MS SQL) on a Microsoft computer or environment.This procedure contains three parts :

  • the database migration from ORCL to MS SQL
  • the connection of HD4DP to the MS SQL database
  • the further action to perform while performing such database migration

1. DATABASE MIGRATION FROM ORCL TO MS SQL

1.1.The database migration tool

Microsoft provides a free migration tool named Microsoft SQL Server Migration Assistant (SSMA).

  • Download it by clicking on this link, then follow the installation procedure of the application
  • Launch it, you should obtain the below.
SSMA Management tool

1.2. Set up a migration project

Create a project.
Select the MS SQL version within the dropdown list (e.g. SQL Server 2012 in the screenshot below)

User-added image

Define the database migration type :

  • Client Side Data Migration (the laptop where SSMA software is installed will bridge the SQL and the ORACLE servers, i.e. act as third party),
  • Server side Data Migration (the database migration will take place directly between the SQL and the ORACLE servers).
User-added image

1.3. Connect to the ORACLE and MICROSOFT SQL database

1.3.1. ORACLE

Fill out the credentials of the 2 database servers:

  • Click 'Connect to Oracle'
  • Fill out the pop-up window named 'Connect to Oracle'

User-added image

You should get the Output : 'Connection to Oracle established successfully' as illustrated below. If not, make sure you are using the right credentials.The loading of the ORCL database objects can take several minutes.

User-added image

Wait until the Output 'Done' is displayed within the console (bottom of the screen). 

User-added image

1.3.2. MS SQL Server

Fill out the Micosoft SQL Server

  • Click 'Connect to SQL Server'
  • Fill out the pop-up window named 'Connect to SQL Server'
User-added image

You could get a warning windows as below which explains that you do not have rights to perfom a 'Server side Migration'. Therefore, you have to select the option 'Client side migration' in the second step.

User-added image

You can ignore this warning by clicking on 'Continue'.
​You should get the Output : 'Connection to SQL Server established successfully' as illustrated below. If not, make sure :

  • the right credentials are used
  • the database server is up and correctly working by ping it using Windows command line
  • the connection string is correct (use another SQL Client for this purpose)

The loading of the ORCL database objects can take several minutes. 
Wait until the Output 'Done.' is displayed within the console ( bottom of the screen). 

User-added image

1.4. Convert the ORACLE database schema to MICROSOFT SQL Server

1.4.1. Launch the schema conversion on ORACLE

Migrate the database schema of ORCL database to MSSQL database (below, the final screen obtained after completing the 4 following actions) :

  • Go towards 'Oracle Metadata Explorer'
  • Click on the '+' sign  (the left of database server IP address or domain name, i.e 10.0.10.11 in the screen below )
  • Select the schema name of HD4DP database, i.e. 'HEALTHDATA' in the example below
  • Right click on the schema name, i.e. 'HEALTHDATA'  
  • Select and Click on 'Convert Schema'  to launch the Schema Conversion
User-added image

Display the HD4DP tables as displayed in the screen below

  • Go on 'Tables'
  • Click on '+' sign on the left the word 'Tables' to display the schema tables
  • Compare the HD4DP tables present initially in the ORCL schema
User-added image

1.4.2. Make sur the schema conversion succeeded on MS SQL

Make sure the schema conversion succeded by checking if the ORCL schema has been created with the MS SQL database. 

  • Go towards 'SQL Server Metadata Explorer'
  • Click on the '+' sign  (the left of database server IP address or domain name, i.e 'oraclemigtest.cvbniieywa3j.us-east-1.rds.amazonaws.com:1433' in the screen below )
  • Select the right database (i.e. 'test' in the screen below)
  • Retrieve the ORCL schema, i.e. 'HEALTHDATA' in that case

User-added image

  • Click on '+' sign on the left of the schema name (i.e. 'HEALTHDATA' on the screen below)
  • Go on 'Tables'
  • Click on '+' sign on the left the word 'Tables' to display the tables of the schema
  • Compare the HD4DP tables present intially in the ORCL schema
User-added image

1.5. Migrate the data from ORACLE database to MICROSOFT SQL database

1.5.1. Launch the data migration from ORACLE database server

Migrate the data from ORCL database to MS SQL database (! make sure the ORCL schema has been successfully performed !) : 

  • Follow the steps presented in section 1.4.1.
  • Select and click on 'Migrate Data' to launch the data migration towards MS SQL

User-added image

1.5.1. Make sure the data migration succeed

Once the migration will be done, you should received a report about the migration of each table.
It displays a for each table these useful fields:

  • 'From' : with table name on ORCL
  • 'To' : with table name on MS SQL
  • 'Total Rows' : number of rows of each table
  • 'Success Rate' : migration success assessment of the table

User-added image

1.5.2. Compare the data content of the same tables on ORCL and MS SQL

Compare the data content of the tables on ORCL and MS SQL (e.g. for the WORKFLOWS table in the screen below):

  • make sure the amount of rows is the same,
  • make sure the content of each field is the same.
User-added image

2. THE CONNECTION OF HD4DP TO THE MS SQL DATABASE

The connection of HD4DP to the MS SQL database has to be done via the configuration of the 'context.xml' file of the Apache Tomcat Server.

  • Consult the HD4DP installation manual for doing this setting,
  • Restart Tomcat after change,
  • Login into HD4DP and make sure the registrations within the TEST register are still visible,
  • Perform a registration test as explained in the installation manual.

3. THE FURTHER ACTION TO TAKE WHILE PERFORMING THE DATABASE MIGRATION

You should list all the HD4DP tables within the ORCL database. For instance, for the HD4DP Version 1.10.0, here are the 54 tables to migrate :

  1. MESSAGES
  2. ATTACHMENTS
  3. PARTICIPATION_WORKFLOWS
  4. ELASTICSEARCH_INFO
  5. DCD_REPLICATIONS
  6. AUTHORITIES
  7. AUDIT_LOGS
  8. ATTACHMENT_CONTENTS
  9. USER_REQUEST_DATA_COLLECTIONS
  10. STATUS_MESSAGES
  11. STABLE_DATA_UPLOADS
  12. REGISTRATIONS
  13. NOTES
  14. GUEST_USERS
  15. DOCUMENTS
  16. ABOUT
  17. USER_CONFIGURATIONS
  18. USERS
  19. STABLE_DATA
  20. PARTICIPATION_WF_HIST
  21. EVENTS_NOTIFICATION_USERS
  22. WORKFLOW_HISTORIES
  23. VIEWS
  24. USER_REQUESTS
  25. USER_DATA_COLLECTIONS
  26. TASKS
  27. REGISTRATION_OPTIONS
  28. ORGANIZATIONS
  29. INSTALLATION_DETAILS
  30. FOLLOW_UP_CONDITIONS
  31. FOLLOW_UPS
  32. FAST_TRACK_RECORD
  33. EVENTS_NOTIFICATION_TIMES
  34. EVENTS_DATA_COLLECTION_DEF
  35. DOCUMENTS_ATTACHMENTS
  36. WORKFLOWS_TO_MIGRATE_COMMENTS
  37. WORKFLOWS
  38. PARTICIPATION_PROGRESS
  39. PARTICIPATION_DOCUMENTS
  40. MESSAGE_METADATA
  41. FAST_TRACK_UPLOAD
  42. EVENTS_WORKFLOWS
  43. CODED_DOCUMENT_CONTENT
  44. TESTORACLE
  45. TECHNICAL_METADATA
  46. PROGRESS
  47. METADATA
  48. CONFIGURATIONS
  49. USER_METADATA
  50. UPGRADES
  51. UPDATED_USERS
  52. EVENTS
  53. DATE_METADATA
  54. COMMENTS

Regarding your version of HD4DP, the list can change, for further details about table structure, email support.healthdata.be@sciensano.be.  If a table is not on the list (because the list is outdated), look at the constraints and migrate it after those from the list. Through an example with the 'WORKFLOWS' table of HD4DP, this part will show you how to take knowledge of the constraints of one table. Below, you can find the 'CREATE SQL' statement used for creating the table 'WORKFLOWS' within the schema 'HEALTHDATA.WORKFLOWS'.

CREATE TABLE HEALTHDATA.WORKFLOWS

    WORKFLOW_ID NUMBER NOT NULL,
    CREATED_ON TIMESTAMP(6) NOT NULL,
    DATA_COLLECTION_NAME VARCHAR2(255),
    DATA_COLLECTION_DEFINITION_ID NUMBER,
    HD4DP_WORKFLOW_ID VARCHAR2(255),
    READABLE_ID VARCHAR2(255),
    STATUS VARCHAR2(255),
    UPDATED_ON TIMESTAMP(6) NOT NULL,
    ORGANIZATION_ID NUMBER,
    UNIQUE_ID VARCHAR2(255),
    SEND_STATUS VARCHAR2(255),
    IDENTIFICATION_TYPE VARCHAR2(255),
    IDENTIFICATION_VALUE VARCHAR2(255),
    SUBMITTED_ON TIMESTAMP(6),
    CORRECTIONS NUMBER(1, 0) DEFAULT 0 NOT NULL,
    FOLLOW_UP NUMBER(1, 0) DEFAULT 0 NOT NULL,
    DOCUMENT_ID NUMBER
);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT SYS_C005259
    UNIQUE (HD4DP_WORKFLOW_ID);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT WORKFLOWS_PK
    PRIMARY KEY (WORKFLOW_ID);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT CSTRT_WF_DOCUMENT_ID
    FOREIGN KEY (DOCUMENT_ID)
        REFERENCES HEALTHDATA.DOCUMENTS (DOCUMENT_ID);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT CONSTRAINT_WF_ORG_ID
    FOREIGN KEY (ORGANIZATION_ID)
        REFERENCES HEALTHDATA.ORGANIZATIONS (ORGANIZATION_ID);

An attention should be paid on the  'ALTER TABLE' statements (in green)  at the bottom of the code.  The table contains 4 constraints :

  • the field 'HD4DP_WORKFLOW_ID' has be unique regarding the constraint 'SYS_C005259'
  • the field 'WORKFLOW_ID' constitutes the primary key because of  'WORKFLOWS_PK PRIMARY KEY' constraint
  • there are 2 foreign key constraint
  • 'CONSTRAINT CSTRT_WF_DOCUMENT_ID', therefore the 'DOCUMENT_ID' field from the DOCUMENTS table has to be filled in
  • 'CONSTRAINT_WF_ORG_ID', therefore the field 'ORGANIZATION_ID' from the 'ORGANIZATIONS' table has to be filled in

Hence, make sure the tables 'DOCUMENTS' and 'ORGANIZATIONS' are filled in in the right way for preventing migration failure of 'WORKFLOWS' table. Such precaution has to be taken for all the tables you want to migrate.