Case Study
Migrating to Amazon Aurora Postgres helped Reliance Steel & Aluminum Co. achieve Near real time Insights, Improved Scalability and Better Data Quality

Overview

Reliance Steel and Aluminum Company (RSAC) is North America’s largest metals service center company, offering 100,000+ products and value-added processing and serving over 125,000 customers primarily by providing metals processing, inventory management services, and quick delivery.

Application Scenario

RSAC had embarked on a journey to move up the information maturity curve. In this regard, RSAC had designed and architected a generic data model to build an AWS Cloud-based Data Warehouse and migrate various ERP systems across the group’s companies into AWS Cloud in a phased manner.

They had multi-tier architecture with hundreds of applications, and the daily changes represented a fraction of the total data volume. This led to data latencies and became a bottleneck for RSAC in creating Enterprise Master Data Management, ETL pipelines for Analytics and Data Quality.

To solve this issue, RSAC approached Agilisium to envisage a serverless solution for easy maintenance and to improve the overall efficiency of the system.

Business Challenges

  • On-prem and legacy data models were modifying/overwriting record data all the time. As a result, RSAC couldn’t have a single source of truth.
  • RSAC required an immutable data storage/system where they can run Adhoc querying, reporting, and third-party integration.
  • RSAC needed real-time transactions to data warehouses and low-latency data transfer to operational and analytics users with low production impact.
  • Batch loads and periodic reloads with the latest data take time and often consume significant processing power on the source system

Solution Highlights

Agilisium, along with the support of RSAC stakeholders, understood the current architecture in place and recommended AWS Lambda as the serverless solution. AWS Lambda was leveraged to migrate the on-prem SQL Server to Amazon Aurora Serverless.

AWS services such as AWS Lambda, AWS Secrets Manager, Cloud Watch Rule, Aurora RDS (PostgreSQL), and SNS were leveraged to finely integrate the required technical components. Below is the architecture diagram of the solution implemented in RSAC.

Data Migration
  • AWS Lambda was put in place to handle and process the core data flows.
  • CloudWatch rule triggers the Lambda process every 5 minutes.
  • Lambda connects to an on-prem SQL Server and extracts CDC data.
  • Simultaneously, Lambda applies business rules within the extracted CDC data and stores the processed records to Amazon Aurora tables.
  • EMR Spark-based framework is developed to run a distributed application environment.
Migration Readiness
  • Migration Readiness was done using Agilisium’s Readiness template.
  • Gathered preliminary information which was required for migration readiness and is segregated into success factors, workload, and security.
  • Helped us to identify the type and volume of workload, stakeholders, and security-related information.
Performance Consideration
  • Measured and evaluated the optimum capacity units like memory size, the startup time for lambda.
  • Controlled dependencies and Minimized deployment package size, which in turn would reduce the amount of time taken for the deployment package to be downloaded and unpacked ahead of invocation.
  • The following metrics were captured and monitored as part of the Performance metrics.
    • Service Response Time
    • Database Query Throughput
    • Memory usage (CPU Utilization)
Reliability
  • The Custom Lambda framework had the capabilities of fault tolerance and failover.
  • Failover decision: If the system goes through a failure, the restart process gets initiated from the point of failure as instructed in the custom Lambda framework.
  • Disaster recovery was in place by leveraging parallel execution techniques.
AWS services used:
  • AWS Lambda
  • Amazon Aurora
  • AWS CodeCommit
  • AWS CodePipeline
  • AWS CloudTrail
  • Amazon CloudWatch
  • Amazon SNS
  • AWS Identity and Access Management

To fulfill SocialHi’5 need for a client self-service portal that was also easy to maintain, Agilisium’s 5-member expert team built a custom web application with a heavy focus on the visualization of campaign outcomes. They also developed in parallel a DevOps process to maintain, scale and operate this portal.

Web Application Architecture


A variety of AWS services and some open source technologies were used to build and run the web application. The web layer used the PHP framework, included a login and authentication system, and used AWS QuickSight to render its outcome dashboards.

The app layer was built on Python, and the backend services were run on Elastic Container Service (ECS) dockers with Auto Scaling and Auto Load Balancing (ALB) to ensure high availability of the portal. The database was run in a private subnet and used RDS MySQL as the database service.

DevOps Process:

As mentioned earlier, SocialHi5 necessitated that the solution offered was easy to maintain, scale, and operate. To that end, Agilisium’s DevOps engineers developed a 2-part DevOps process focusing on

  • CI/CD for web application development
  • Infrastructure Provisioning for maintenance.

Continuous Integration/Continuous Deployment (CI/CD Process)

All application (Web & App Tier) maintenance was articulated via AWS’s Code Pipeline. AWS’s Code Commit, Code Deploy, and Code Build services were invoked to automate the enhancement and maintenance of the self-service portal.

CI/CD Process Flow: Web Tier


CI/CD Process Flow: Web Tier


Infrastructure provisioning

All infrastructure was hosted on an exclusive SocialHi5 Virtual Private Cloud (VPC), to add an extra layer of confidentiality. AWS CloudFormation templates were used to spin up and maintain a host of AWS services utilized for the self-service portal.

Serverless Web application hosting: EC2, ECS, RDS, S3, SSM, VPC, NAT Gateway, ALB with Autoscaling Group, LAMBDA, Certificate Manager, Route53 were some of the services used to get the portal live.

Security: Web Application Firewall (WAF) was used with Cross-site scripting, Geo match, and SQL injection rules to protect from common cyber threats in conjunction with the AWS inspector service.

Monitoring and Logging: CloudWatch, OpsWorks, Config & Inspector services were also invoked to cover configuration management, logging, and monitoring of the application and infrastructure.


Results and Benefits
  • Near-real-time insights to data teams and dashboards
  • Data propagation & synchronization
  • Better Data Quality – Captured real-time changes to data record and metadata management
  • Improved scalability and performance of the system
  • Overall TCO is reduced