Blog: Creating a CI/CD solution for the database unification project

Being an independent software vendor with a pile of applications written decades ago and a huge database in Oracle, our customer wanted to upgrade their software and make it more accessible for clients money-wise. They have built and sold applications for Oracle Database exclusively for 35 years. By now these solutions had become not only harder to sell due to competitiveness but difficult to support and modernize. Our client required applications’ upgrades, alternative cheaper hosting for users, and the maximum automation of processes around the applications and the databases.

Within this project, we carried out unification and added the PostgreSQL to the list of supported databases. Concurrently, we implemented the crucial CI/CD pipeline which opened new perspectives in terms of efficiency.

The project overview

We have an enterprise-level customer offering a comprehensive list of applications. Their business is all about ensuring high-quality services by managing applications efficiently and in a timely manner. The customer had a portfolio of apps written in different technologies, such as Java, Pro*COBOL, and Pro*C. The applications were partly covered by tests, and the database wasn’t tested at all. The apps’ code implied specific Oracle-oriented commands, which made it impossible for some applications to migrate them to PostgreSQL as they supported nothing but Oracle.

What we needed to achieve was a rewrite and customization of all services, so that these services interacted with the PostgreSQL database exactly as with Oracle Database. Even more important for database unification projects, we needed not only to solve the problem of new database support but also provide a path for ongoing development and code merges. This meant that the new code had to be re-converted regularly and changes had to be continuously applied to the target database with the help of automation algorithms.

We turned to the CI/DC practice to enable the following:

  • easy and quick automatic updates deployment for both data storage and all applications;
  • proper testing of the services and the databases with the help of the latest automation testing tools;
  • a safe and easy-to-use system environment for optimum big team performance.

At the start, we had these project insights and obstacles:

  • a range of Java and Groovy applications on the Oracle database needed to be unified for PostgreSQL;
  • applications in Pro*C language exclusively used by Oracle Database management systems;
  • Oracle-oriented Pro*COBOL applications;
  • one of the biggest databases that Oracle had ever hosted.

We set the following goals for the unification and automation project:

  1. Have all the applications work with Oracle Database and PostgreSQL database depending on settings.
  2. Get the full PostgreSQL version of the customer’s database in Oracle.
  3. Rewrite the Pro*C applications and bring them to the ECPG standard to enable their interaction with PostgreSQL.
  4. Build Java applications instead of Pro*COBOL applications to make them compatible with PostgreSQL.
  5. Continue the development of applications for Oracle and enable maximum automation for newly added changes to PostgreSQL.
  6. Enable Unit testing for all applications. In the beginning, the customer had only 10% of the applications code covered by tests.
  7. Enable proper testing of the database migration. Prior, the customer did not have any tests for the database.
  8. Enable Integration testing for all applications including the Selenium-based UI tests automation.

The infrastructure for a big team

Talking about a massive project, our customer had about 40 k objects in the database and 5.5 million lines of code for 7 applications and 400 command-line interfaces. So, for this migration and unification, we had to involve a huge team of experts that would work simultaneously to achieve the project goals. The team and the sub-teams included around 30 experts: Database Developers, Application Developers, Test Automation Experts, System Architects, DevOps Experts, and Project Managers.

Having such a massive project and a huge team, we needed to clarify all the infrastructure modules essential for this team to achieve the highest productivity. In this respect, we created an infrastructure that contains numerous database and application servers for the experts to work comfortably in parallel:
The big team infrastructure diagram

Technologies stack

To solve all the issues that we met from the start and ensure superior performance and communication for a massive and constantly growing project, we had to come up with the optimum technology stack. This stack was to meet our development, unification, testing, and automation needs.

Application Development

We started with updating most of the applications to Java and Groovy-based web applications and building the new ones in Java and Groovy. In addition, the team also:

  • transferred the Pro*C scenarios for Oracle to ECPG scenarios to work with PostgreSQL;
  • transferred the Pro*COBOL scenarios for Oracle to Java to support PostgreSQL;
  • implemented the Groovy configuration files since they define a database the application connects to, whether it’s Oracle or PostgreSQL.

Finally, we enabled Unit testing based on TestNG since all development is done using the Java technology stack.

Database Migration and Tests Automation

For the most efficient database migration, we picked the AWS SCT tool as it has a CLI mode allowing for integration into the automated process. And, we had the PostgreSQL 11 database as the target database for this project. For enabling test automation, we went with Selenium WebDriver and Java to automate web-based testing. For evaluating the database migration results we applied DB Best Migration Platform.

Continuous Integration and Continuous Delivery

This is the range of tools that allowed us to build a neat automation process:

  • BitBucket as a version control system;
  • Jenkins as an orchestrator;
    • 2 Linux Jenkins Slaves added to all the general flows;
    • 1 Windows Jenkins slave to run the DB Best Migration Platform tests;
    • 1 Windows Jenkins slave to run Selenium web tests.
  • AWS EC2 as VM machines to do all the build process;
  • Microsoft Teams and Emails as notification services.

Before, the client had no CI/CD processes implemented within the project. Thus, all the changes had to be done by hand and deployed separately for each of the applications and databases. In the beginning, no database testing process existed. The client’s team basically fixed the issues in the live environment when detected by users.

Proposed solution

To reach the project goals and enable automation, we designed the automated process driven by Jenkins. This process contained about 20 different steps with some of them running in parallel. The overall idea was to have a daily-based migration, build, and deploy for all applications and databases. In this way, we allowed for regular updates in a few clicks that would be implemented automatically for all services. We managed to build an infrastructure that is now easy to work with so that the clients’ team could be independent and possess all the instruments for maintaining and expanding the project successfully.

General Flow

The general flow of the automated processes built has the following sequence:

    1. Restore Initial Oracle Database.
    2. Apply Changes to Oracle from BitBucket.
    3. Apply Oracle Test Data.
    4. Run SCT to Convert Database.
    5. Apply Manual Changes to PostgreSQL DB.
    6. Migrate Data from Oracle to PostgreSQL.
  1. Build Applications.
  2. Deploy Applications.
  3. Run Database Tests.
  4. Run Selenium Tests.

Test Automation environment

Detailed Flow

The detailed automation flow is as follows:
Initial Power Query Editor

Gains for the customer

With respect to migration, we achieved an amazing 95% automation of the database conversion. This evolves the best practices for database migration and unification. For enabling such an efficient automatic database conversion score, we implemented a very specific knowledge of AWS SCT (Schema Conversion Tool), and its conversion workflow.

What’s more, our team came up with recommendations for the optimum code writing in Oracle, so that the source code could be converted automatically and purely. Now engineers on the clients’ side implement these best practices for their new applications.

Here are the key achievements for our customer with the CI/CD solution

  1. A stable and mature automation process in compliance with the best practices of 2019.
  2. Fast iterative development and deployment due to the unified databases.
  3. A well-controlled development process and a predictable migration schedule.
  4. A set of customized playbook items for the engineers on the client’s side to achieve even better results from auto-conversion of Oracle code to PostgreSQL.

 

The DB Best team is ready to contribute to your outdated system update and optimization. Find out more about modernization options, and feel free to apply for a consultation on the best way to boost the performance of your solution and automate the daily processes of your development team.

Start switching to a modern environment and workflow today! DB Best is here to enable it for you.

Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin