Oracle workload migration

Executive Summary

The database market is large ($46B in 2019 according to Gartner) and Oracle is one of the major vendors. Over the last few years, the cloud and open source technologies have provided new ways of managing data, whatever the workload might be. At the same time, many Oracle customers have decided to migrate out of Oracle to a new technology, for different reasons but mostly driven by cost and cloud migrations. In July 2019, Gartner indicated that “By 2022, 75% of all databases will be deployed or migrated to a cloud platform, with only 5% ever considered for repatriation to on-premises.”

There are significant challenges in replacing an existing Oracle database. The main one is the cost in time and money to convert all the Stored Procedures written with Oracle’s proprietary language. Planning those migrations following a well-established process and using software to automate the code conversion are key to being successful. Doing the conversion by hand is usually not practical and too expensive.

Once the new system is fully migrated, the cost savings and the business agility acquired by moving workloads to the cloud can be substantial and we are only in the early days of this fundamental shift away from expensive proprietary technologies such as Oracle.

What is a migration?

A migration is the process of moving a given workload from an Oracle database (and potentially other tools) to an equivalent system, most likely in the cloud. After the migration is complete, the new system takes over the workload and the original Oracle database can be shut down or significantly scaled down.

Motivation

There are many reasons why customers consider moving their workload out of an Oracle database. Based on numerous conversations, a pattern of recurring concerns has emerged. The main reason is the cost associated with running Oracle: License fees, support and potentially very expensive audits.

The second reason is migrating to the cloud. Although it is technically possible to run an Oracle database in any of the three main public cloud providers (with some caveats), it may be very difficult to buy a license from Oracle.

The third reason mentioned is a corporate decision to move to Open Source technologies and out of proprietary solutions.

Whatever the reason(s), once the decision is made, there are many challenges that need to be addressed in order to make it happen.

Challenges

Right from the start, the amount of work required can be daunting. It is very common to have accumulated many Oracle databases over the years. Migrating a handful of databases and migrating hundreds or thousands of them are different challenges.

Next, there is a need to decide what technology to use instead of Oracle. The market now has a plethora of tools that can do the job of an Oracle database. For instance, if your workload is driving an OLTP application (lots of small concurrent queries), an open source database such as PostgreSQL could be a good choice because it behaves a lot like what Oracle professionals are used to and has been proven in Production by many large organizations. One can run PostgreSQL in any of the three major clouds without having to pay for a license.

If the workload is a Data Warehouse (a few concurrent heavy queries with a sprinkle of small queries), there are many tools that can be considered. One of them is still PostgreSQL but if you intend to use cloud native tools, something like Amazon Redshift or Google Big Query can also get the job done.

Migrating out of Oracle doesn't have to be all or nothing. It can include keeping some of the existing workloads on Oracle and migrating a subset only to other technologies.

Successful migration

There are three major areas that need to be worked on for a successful migration:

·     Convert the data structures (Tables, Views, Indices…)

·     Move the data over

·     Convert the stored procedures from PL/SQL to something else.

In general, converting the data structures is ‘easy’ when using typical data types that are available out of the box in any database (Integers, Numbers, Data/Timestamps, Varchar, etc.) but can be more difficult when using more specialized types like Blobs, Geo types, etc. This is a key requirement in selecting a target technology. If the mapping of data types used in Oracle and the ones available in the target engine is straight forward, converting will be easier.

Moving the data usually requires careful planning and the use of external tools that simplify the process, such as CDAP (cdap.io) as there might be some details ‘lost in translation.’ Also, if the original system running on Oracle cannot be stopped during the final cutover to the new system, there will be a need to implement a Change Data Capture (CDC) pipeline that can read from the Oracle transaction logs to update the target system until the cutover. Testing and rehearsing this operation are critical to the success of the migration.

The last piece is usually the most time consuming and represents the bulk of the cost. Over the years, many Oracle users have added Stored Procedures (SP) written in Oracle’s proprietary language, PL/SQL. It is very common to have tens of thousands or even hundreds of thousands of lines of code in a single database instance.

A key component in selecting a replacement technology is its capability to run code (inside or outside) to reimplement the logic embedded in the original PL/SQL code. Depending on the technology selected, this can also be Stored Procedures (for instance, PL/pgSQL in PostgreSQL or T-SQL if using Microsoft SQL Server) or it can be code running outside of the database that uses common programming languages (Java, Python, JavaScript…)

