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.
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)
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).
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'
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.
Wait until the Output 'Done' is displayed within the console (bottom of the screen).
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'
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.
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).
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
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
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
- 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
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
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
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.
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 :
- MESSAGES
- ATTACHMENTS
- PARTICIPATION_WORKFLOWS
- ELASTICSEARCH_INFO
- DCD_REPLICATIONS
- AUTHORITIES
- AUDIT_LOGS
- ATTACHMENT_CONTENTS
- USER_REQUEST_DATA_COLLECTIONS
- STATUS_MESSAGES
- STABLE_DATA_UPLOADS
- REGISTRATIONS
- NOTES
- GUEST_USERS
- DOCUMENTS
- ABOUT
- USER_CONFIGURATIONS
- USERS
- STABLE_DATA
- PARTICIPATION_WF_HIST
- EVENTS_NOTIFICATION_USERS
- WORKFLOW_HISTORIES
- VIEWS
- USER_REQUESTS
- USER_DATA_COLLECTIONS
- TASKS
- REGISTRATION_OPTIONS
- ORGANIZATIONS
- INSTALLATION_DETAILS
- FOLLOW_UP_CONDITIONS
- FOLLOW_UPS
- FAST_TRACK_RECORD
- EVENTS_NOTIFICATION_TIMES
- EVENTS_DATA_COLLECTION_DEF
- DOCUMENTS_ATTACHMENTS
- WORKFLOWS_TO_MIGRATE_COMMENTS
- WORKFLOWS
- PARTICIPATION_PROGRESS
- PARTICIPATION_DOCUMENTS
- MESSAGE_METADATA
- FAST_TRACK_UPLOAD
- EVENTS_WORKFLOWS
- CODED_DOCUMENT_CONTENT
- TESTORACLE
- TECHNICAL_METADATA
- PROGRESS
- METADATA
- CONFIGURATIONS
- USER_METADATA
- UPGRADES
- UPDATED_USERS
- EVENTS
- DATE_METADATA
- 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.