Database migration to AWS Aurora PostgreSQL enables better batch job performance at lower costs for a global commercial truck manufacturer.
A leading designer and manufacturer of large commercial trucks wanted to modernize their data architecture. The client hosted their databases in a traditional on-prem environment, which was becoming expensive and difficult to scale. The database was loading 300,000 records per day, and its batch job performance was declining. This affected downstream processes and degraded the performance of reporting tools and applications that used the data.
The client engaged Birlasoft to modernize their data architecture, by migrating their on-prem Teradata deployment to a modern cloud-based database service. The underlying strategy that drove this move, was the need to move to an on-demand, as-a-service model to lower the management overheads, reduce the TCO, and shift to a more agile data architecture.
The Birlasoft team deployed data migration and data architecture experts to migrate the on-prem Teradata database to AWS Aurora PostgreSQL and reconfigure their production environment to point to the cloud-based database. The engagement enabled the client to achieve over 13% return on investment (ROI), and a 40% enhancement in batch job performance.
Read the details of this engagement below.
The Challenge
#1. Growing cost of supporting the database on the Teradata platform
Teradata databases enable high-performance parallel processing, which requires specialized hardware that is typically expensive to source and maintain. In addition, our client was incurring significant licensing expenses and required highly skilled employees to run and maintain the Teradata architecture.
Moreover, upgrading the database to new versions called for significant investments, which significantly restricted the client’s IT modernization initiatives. This triggered the need for a more cost-effective PostgreSQL database service, which would eliminate the need to maintain on-prem infrastructure, and would be cheaper to support, maintain, and upgrade.
#2. Inefficient scaling and suboptimal reliability of the on-prem database
It is a well-known fact that Teradata databases are not only expensive to scale, but also difficult to scale. While on-prem databases require high-performance hardware components to scale the database, they also call data redistribution across the nodes and query optimization to maintain performance.
Data redistribution is typically required to scale horizontally, and in some cases, downtime will be unavoidable. This often leads to overprovisioning, which leads to high upfront investments that lie unused for a long time.
In addition to facing the above scalability challenges, our client was also finding it difficult to ensure the reliability of the deployment. This was primarily due to the complexity of the underlying tech stack, and problems with maintaining availability around the clock.
#3. Difficulty optimizing database performance due to growing size and complexity
Our client’s database was over 315GB in size and loaded 300,000 records per day on average. It housed 210 stored procedures and 44 custom functions and supported over 50 views. Such complex databases require the use of right indexing strategies, efficient data distributions, and sophisticated querying strategies to achieve efficient performance.
Our client’s database performance was declining, and consequently, downstream application and reporting performance suffered too. The client deployed a team of 14 members working from two locations to optimize the database for performance, which was adding significant costs.
These challenges led the client to consider the migration of the Teradata database to a cloud-based database service.
The Solution
To mitigate the above challenges, the client engaged Birlasoft to migrate their on-prem Teradata database to AWS Aurora PostgreSQL. This process entailed assessment of data sources, schema conversion, migration of the database tables, post-migration validation, and pointing the production environment to the cloud-based database.
Read the details of the migration initiative below.
#1. Data assessment to devise a migration approach and roadmap
Birlasoft began with a thorough assessment of the existing database and deployed its data migration experts to discover the sources of data, measure the database size, and map dependencies. Following this, the order migration of data was prioritized based on business-criticality, dependencies, and other factors.
The Birlasoft teams collaborated extensively with the client’s stakeholders and PoCs to obtain signoff on the determined migration strategy.
#2. Schema conversion, data extraction and transformation, and quality assurance
After obtaining signoff from the client, our teams analyzed the Teradata database schema using DataSwitch, and generated a PostgreSQL-compatible schema for the database. Following this, the data from external systems, vendor-managed environments, and other sources was extracted.
This data was analyzed for quality and completeness to ensure the migration of quality data into the target system. Birlasoft leveraged the DataSwitch migration tool for analyzing quality. Finally, the data was transformed to prepare it for migration.
#3. Database migration with Apache Airflow, and reconfiguration of ETL jobs and APIs
Once the data was ready to be migrated, the Birlasoft team devised the workflows to migrate the tables to AWS Aurora PostgreSQL. After configuring the AWS Aurora environment to host the database, we leveraged Apache Airflow to orchestrate the migration of all the tables, views, and other Teradata objects to the target environment.
Following this, we reconfigured the existing ETL jobs and Pega APIs to redirect data from the on-prem database to the cloud database.
#4. Post-migration validation and testing, mock runs, and production switching
Finally, the migrated database was tested extensively to validate the completeness and integrity of the migrated data. Then, SQL Server Reporting Services (SSRS) was integrated to the PostgreSQL database, and SQL Server Integration Services (SSIS) jobs were configured to run with the cloud database.
Following user acceptance tests (UATs) and mock runs, the production environment was finally configured to point to the migrated database.
The Impact
Migrating the database to AWS Aurora enabled the client to retire their on-prem deployment, reduce the database management overheads, and achieve enhanced performance. Here are the key benefits that the client achieved through this migration:
1. 40% improvement in batch job performance
AWS Aurora PostgreSQL is a fully managed, performant database service, which offers significantly improved performance at lower costs. Our client was able to achieve 40% better batch job performance with the migrated database.
2. 25% enhancement in code reusability
With infrastructure and platform abstracted away, developers could now focus on writing app-specific code in tune with the business logic. This enhanced code reusability by 25%.
3. 50% better application performance
The cloud-based database served downstream applications faster, improving the performance for multiple applications.
4. 13.44% ROI with the shift to cloud
Lastly, the fully managed database enabled the client to retire on-prem systems and optimize resource utilization. This not only lowered the TCO associated with the database but also contributed to a 13.44% return on investment.
