AWS Partner Network (APN) Blog
Optimizing AWS Database Migration Service for Oracle to PostgreSQL with Fujitsu
By Ben Brunswick, Cloud Engineer – Fujitsu
 |
| Fujitsu |
 |
Migrating a company’s database architecture from an on-premises database to the cloud can be a complicated and time-consuming process. There are many aspects of the end-to-end journey of migration—from discovery, assessment, management, and the migration itself.
One of the critical decisions is the choice of tooling to deliver the migration, and how to get the best out of the selected tool.
This post discusses some of the key learnings and experiences Fujitsu has gained through its use of AWS Database Migration Service (AWS DMS) during database migration projects. These insights are drawn primarily from Oracle-to-PostgreSQL migrations delivered by Fujitsu. I will focus on optimization of the migration tool, as well as understanding the limitations and best use cases for it.
In addition, this post highlights further considerations and wider topics concerning database migration. I’ll include end-to-end considerations, how to align database architecture with adapting business operations, and tips for approaching the inevitable manual steps of a database migration.
Fujitsu is an AWS Advanced Tier Services Partner with Competencies in Migration and DevOps consulting. Fujitsu is also a member of the AWS Managed Service Provider (MSP) and Well-Architected partner programs.
Optimizing AWS DMS
AWS DMS is the native Amazon Web Services (AWS) database migration tool, allowing migration of databases quickly and securely. It utilizes an automation-first approach to minimize manual processes and supports multiple homogeneous and heterogeneous migration types.
One key component of AWS DMS is the Schema Conversion Tool (SCT) which automatically converts the source database schema, and the majority of database code objects to a format compatible with the target database. It also provides a migration assessment report for a source and target database, providing insight of the complexity of the database migration.
Fujitsu has used AWS DMS for many database migration projects to AWS. Next, let’s review some of the common issues encountered when deploying AWS DMS at large scales.
CDC Throttling
One of the key issues Fujitsu has faced in database migrations is managing the change data capture (CDC) functionality for large write-heavy databases. When migrating these databases, there’s a tendency for the CDC to exhaust available network bandwidth, which is problematic when trying to keep the source and target databases in sync during the migration process.
Fujitsu first experienced this problem when undertaking an on-premises Oracle-to-PostgreSQL on Amazon Aurora migration. The main source of the issue was identified as trying to run the CDC directly from the on-premises database to AWS over the internet.
To resolve the problem, Fujitsu used Oracle Data Guard to create a staging copy of the database in AWS. Oracle Data Guard has functionality to keep Oracle database instances in sync.
Fujitsu then ran the CDC internally in the cloud, mitigating the high levels of local network traffic it had previously experienced.
Figure 1 – Architecture diagram for a staging copy of an Oracle DB instance.
If target database latency is still high after this, batch apply can potentially be utilized. Batch apply consolidates changes from the source table transaction logs into a new table called “net changes.” It then aggregates changes in this table to find the net changes before applying them to the target database.
Batch apply is particularly effective when many operations are being carried out on the same row of a table, or if a high number of transactions are captured in the source database.
Foreign Key and Trigger Constraints
When migrating databases in a full data load, triggers and foreign key constraints can cause issues when creating the target database. Manually disabling these allows the migration to progress without related errors, but this often involves writing large blocks of SQL code.
For a PostgreSQL target database, this lengthy manual process can be mitigated by utilizing a parameter called “session_replication_role.” When setting this role to “replica,” the firing of replication-related triggers and roles is disabled, allowing the database to migrate without errors.
After a full data load, the triggers and foreign keys can be reinstated and kept on for any CDC loading. This process allows PostgreSQL databases to be replicated more efficiently and with reduced possibility of failure.
Tables with no Primary Keys
To run CDC on AWS DMS, it’s generally recommended that your tables contain a primary key. In PostgreSQL, having a primary key allows the write-ahead logs (WAL), which are used by DMS to track changes in the table, to include a “before” image of the database row.
There are occasions where you will be working with tables without a primary key or unique identifier. Fujitsu encountered this problem when operating a backwards migration from PostgreSQL to Oracle, as part of a data warehousing solution.
In this case, there’s a workaround which allows you to run CDC on a table without a primary key. To do so, you must set the table level parameter REPLICATE IDENTITY to FULL. This allows old values of all columns in the row to be logged, enabling CDC to work on the table.
The drawback to this approach is that it creates large amounts of logs. With that in mind, this solution should only be implemented if completely necessary.
Aligning Code to Business Applications
Beyond optimizing the AWS DMS tool itself, it’s important to consider how your migrated database architecture and code will optimize your business applications and functions. It’s easy to overlook the importance of this and focus solely on re-platforming the infrastructure.
One of the key considerations in this process is to consider what data and code is still necessary. Databases often contain huge amounts of unnecessary data, which is both costly and inefficient to store. In addition, there’s a chance your databases contain historic data, in breach of GDPR requirements.
As a result, it’s vital to include resources with business knowledge and executive sponsorship when migrating your database architecture. The aim of a migration is to reap the benefits of the cloud, so it does not make sense to solely lift and shift your current architecture as-is.
Delivering at Scale
Often, the benefits of database modernization and migrations are realized when these are delivered across a business rather than on a piecemeal basis. Having the correct strategy, management workflow, and a repeatable process helps drive real benefits.
AWS Partners like Fujitsu have the knowledge and experience to deliver, bringing in multiple tools and technologies to automate each step of the migration and modernization journey.
Manual Migration Steps
Despite AWS DMS’s ability to automate vast amounts of the migration process, it’s almost inevitable there will be data in your databases that requires manual conversion. These artefacts are often functions or procedures which cannot be replicated in the target database.
The manual steps in a database migration are arguably the most important. The ability to understand and convert this data requires a deep understanding of the source and target databases, as well as comprehensive technical and business knowledge.
Again, AWS Partners like Fujitsu have a diverse range of knowledge and experience in these areas and are ideal for carrying out the manual processes of a migration. Fujitsu’s Database Modernization offering packages automated tooling with expert knowledge and experience, enabling customers to reliably migrate their database architecture to the cloud.
Summary
In this post, I discussed methods to combat network bandwidth limitations, as well as some table specific controls that will enable you to deliver large-scale Oracle-to-PostgreSQL migrations using AWS Database Migration Service (AWS DMS). I also covered some considerations to be made when approaching database migrations, which stretch beyond just the tooling.
Fujitsu’s Database Modernization offering covers the end-to-end journey of migration. This starts from discovery and assessment and progresses through to demand management, migration patterns, and finally to target implementation and testing.
To find out more, visit Fujitsu Cloud Service for AWS.
The content and opinions in this blog are those of the third-party author and AWS is not responsible for the content or accuracy of this post.
.
.
Fujitsu – AWS Partner Spotlight
Fujitsu is an AWS Advanced Tier Services Partner that provides innovative IT services and products which drive new value from cutting-edge digital technologies.