Search
Close this search box.

Database Migration and Modernization: comparison of DMS Solutions (Azure, AWS and GCP)

Why Database Migration?

Every migration and modernization project deals with databases, there is no single migration effort that does not have databases among the workloads to be migrated or modernized.

In this article, we’ll delve into the intricacies of database migration, comparing the approaches offered by Azure, AWS, and GCP while focusing on popular database options like MySQL, PostgreSQL, MS SQL Server, and Oracle.

  • The database is the simplest resource to modernize from virtual machines/containers to cloud-native platforms (PaaS) by using database migration tools
  • In our research, we prioritized keeping the database platform, however, database schema conversion (example: AWS Schema Conversion Tool) provides a great opportunity to modernize legacy workloads, along with the applications connecting
  • Lastly, we strongly believe, that with the rise of Data and AI, data needs to go to the cloud to lower latency when supporting data-intensive cloud-native AI workloads

Our expertise comes through completed projects (hands-on experience)

I am deeply enthusiastic about technology and firmly believe in the importance of hands-on experimentation before assuming the role of a trusted advisor. This principle holds particularly true when encountering limitations or when reality diverges slightly from the promises made in marketing materials.

Last summer, we (Hugo Rodrigues | LinkedIn my excellent stunning ex. colleague/friend and I) established a multi-cloud lab environment due to the scarcity of information available on the public internet regarding challenging queries concerning database migration tools, particularly their limitations.

While ChatGPT excels at generating blog articles like this one, it isn’t equipped to address intricate technical questions that may arise during database migrations — at least not yet.

Here’s a simplified architectural overview of our lab, demonstrating the connection between on-premises VMware virtualized source VMs and Azure.

While we utilized the same source for testing AWS and Google Cloud solutions (which aren’t depicted in this diagram), constructing a similar lab is within reach for anyone with basic infrastructure and database skills. It’s simply a matter of investing time and commitment to embark on the challenging journey of experimenting with DMS tools and grasping the nuances dictated by the source/destination or chosen cloud platform.

We invested time and resources to explore what could be accomplished.

Here are some notable achievements

  • We deployed all major databases (MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database) on VMware on-premises VMs
  • Alongside our customers’ production databases, we conducted tests with DMS solutions using various sample databases, including Oracle HR and Microsoft SQL’s renowned AdventureWorks.
  • We found cross-database samples, such as Sakila (available on GitHub – jOOQ/sakila: The Sakila Database), to be particularly useful during experimentation and preparation for real projects.
  • Our lab is connected to Azure, AWS, and GCP through site-to-site connections. This is essential because most database migration solutions rely heavily on private networks and endpoints. We conducted tests and comparisons using both VPN and public internet-based scenarios to ensure thorough evaluation.
  • We established cloud destinations ranging from databases in virtual machines (similar to on-premises sources) to managed Platform as a Service (PaaS) offerings, such as Azure SQL Database.
  • We also explored modernized PaaS options, like Amazon Aurora MySQL, and managed Infrastructure as a Service (IaaS) compatible solutions, such as Azure SQL Managed Instance.

Comparison of database migration services (DMS)

Here are the key findings from our experiment after deployment and testing.

Cost of the database migration tools/projects

Certain features of DMS tools are not available for free, and online migration might incur additional costs.

AWS MAP, Azure AMM(P), and GCP RaMP programs: each cloud provider is eager to transition on-premises resources to the cloud, offering initiatives for system integrators to facilitate these migrations. These programs primarily focus on migrating on-premises workloads, including applications, and optionally, VMs/containers hosting databases.

DevOps automation capabilities

Our strategy is to maximize automation to minimize manual tasks and capitalize on past experiences to expedite delivery times.

Cloud-native language support, example: AWS Database Migration Service resource type reference – AWS CloudFormation (amazon.com) versus multi-cloud provisioning support, example: GitHub – terraform-aws-modules/terraform-aws-dms: Terraform module to create AWS DMS (Database Migration Service)

Underlying technology

While online DMS-assisted near real-time database replication is impressive, at scale, it’s crucial to also understand transaction log shipping and offline database/schema import-export methods.

In cases of extreme Recovery Time Objectives (RTOs) and Recovery Point Objectives (RPOs), creating online active/active stretch database clusters becomes necessary, balancing migration costs against downtime.

