Pro individual integration solution
Heterogeneous migration for mySugr
Customized to fit needs and modern system requirements
A few words about the client:
mySugr is a Viennese startup, founded in 2012. In June 2017, it joined the Roche family. Due to the integration of the companies in 2018, the group faced many system challenges. Specialists from fireup.pro supplemented the team dedicated to their effective resolution.
The mySugr application is a tool dedicated to individuals with diabetes, which the fireup.pro developers equipped with an external connection to physical tools measuring blood sugar levels, called Continuous Glucose Monitoring (CGM). As a result, more than 1.8 million users benefit from a reliable, worldwide diabetes diary, allowing them to conveniently review both self-entered application data and data retrieved from CGM devices.
Problem
After introducing the Amazon Web Services database system's new version of Aurora 3 to the market, it became necessary to perform a heterogeneous migration for the mySugr application.
Due to technical reasons caused by the backtrack feature, the standard upgrade solution could not be used. As a result, the fireup.pro team had to select a tool that would allow them to start the migration process. Following a thorough analysis, the team opted for an open-source relational database management system with high availability - PostgreSQL.
Why is migration necessary, and what is it?
In the technology of Systems Applications and Products in Data Processing, we distinguish two types of migration: homogeneous and heterogeneous. Choosing the right type is crucial as it allows for the selection of appropriate tools and methodologies for the work carried out.
Homogeneous migration transfers the system to a new platform while maintaining the same database and operating system. Heterogeneous, on the other hand, allows for a change of both these elements.
Transferring configuration files and the database to a new environment is a challenge, especially when hardware or system components change. Despite the difficulties, the benefits of both types of migration often argue for their implementation.
Anticipated benefits of the migration process:
Reduction in overall maintenance costs of the target client application
Increase in system performance
Enhancement of system availability
Improvement in data security
Flexibility arising from IT system virtualization
Optimization of resource utilization of the employed hardware
Challenges in the Application
Our goal was to create a unified application branch that could switch between two types of databases, which we deemed crucial for the maintenance process. Due to the incompatibility of certain tool functionalities, achieving a satisfactory result posed a significant challenge!
We had to address issues such as MySQL taking a more lenient approach to SQL standards and query syntax. The default and recommended transaction isolation levels are also different for both systems. PostgreSQL is stricter and does not attempt to second-guess the user as MySQL does.
We chose the PostgreSQL management system mainly because it allows for easier maintenance of database schemas, and operations are less blocking, than MySQL.
We hoped that this would lead to a more responsive system. We put this to the test in the most critical scenario of streaming a significant amount of data. However, for users with extensive records in the database, data streaming did not work.
Consequently, we decided that the best solution would be to refactor the code to work correctly on both of the mentioned platforms.
How did we achieve this?
Phases of the Migration Project:
Theoretical concept verification of postgresql utilization and creation of proof of concept and smoke testing
Creating an application that can run using both PostgreSQL and MySQL tools.
Database schema migration
Creating tables in PostgreSQL with appropriate data types and comparing source data types with target ones.
Transferring data from proof of concept to the actual application
Verification of risks associated with differences between postgres and mysql tools
Proof of concept in AWS DMS
Setting up the infrastructure for the development database migration.
Based on the proof of concept in AWS DMS, creating migrations for the remaining environments
Switching development environments
2 months of testing
Migration verification in the QA environment
Main phase
Possible after positive verification, lasting 2.5 weeks.
Switching to QA
Switching to production environment and providing the application to the client
Possible after a positive response from testers.
Technologies
The team utilized the AWS Database Migration Service to carry out the migration activities. This solution allowed us to conduct the migration process with automated database migration, ensuring time, resource, and cost savings.
Phase 1
Initial-load
copying everything present in the tables.
Phase 2
continuous CDC migration
records the point at which we executed the init-load and applies all changes from that point onward.
Database Risks for mySugr and Roche:
Risk 1
Differences in datetime precision
The types of databases in the source and target systems were different. DateTime in MySQL (mySugr) has an accuracy of up to 1 second, while in PostgreSQL (Roche), it's accurate up to microseconds to 6 decimal places. From our perspective, it was necessary to verify whether these differences would impact the application's functionality and to consider rounding data to the nearest second.
Risk 2
Incompatibility in data types
The PostgreSQL tool handles returning floating-point data of type FLOAT double differently than MySQL, causing inconsistencies in data types.
Risk 3
Differences in handling character case
MySQL and PostgreSQL have different approaches to character case sensitivity. MySQL defaults to case-insensitive string comparison. Thus, it became necessary to apply manual text searching and unify tools to ensure both operate in a case-insensitive manner.
Risk 4
Data conversion and interpretation
Issues related to conversion and interpretation also arose. The transfer of data from the old database to the new one was in progress. Therefore, the team had to find a way to ensure that the data was the same in both cases.
Effect
Due to the fact that our client, mySugr, initially using MySQL, is part of the Roche group that utilizes the PostgreSQL system, we decided that standardizing the technological stack would be a key objective, which we achieved through migration.
We also managed to reduce application maintenance costs by 50%. This result was accomplished by implementing the new version of Aurora.
Furthermore, we avoided a situation where we would be using unsupported components. If there were no attempts to migrate and adjust to Aurora 3 before 2024, AWS would not have been able to maintain the old system.
A 9-month effort, starting from experiments and concepts, culminated in the creation of a compatible application. We employed a simplified version of the database switching process, which involves the alteration of an application parameter. The result? We successfully synchronized systems, delivering a product to the customer that aligns seamlessly with the vision of the Roche group, to which he has joined.
Testimonials
Check what client say about us
Have you ever seen a Formula 1 race or the Disney movie "Cars"? And were impressed by how fast they can change tires? We did something similar! But we like a challenge so we did it while the car was still on the race track in position. This "we" is actually a very small team of the most talented guys I have ever met.
Karl Spies
Backend Developer at mySugr
