Tags: code conversion, ETL
One of the leading US healthcare organizations needed to upgrade their health record system in an effort to drive down costs while enabling new capabilities. We replaced the customer’s original Oracle database with SQL Server database to build a scalable and secure architecture. That task was challenging due to the huge volume of data along with very large tables aggregated in the original database.
As a result, the customer got the modern, secure, and less expensive platform. Please check how we migrated the custom database in the following video.
Original customer system
Our customer operates a big transactional system called “Epic” with the Clarity database as a component used for reporting. There is an ongoing ETL data feed from Epic to Clarity and various systems connected to Clarity, which were implemented as the Oracle database. The customer has customized the standard Clarity schema and created the new schema to supplement Clarity.
Clarity includes a data mart with thousands tables and views available for reporting service. Customer environment also included vendor integrations (imports and exports) and scheduled jobs, that were previously converted from Unix schedulers to SSIS or scheduled tasks.
Migration process
Our task was to migrate several hundred tables, views, and reports in a custom database from Oracle to SQL Server, and then update the customer environment, including ETL, reports, scripts, and jobs.
We faced a hard challenge during migration due to a large amount of data aggregated in the Clarity database (around 8 terabytes).
At the same time, we had to complete the migration process on the working system, with a requirement of only 1-day downtime per month.
1 Database schema conversion
We had to migrate 11 custom elements in the Clarity schema. Besides these Clarity customizations, only the EULER schema needed to be migrated.
We have performed automatic conversion of database objects by the means of SQL Server Migration Assistant (SSMA) for Oracle. However, SSMA did not convert some of the objects, so we had to convert them manually.
The database conversion falls into three pieces:
- The EULER schema is entirely custom and had to be migrated.
- Certain Clarity Custom Objects have been delivered to Clarity (apart from the views).
- Existing Clarity Custom Views consisted of a mix of views built on 36 patterns with minor changes to each instance, and a number of individual views. The migration strategy is to migrate around 200 views and then make minor modifications to the derived views in the form of parameter changes.
2 Code Conversion
We rewrote Fair Warning Korn shell scripts to add compatibility with Microsoft Windows. Also, we converted and updated Import and Export Jobs, and converted the Cron scripts used for the ETL processes to SQL Server scheduled jobs.
Furthermore, we have implemented the business intelligence reports in the new customer’s system.
3 Data Migration
This part was one of the most difficult in the project due to the size of the database and the limited windows during which migration could take place.
We have divided the data into three kinds:
- Epic feed,
- Smaller Tables including Euler,
- Large Tables.
We have defined a threshold to divide tables into “large” and “small”. For the purpose of this migration, we define a limit of 250M rows and more as “large”.
We have moved the largest portion of data using DB Best products Database Compare Suite and OraMigrator, except for 3 very large tables (between 9 and 16 billion rows).
To subdivide large partitions, we have used where clause in Database Compare Suite to delineate rows in smaller blocks.
OraMigrator has some restrictions when working with very large tables, so we have performed manual migration for these tables. To solve this task, we have developed a special tool that extracted data from Oracle database, processed it, and handled any possible errors.
We have migrated around 30 terabytes of data in total including the testing stage.
4 Functional testing
At the testing stage, we had to ensure that original and migrated objects return identical results in Oracle and SQL Server environments.
We performed three types of tests, that were limited to the parts of the system that had been migrated: functional code tests, data migration tests, and aggregation tests.
Then we tested migrated functions and stored procedures during functional testing stage. Also, we ensured that the Fair Warning scripts can handle modified data.
Finally, we used Database Compare Suite to compare original and converted schemas.
Moreover, we tested the queries that report on aggregated values by comparing them between Oracle and SQL Server. That queries represent application level check-sums that focus on the relevant to the business numbers.
5 Deployment
After we had migrated the data to the production environment, the catch-up of data occurred with Clarity ETL processes.
As a result of the completed project, our customer obtained the new system based on the SQL Server database that allowed to reduce the licensing costs.
Feel free to contact DB Best to complete your migration project.
