How the AWS Schema Conversion Tool Drove Trimble’s Database Migration Successes

By Todd Hofert, Director of Infrastructure Operations—FSM at Trimble Inc.

The situation
Recently, the Infrastructure Operations Group for Trimble’s Field Service Management division embarked on an aggressive initiative to migrate their privately hosted SaaS offerings to Amazon Web Services (AWS). The division faced the need for a hardware refresh, continuing cost reduction pressures, and a desire to align legacy offerings with the company’s next generation platform on AWS. In response, the Trimble Infrastructure team established a comprehensive migration plan that started with its data warehouse solutions.

Cost reductions, availability, and scalability were the primary drivers for the AWS initiative. Trimble decided to optimize their current licensing costs by migrating from Oracle to an open source database platform. Trimble also moved to help guarantee reliability and lower operational support overhead by using Amazon RDS to run the database.

Selecting a database
The Trimble Operations and Database Development teams invited an AWS team to help evaluate the complexity of the database platform migration away from Oracle. At the center of this evaluation was the AWS Schema Conversion Tool (AWS SCT) Assessment Report.

Our team at Trimble used AWS SCT to check the “distance” between the source Oracle database and alternative targets among the Amazon Relational Database Service (Amazon RDS) open-source database engines. Trimble also had a second criterion, the ability to work with existing ETL tools (Informatica) and reporting products (Microstrategy). We decided that this approach was necessary to allow the project to meet timelines and business expectations while also avoiding scope creep.

We saw that either database allowed Trimble to continue using existing front-end solutions. However, the AWS SCT assessment showed that RDS for PostgreSQL had a smaller gap to the Oracle source database, leading us to select that as our target database. The Schema Conversion Tool Assessment Report provided a clear picture of where effort was required to deal with functional gaps between the current database platform and RDS for PostgreSQL. It allowed us to plan resourcing accordingly.

The Assessment Report provided Trimble a foundation and framework for soliciting bids against an RFP to engage a third party to perform the bulk of the migration work. This foundation allowed Trimble to focus on other migration activities. As a result, Trimble formed a valuable partnership with OpenSCG, an Amazon Partner, to drive the database migration activities. Trimble maintains that relationship today through OpenSCG’s support offerings and training programs.

The Schema Conversion Tool Assessment Report was the key enabler to understanding the scope of effort for this migration. What we originally thought to be a largely manual set of tasks that no one was particularly excited about became a very straightforward process. This evaluation shaved months off the projected timelines.

The migration
Trimble solicited bids from four prospective vendors. After reviewing the bids, Trimble came to an agreement with OpenSCG based on their expertise and depth of knowledge compared with the other vendor responses. Trimble tasked OpenSCG with resolving the identified functional gaps. OpenSCG also wrote migration scripts to create the PostgreSQL database schema and move the data from the source database to the target database. Trimble staff supported these efforts and took responsibility for all tasks associated with configuration and validation of the front-end applications.

The project was broken into two subprojects. First, the teams focused on the North American hosted migration, followed by the European hosted migration. The North American database was 6.5 TB in size with an average growth rate of around 22 GB per month. The European database was 4.6 TB with an average growth rate of 33 GB per month.

The teams used the database replica set on the existing privately hosted disaster recovery site as the source database for the migration. They deployed a PostgreSQL database VM and a data migration VM alongside it. These VMs used the OpenSCG-provided schema and scripts to migrate the production database into the target PostgreSQL database. On successful completion of the migration, we used an AWS Snowball to ship the exported PostgreSQL database to AWS. At AWS, the exported database was imported into the target RDS for PostgreSQL instance. This process was performed twice in each geographic location. The first iteration seeded test data; the second one created the production dataset after validation.

The cutover
The existing ETL process maintained sync between the live privately hosted data warehouse solution and the preproduction RDS for PostgreSQL solution. A thorough period of QA, functional and performance testing, and data integrity validation identified a number of issues. The teams resolved them well in advance of the planned go-live dates. The most significant challenges were related to the performance of the ETL process, and also to performance issues on some reports.

The due diligence of the implementation team resulted in an unremarkable cutover. This cutover was scheduled logically between ETL runs and report runs; thus, it was completely transparent to end users.

Results
Trimble FSM has since fully decommissioned their legacy data warehouse stacks. Trimble is in the process of completing a full exit from their colocation providers.

This data warehouse migration represented the first three phases of a larger project to move all hosted solutions into AWS. To date, Trimble has completed the migration of the two production data warehouse stacks, their respective development environments, and their North American and European legacy production application stack. Trimble is currently in the final phase of migrating their application development environments projected to be complete by year end.

Projections show these projects should reduce Trimble’s direct infrastructure costs to less than one-quarter of their privately hosted infrastructure. Planning for future initiatives that use additional AWS services are under way. Trimble expects these to reduce operational overhead even further.

Summary
The key takeaways from this project are these:

Use all available resources to conduct thorough due diligence to define the project roadmap. Trimble made use of the AWS Schema Conversion Tool and the advice of their AWS account team. These proved to be key success factors.

Don’t hesitate to explore the use of external resources and expertise to supplement the project. Working with an outside vendor to complement the internal team cut project completion times by months.

First consider your ideal end state, then work back toward what is feasible if necessary. Many felt a platform migration off of Oracle was a monumental and unattainable goal. In the end, this concern turned out to be unfounded.