Portal-only features & when a manual approach is required

Depending on the solutions some features are only possible on cloud portals and not possible via APIs (database migration monitoring is a good example).

Occasionally, it’s necessary to migrate users/schemas before initiating the migration process. We’ve found that data validation is often performed manually. A straightforward validation method involves comparing database hashes. For instance, in PostgreSQL, the equivalent of MySQL’s binlog position is referred to as the Write-Ahead Logging (WAL) location or WAL position.

Database platform independent migration approach

Database migration involves transferring data, schema objects, and related elements from an on-premises environment or one cloud platform to another. The process aims to ensure minimal downtime, data integrity, and compatibility with the target environment.
We advocate for a straightforward three-step approach: Plan, Execute, and Validate, which we believe is the most effective method.

Plan (assess/deploy/test)

  • Get access to source databases
  • Use database migration assessment tools combined with our experience to identify possible target solutions
  • Design the migration – this is highly dependent on RTO/RPO requirements, size of data (from physical disk shipment to online-near-real-time database replication)
  • Setup necessary networking (public firewalls and/or private endpoints/networking)
  • Deploy and configure DMS solution (solution, DMS project, required resources) – automation preferred
  • Required adjustments on source database (example is change the on-premise PostgreSQL to enable Logic Replication)
  • Required adjustments on destination database (example is to import the DB Schema to the Azure PostgreSQL) – sometimes users, database schema creation is not supported by DMS tool
  • Test migrate a smaller subset of the data (most DMS supports table filtering), functional and performance testing

Execute (cutover)

  • Adoption and change management, the communication plan for key stakeholders, keeping users updated throughout migration (in case database connection change has an impact on their work, direct DB connection clients used, etc.).
  • Migrate data (depending on size and complexity, in multiple phases).

Verify (complete)

  • validate migration
  • connect the application to the destination database
  • decommission old database

Oracle Database migration/modernization considerations

Having formerly been certified as an Oracle PL/SQL developer and collaborating with Oracle DBAs in prominent Telcos and Finance sectors, I remain unconvinced about the suitability of large Oracle databases for schema conversion and migrating to a different database platform.

Enterprises have adopted Oracle databases for specific reasons, leveraging their distinct capabilities. However, it’s worth discussing whether Oracle Database is solely utilized for basic table and row operations without exploiting its advanced functionalities, thereby making it interchangeable with any SQL database.

Challenges with Oracle schema conversion

Schema conversion predominantly arises as a concern when dealing with Oracle as the source database, yet it presents potential challenges and risks. Here are the obstacles encountered in converting from Oracle to PostgreSQL.

  • Differences in SQL Syntax and Features: Oracle and PostgreSQL have differences in SQL syntax and supported features. Migrating complex queries, stored procedures, triggers, and functions may require significant rewriting and adaptation.
  • Data Type Mapping: Think about special/custom user types (advanced table in table types). Mapping data types from Oracle to PostgreSQL can be challenging due to differences in data type definitions and capabilities. Ensuring compatibility and data integrity during the migration process requires careful consideration and sometimes data transformation.
  • PL/SQL to PL/pgSQL Conversion: Oracle’s PL/SQL (Procedural Language/Structured Query Language) and PostgreSQL’s PL/pgSQL have similarities but also differences in syntax and functionality. Converting PL/SQL code to PL/pgSQL or other PostgreSQL-compatible languages requires thorough understanding and manual adjustments.
  • Performance Tuning and Optimization: think about Oracle Enterprise databases table partitioning and high-performance processing (PostgreSQL performance limitations)
  • Third-Party Tool and Application Compatibility, a different way of handling in-memory tables (in case it is used)
  • Connections to the underlying operating system, file system, etc. (lack of Oracle’s custom database packages in PostgreSQL)
  • Transaction Management and Locking
  • Security and Access Control

Avoid lift and shift operating system + Oracle DB as a server to Cloud

Moving Oracle Database along with its underlying operating system to the cloud through a lift-and-shift approach is generally the least favoured option and likely considered only as a last resort.

While there have been articles about deploying “Oracle Database on the AWS Cloud: Quick Start Reference Deployment,” they are currently not supported or available.

Alternatively, you can redeploy Oracle Database along with the operating system from the Azure Marketplace using Oracle’s published images. Additionally, third-party vendors offer support for such deployments.

