The main scope of work in this project was to migrate Oracle databases from on premise to GCP ClougSQL PostgreSQL. Our client wanted to free themselves of yearly costly license renewals and increase their footprint to managed services and the cloud cost model.

Our initial step was to assess all the objects in the database to determine the difficulty migrating to GCP CloudSQL. Objects such as tables, indexes, triggers and sequences are generally easily converted to the new platform. Other objects such as packages, procedures, functions will require manual conversion and testing. Extra Oracle database features need to be checked for a PostgreSQL equivalent that is supported by GCP CloudSQL. Determine which components of the application need to be modified in order to support the new database platform

Once all potential issues and possible delays have been identified, database objects are converted and created in the target system. We create a baseline for objects using a number of tools that save our time and our customers cost. Difficult features to convert are not included in this baseline, they will be handled at the next step.

When all baseline objects are created, manual work begins on any stored code procedures, packages, and functions. From our extensive experience in this task, we have created our own list of best practices ensuring successful migration of code. Full testing of these functions are done with data that we supply. In order not to bother our customers too much, we take the extra time to check table data and fully understand code in order to create proper test cases.

Data is then loaded from on premise to GCP and database objects are verified. The data load can be done with the use of in-house scripts, but often we use third party tools that is already being used by the client. For this client, the third party tool Striim was used to copy data to GCP. Data validation options are also discussed and agreed on at this phase.

After the data is loaded, we assisted the application team in converting SQL statements found in application code to PostgreSQL. This is a generally manual effort and can take time to work with internal application teams.

At this point we are ready to cut-over our first system which is usually DEV. Cut-over plans and rollback options are discussed and agreed on. This cut-over plan will be executed again as we do QA and PROD. Production will be the third time going through the cut-over process and generally tend to go seamlessly.