Save time, money and modernize your legacy database estate .... but first assess

As companies modernize their applications and infrastructure, many IT stakeholders are looking to open source technologies to help them do so. One area where open source is gaining traction is in relational database management systems (RDBMS). Open-source RDBMSs such as PostgreSQL are becoming increasingly popular alternatives to commercial RDBMSs such as Oracle and Microsoft SQL Server. There are several reasons for this shift. 

First, open-source RDBMSs are often more cost-effective than legacy, proprietary RDBMSs. They are typically free to download and use, and there are no licensing fees. 

Second, open-source RDBMSs are often more flexible and scalable than legacy RDBMSs. They can be easily customized to meet the specific needs of an organization, and they can be scaled up or down as needed. 

Due to these factors, open source RDBMSs are becoming a popular choice for organizations of all sizes. They offer a number of advantages over legacy RDBMSs, including cost-effectiveness, flexibility and scalability.

Migrating from Oracle/SQL Server to PostgreSQL

Migrating from Oracle/SQL Server to PostgreSQL can be a challenging task. The migration strategy will vary depending on your specific goals, but there are some general strategies that can help to smooth the migration process: 

• Utilize Database Migration Assessment (DMA): There are a number of assessment tools that will provide insights detailing the level of effort required to modernize an Oracle or SQL Server database to PostgreSQL. To expedite this step, customers can leverage the Google Cloud first-party tooling called DMA to assess Oracle/SQL Server database complexity. It’s Google Cloud’s “no cost” database assessment solution, which estimates the migration effort, cost, and anticipated return on investment. We’ll talk about DMA in more depth below. 

• Leverage code/schema conversion tooling: These tools can help to reduce the risk of human error and improve the overall efficiency of the migration. Leverage  Google Cloud tooling to automatically convert your schema and code from Oracle/SQL Server to PostgreSQL.

• Migrate data in stages: Migrating all of your data at once can be a risky proposition. It is often better to migrate your data in stages, starting with a small subset of data and then gradually migrating more data over time. Depending on your requirements, we can help you build a data migration strategy. Whether you can tolerate downtime or need real-time replication, we offer data migration solutions to meet your requirements.

• Make application changes: Switching a database engine often involves application changes. We can help scan your application repositories and identify the SQL statements that may need to be altered to run on the new database engine.

• Test the migrated data: Once you’ve migrated your data, it’s important to test it to make sure that it is working properly. There are a number of tasks required in this stage: First you need to validate that all the data is synced between the source and the target; you can leverage our tooling for this task. Second, you’ll need to perform application functional testing to validate that the application behaves as expected. Lastly, we recommend performance testing.

By following these general strategies, you can help to ensure that your database migration from Oracle/SQL Server to PostgreSQL is smooth and successful. 

The rest of this blog will focus on our database assessment process, which is the starting point in this cloud journey. A database assessment brings together industry best practices, database experts and data collection tooling to provide you with a migration strategy to tackle your Oracle and SQL Server migrations to PostgreSQL. 

Migrating to PostgreSQL: Start with a Database Migration Assessment

The Database Migration Assessment (DMA) is a no-cost customer engagement in which we collect database metadata and deliver a detailed customized customer readout. The collected data contains detailed metadata spanning database objects types, PL/SQL or T-SQL code, database features in use, current resource usage and the workload characteristics. This data is collected using our *new* first party DMA tool.

Once the metadata is collected, it gets processed and our database expert team delivers a detailed customized readout containing:

• Google Cloud target state database recommendations based on your current on-premises database workload characteristics.

• Migration effort: This reflects the level of effort required in hours to convert the Oracle/SQL Server schema and code to an open source database like PostgreSQL.

• Right-sizing for the cloud: Current on-premises resources usage information is used to right-size your Google Cloud database, so that you only pay for resources that you actually need.

• Complete migration plan: Identify your first-mover databases and create migration waves for subsequent databases.

Below is an excerpt of a customer readout, in which we looked at 14 Oracle databases. In this example, our customer wanted to modernize from Oracle to either Cloud SQL for PostgreSQL or AlloyDB for PostgreSQL. Originally, the customer’s perception was that their Oracle databases would be too complex to modernize and that it would require too much effort. Once we ran the migration assessment with DMA, we were able to determine that 89% of their database environment could be automatically converted by using Google Cloud schema/code conversion tooling. We determined this based on the graph below, in which each database is listed and the compatibility between Oracle/SQL Server and PostgreSQL is displayed. 

For each database, we categorize database objects and code into three buckets. The green bucket identifies the database object that can be automatically converted using the Google Cloud tooling. The yellow bucket identifies the database objects/code that require some manual work. Lastly, the red bucket identifies the database objects that need to be completely refactored because the Google Cloud schema/code conversion tooling cannot automatically convert these objects. In summary, the more green there is on the chart, the more objects we can automatically convert using Google Cloud conversion tooling, and the less effort is required by the customer.

For this customer, the results were better than they expected. Originally, their overall impression was that there was a lot of PL/SQL code in these Oracle databases which were making these environments sticky. However, this visualization showed that most of their Oracle objects and code could be automatically converted.

Next, we showed the customer the level of effort required to convert each of their databases. The graph below displays all the databases ordered by the level of effort (shown in hours of time) required to complete a migration. The first database listed will take the least amount of time to migrate, whereas the last database will take the most amount of time to migrate. This data is useful to help you identify the first-mover databases and define your migration waves. In other words, you can define a database group to migrate in phases.

So, how do you start? As a recommendation, we suggest starting with the least complex database first as opposed to the most complex. This will allow you to begin building a migration blueprint with repeatable processes and get you comfortable with the Google Cloud schema/code conversion and data movement tooling. These learnings in combination with a repeatable process will set the stage to expedite migrating your more complex databases. 

This customer was delighted with these results and concluded that the level of effort required to modernize their Oracle databases to AlloyDB offered a high return-on-investment (ROI), as they were moving away from an expensive licensing/support structure to a cost-effective fully managed database service. 

In this example, we only provided an excerpt of the customer readout that we delivered to the customer. However, a full customer readout provides a wealth of information. We can drill down multiple levels, right down to the individual database objects that make up the hours of effort calculation.

Key features

The DMA tool is open source and is available on Github. It currently supports Oracle and SQL Server databases:

• For Oracle,  DMA supports both AWR and non-AWR (statspack) database assessments based on customer licensing policy. 

• For MS SQL, DMA leverages Perfmon data and only collects metadata.

The table below lists the type of data that is collected with the DMA tool.

Conclusion

If you are currently using Oracle or SQL Server databases and are looking to modernize them onto an open-source database like PostgreSQL,  you can download the DMA tool from our Github repository. Or, reach out to your Google Cloud sales representative and schedule an assessment. Our database experts can provide recommendations, best practices and guidance on modernizing these databases, which in turn will provide a data-driven approach to assessing if modernizing your Oracle or SQL Server database is right for you.