BUSINESS INTELLIGENCE FOR SUPPLY CHAINS

About the Business

Specializing in subscription-based services for the analysis and delivery of real-time weather, agricultural, energy, and commodity market information, this global BI provider empowers customers with intelligent and actionable insights. They are committed to delivering the most advanced analytics and ML solutions to help make tough decisions easier. Industries and solutions include agriculture, aviation, marine & offshore, refined fuels, sports & safety, shipping, transportation, utilities & renewable energy, and weather.

The Business Challenges

The analytics company no longer saw the business value in paying more than $1 million annually for the legacy, on-premises Oracle environment. They were bound by the standard limitations of onpremises hardware, including lack of expandability, management issues, and inadequate backup and disaster recovery. One of the biggest challenges to migration was the sheer size of the applications. There were 4.3 million lines of code involved in both applications and making refactoring changes, as well as the database backend change, required a lot of planning, expertise, and problem solving. Due to the critical nature of the applications for the company’s daily operations, they could only be down for a few hours.

The 2nd Watch Solution

During the initial phase of the project, 2nd Watch established the infrastructure in AWS where the database and applications would be migrated. The infrastructure team created a pipeline to deploy environments and a pipeline to deploy the application. They then leveraged the pipelines to stand up new environments and deploy the application into them. Using the deployment pipeline, we were able to create a dev test environment, an integration environment, and a production environment in AWS.

The second phase involved examining the source code and database objects of both applications to identify areas with inline SQL that stored procedures and Oracle-specific references. The larger application originated in the late 1990s and was Oracle Forms. The smaller app was more modern, built in the late 2000s. There were multiple third-party applications that also integrated with the Oracle database for ETL, data transforms, data ingestion, and data pulls for reporting purposes. Modifications were required for the applications to function with a Postgres backend rather than the Oracle backend.

2nd Watch developers used the AWS Schema Conversion Tool (SCT) against the Oracle backend to produce a new database schema compatible with Postgres. We created a pipeline to quickly deploy the schemas suggested by the SCT into an RDS Aurora Postgres instance and trigger DMS to migrate data. During migration, 2nd Watch leveraged the schema tool to identify areas in the database that would not migrate directly with the database migration service. Those areas required additional adjustments to function both on the migration side of the data, and on the code that referenced that data just in case there were changes in data types and string links. The SCT identified hundreds of areas that required manual adjustments, we built custom scripts to generate those schemas and integrated them into the DB deployment pipeline. We used AWS Database Migration Service (DMS) to migrate the data in all tables.

2nd Watch chose a direct forklift migration of the applications because of their critical nature to the company’s overall operations, and their size. Financially, the company heavily depends on these applications. Refactoring and backend changes during migration would threaten business availability and make the cut over and the migration itself extremely difficult. 2nd Watch maintained the core code with minimal changes only where Oracle dependencies demanded updates. This approach limited downtime and ensured the functionality and performance of the previous system.

The Business Benefits

The most obvious and immediate benefit to the company’s partnership with 2nd Watch is eliminating more than $1 million in annual licensing. Without Oracle, the company can reallocate those funds toward value added tasks like growing their cloud-based services in their new AWS environment.

Having migrated into an Aurora Postgres RDS database environment, the company is able to scale their backend database instance sizes up or down as needed. With the serverless approach, there are still instances, but there is also an abstraction layer that automatically decouples for seamless scalability – without having to think about instance sizing or anything else. The company can also take advantage of the standard features available on RDS, including instantaneous snapshots, unlimited storage in AWS, custom retention limits, and builtin multi-region disaster recovery capabilities. Now, the company can spin up a cluster in another region and have a whole second copy of data running in a read replica state. Without the limitations of hardware, the company can customize what is backed up and where it is stored.

During the migration, 2nd Watch recommended that the client should leverage a built-in RDS logging feature that audits and tracks all activities within the database. In the legacy Oracle system, a custombuilt solution with a tremendous amount of data was used to do the same thing as the built-in logging feature. Not only did the RDS logging feature mimic the capabilities of the legacy custom solution, but it also provided additional capabilities. This is just one example of how the company’s new RDS system provides business enablement opportunities to operate more efficiently and effectively. Amazon continues to release new features on a regular basis that the company has used to improve reliability, monitoring, disaster recovery scenarios, and other functionalities that are not available with legacy on-prem environments.

The migration project also provided the company with a much-needed line-by-line QA review. Such a robust QA process is not typically feasible without a partner, but it is hugely beneficial to ensuring quality code. We were able to uncover source code that did not work as intended, was causing problems elsewhere, or was years old. This gave the company the opportunity to clean the code, fix functionality, eliminate redundancies, and overall, improve the quality of their code. Now that the company has successfully migrated to the cloud, they can begin to break apart their monolith applications. In the AWS environment, the company can be much more dynamic and flexible using modern, built-in resources and software to deploy code, roll back code, initiate new services, and facilitate integration. The cloud provides a whole new set of tools to not only complete application modernization, but a variety of other processes for ongoing optimization.