How do you migrate a large, expensive, and on premises Oracle data warehouse to a free, open source, and cloud hosted database?

PostgreSQL + Amazon RDS + OpenSCG (of course)

The Customer

truck_orange_small A leader in the GPS (Global Positioning System) market has a fleet management product that provides businesses with real-time visibility of vehicle location, status, and diagnostics.

The Need

The client was looking to expand the on premise Oracle, Informatica and Microstrategy – based data warehouse. Growing data volumes, a need for a robust disaster recovery solution, multi-region (U.S. and U.K) capability requirements, and increasing Oracle licensing costs signaled it was time to make the leap to an open source / cloud based solution. Our customer’s fleet management system was ready for a paradigm shift in its data storage strategy.

The Solution

OpenSCG proposed a migration to AWS using RDS PostgreSQL. The databases would be hosted in the two AWS Regions for the client’s customer base: U.S. and U.K. aws

Challenges

With 10 schemas, 120 tables, and 200+ views, 20+ stored procedures, and many lines of PL/SQL code, the 6TB Oracle DW database was quite large and complex. Updates occurred 24/7, so there was no room for downtime and the bandwidth needed for data transfer was limited. The client’s system was relying on an older version of Informatica which would require an ODBC patch to support ETL processes to PostgreSQL.

OpenSCG Added Value

Our team created a patch for the PostgreSQL ODBC driver to support the client’s older version of Informatica. As a result, we were able to submit a pull request to the community ODBC driver project.

Our internally developed regression testing tool enables us to easily execute queries against both source and destination or re-execute against destination and compare results from prior runs. We used this tool to verify the 150+ BI dashboard queries. We created a test case for each query and ran the full suite to verify the pl/sql migration. In addition, we were able to preserve the existing Informatica and Microstrategy based end user reporting.

Another of our internally developed tools we use for schema migration avoided 100+ hours of additional cost for this project.

Transitions often offer the unique opportunity for cleanup. During our planning effort, we identified a data purge opportunity and reduced size to be migrated from 6TB to 2TB.

The Outcome

zones_aws The OpenSCG team was able to successfully complete the project in 6 weeks. In only 6 months, the client was able to see a ROI on Oracle licenses alone. It was determined that the client would save more than $100k annually in Oracle licensing costs. Hosting the databases in AWS RDS enables taking advantage Amazon’s Regions and Availability Zones, decreasing latency and ensuring failover. This helped our client not only respond to the needs of its UK and US clients, but gives them the opportunity to expand globally.