Oracle DB to OCI, applications to multi-cloud, leverage cloud-interconnect

I am a big advocate of Oracle’s multi-cloud strategy, which involves migrating Oracle Database to Oracle Cloud while connecting applications to Azure, facilitated by a low-latency interconnect between public clouds to ensure seamless application functionality. A similar approach for AWS and GCP is currently in the works.

“Oracle Database Service for Microsoft Azure is an Oracle-managed service for Azure customers to easily provision, access, and operate enterprise-grade Oracle Database services in Oracle Cloud Infrastructure (OCI) with a familiar Azure-like experience. Users can seamlessly build Azure applications with the high performance, high availability, and automated management of Oracle Database services, such as Autonomous Database, running on OCI.”
Read more here: Oracle Database Service for Azure | Oracle

AWS and GCP alternatives to run Oracle DB in cloud

If your application is already hosted on AWS or Google Cloud, it’s advisable to conduct research to explore the existing solutions available for supporting your Oracle Database, especially if it hasn’t yet been migrated to Oracle Cloud.

“Bring your Oracle workloads to Google Cloud with Bare Metal Solution and jumpstart your cloud journey with minimal risk.”
Learn more (source): Bare Metal Solution For Oracle | Google Cloud

“Amazon RDS for Oracle is a fully managed commercial database that makes it easy to set up, operate, and scale Oracle deployments in the cloud. Amazon RDS frees you up to focus on innovation and application development by managing time-consuming database administration tasks, including provisioning, backups, software patching, monitoring, and hardware scaling. You can run Amazon RDS for Oracle under two different licensing models – “License Included” and “Bring-Your-Own-License (BYOL)”. In the “License Included” service model, you do not need separately purchased Oracle licenses; the Oracle Database software has been licensed by AWS. You can take advantage of hourly pricing with no upfront fees or long-term commitments. In addition, you also have the option to purchase Reserved DB Instances under one or three year reservation terms. With Reserved DB Instances, you can make a low, one-time, upfront payment for each DB Instance and then pay a significantly discounted hourly usage rate, achieving up to 48% net cost savings.”
Learn more (source): Oracle Relational Database – Amazon RDS for Oracle – AWS

Additional resources, related exams

Lastly, but certainly not least, becoming a database migration expert is a journey. It starts with obtaining cloud architect certifications as a foundation, followed by specialization in database workloads. In my experience, these exams share similarities across clouds due to the similarity of supported relational databases. However, differences may arise depending on the specific scenarios you encounter and support.

Related posts

Using ChatGPT to assist multi-cloud scenarios

I have generated Terraform codes for the same Ubuntu VM with OpenAI’s Chat GPT-4 and compared the VM prices per region in a fancy table. HCL code results are quite accurate, however, I would not trust price comparison due to outdated information. You can get adjustable results in minutes saving lots of human research time.

Comparison of VMware relocation options in public cloud

I keep researching this topic from several perspectives: regional availability, provided architecture, most popular use cases, VMware software versions, provided hardware configuration, and finally the price of a 3-node vSphere cluster in the Cloud.

AWS MiGratioN, GCP Migrate4Cloud, and Azure Migrate pros and cons

It’s been more than 5 years since I am testing and comparing 1st party migration tools. I have seen these tools getting better over the years, with major improvements by acquisitions, end-of-life products, continuous changes, and improvements not just the tools but the methodology around, well-architected, CaF, the concept of the landing zone, 5Rs become 7Rs. In this article, I am sharing my experiences with the most commonly used cloud migration tools.

Oracle Database service for Azure – connecting Azure VM and Power App

I have connected a Database Admin Azure VM running Oracle’s SQL Developer (Windows version) and a Microsoft Power Platform application displaying Oracle’s HR demo schema (via on-premises data gateway on Azure VM connecting with Power Platform’s Oracle Premium Connector) to the same Oracle Database hosted on OCI.

Oracle Database service for Azure – linking subscriptions

As part of my multi-cloud research, I wanted to test Oracle Database Service for Azure. In this article, you will see how to sign up for the new service and how to link Oracle and Azure accounts. I used Frankfurt datacenters, Azure MSDN, and OCI paid account (Free Tier does not work) using my private Azure Active Directory.