The challenges with Stored Procedures are:

·     The cost of converting them by hand

·     The time it takes to do the conversion

·     The quality and consistency of the result

Those challenges are amplified if dealing with a large number of databases. In this case, this means converting, testing and debugging potentially millions of lines of code or more. The cost of doing this conversion by hand can be astronomical.

How to approach it

Define the objective

Before embarking on a migration project, it is vital to define the objective so that planning and measuring progress can be done. The objective should at least define why the migration is to be done, the number of databases to migrate, a time frame, a budget, target technologies considered and who is going to do the work.

Size of the challenge

The number of databases to migrate is directly related to the effort needed and thus the time and cost associated with the project. If the number is just a few databases, doing everything by hand is an option. However, if the scope requires migrating tens, hundreds or thousands of databases, the time and cost will require using some software accelerated conversion.

Once all the different databases to be converted have been identified, it is time to define in what order the migration needs to be done.

Prepare for scoring

An approach that works well is to list all the different databases and score them based on key dimensions such as:

·     Business area(s) managed or impacted (Vendor, Customer, Orders, Deliveries, etc.)

·     Can the cut over be done offline (Yes/No)?

·     Size of the migration (Schemas, volume and complexity of data, number of lines of PL/SQL) and expected effort

Based on this knowledge, the different databases can be scored and ranked. Start with the ‘easy ones’ that don’t have many dependencies and can be switched over with little risk to the supported application or process. Build up knowledge and confidence with rapid success. Then, even before having completed all the ‘easy’ migrations, select a few ones in the middle of the pack as they are not ‘too easy’ or ‘too hard’ to get done in order to build even more momentum. Finish the rest after completion of those two populations.

Who does the work?

It is rare for larger migration projects to be done entirely in-house. Usually, a mix of FTEs and consultants constitutes the migration team. The cost and time constraints are such that doing all the work by hand is usually not feasible. In this case, look for consultants who can help with the migration who use software to accelerate the process.

Whatever software the consultants use, make sure they can customize it to control precisely what the result looks like and that the software is fast enough to allow for running the entire conversion multiple times a day or even per hour. The faster the software runs, the better. The reason is that if there are changes to be made, those need to happen as fast as possible as this is an iterative process (generate code, test & debug, fix the generator, generate again.) When the QA team finds bugs in the converted code, the consultants can fix the code generation and re-run the whole conversion automatically.

If you need to convert hundreds or thousands of databases, being able to convert those in bulk very quickly with software becomes a significant time saver.

Migration process

Assessment and Planning

Here we define the objective, gather details about all the databases to convert, score and rank them. We then pick the order for the migration and build a plan for the project to define who does what and when.

Execution

This is where the actual conversion takes place. Hopefully, most of it can be done with software so that the remaining piece that need to be done by hand is small enough to be manageable.

It also includes testing the converted database and focuses on:

·     Technical tests – Is the converted data correct? Does the conversion software report a high percentage of successful conversion? Is the performance meeting SLAs?

·     Functional tests – Does the converted application work the way it should?

Roll out in production

Before rolling out the new system in production, it is very important to rehearse the cut over multiple times to make sure the process is ready. There probably will be a need to run both the old system and the new one in parallel for a while with a constant update mechanism in between, built on a Change Data Capture (CDC) tool. This way, it is possible to check that the new system behaves as expected. More importantly, it also offers a rollback path in case something goes wrong.

Eventually, once it is confirmed that the new system works as intended, the old one can be shut down.

Optimization and support

After the roll out, there is a need to optimize the new system and support it. That’s where the conversion team can be used to become a center of excellence on the new technology.

What’s next?

In July 2019, Gartner indicated that “By 2022, 75% of all databases will be deployed or migrated to a cloud platform, with only 5% ever considered for repatriation to on-premises.”

We are now in the early days of a major effort to migrate database workloads to the cloud and if Gartner is right, this is a complete shift in the market happening at an impressive speed.

These migrations provide significant savings for existing Oracle customers and allow them to become more agile and respond quicker to business needs by having access to all the elasticity and versatility the cloud has to offer.