Achieving Optimal Efficiency through Database Modernization

Introduction

Our client, a leading global container shipping business, operates an extensive network of 250+ shipping routes connecting over 400 ports across 160 countries. Seeking a streamlined migration solution, they aimed to adopt open-source alternatives for their proprietary databases while minimizing code changes. However, the challenge was compounded by limited resources and a lack of technical documentation, making it difficult to conduct a comprehensive feasibility study.

Application Description

The client relied on a custom-built .NET application utilized by branch users to process vessel arrivals at ports. During peak usage, the application handles approximately 35% of its user capacity. However, the absence of performance benchmarks and functional documentation, coupled with the need for multilingual support, posed additional complexities.

Migration Approach: To address the migration challenge, our team initiated an initial discovery and assessment phase, collaborating closely with the application and database teams. The objective was to assemble a robust business case for the migration. During this process, we explored various options, and one tool stood out as a game-changer: Babelfish.

Project Execution: Driven by the promise of minimal code changes and accelerated migration, we adopted Babelfish—a cutting-edge tool designed for seamless migration from SQL Server to PostgreSQL. With its high compatibility and performance capabilities, Babelfish emerged as the ideal solution to meet the client's requirements.

To ensure a successful migration, we devised a comprehensive program plan. This involved revalidating the business case, estimating the post-migration run and maintenance costs, selecting appropriate migration and testing tools, obtaining sign-off on the migration plan and target architecture from the client, and securing proof-of-concept funding from AWS.

The migration process encompassed meticulous planning, thorough testing, and robust cutover and fallback plans. We first migrated the UAT environment to mitigate production risks and conducted extensive testing to ensure seamless functionality. By benchmarking the source SQL Server and target PostgreSQL on Aurora for performance, we gained valuable insights for side-by-side comparison and issue resolution.

Post-migration, we focused on system monitoring, backup implementation, and day-to-day operational readiness. We updated standard operating procedures (SOPs) and playbooks while facilitating knowledge transfer to the application and managed services team for sustained maintenance.

Challenges and Solutions

As with any heterogeneous migration, challenges arose. Varbinary data corruption was encountered during migration using DMS, requiring an alternative approach. We innovatively developed a small application that efficiently queried and migrated PDF documents from the SQL Server to PostgreSQL.

Addressing the migration of bulk insert queries presented another hurdle. Although Babelfish 1.0 lacked support for this, we bypassed the limitation by crafting stored procedures using the copy command.

Given the application's multilingual nature, we encountered display issues with unsupported collations on PostgreSQL. Our team rewrote the affected stored procedures with native collation support, rectifying the problem.

Lastly, performance issues arose with self joins in PostgreSQL, prompting us to rewrite numerous queries and stored procedures for optimized performance.

Outcome

Through diligent efforts and strategic deployment of Babelfish and related tools, we successfully completed the migration project within a relatively short timeframe. The client now enjoys substantial cost savings, amounting to over $21,000 per year, while experiencing improved performance in over 70% of their queries post-migration. Babelfish and our expertise proved instrumental in delivering a seamless migration and empowering the client with a modern, open-source database solution.

has context menu