Built an immutable data storage solution to simplify reporting and third-party integration for Reliance Steel and Aluminum Co.

AWS Serverless near real time architecture for migrating on prem SQL Server data changes to Amazon aurora

Highlights

Better Report experience
Improved database performance Decreases the database management cost
Reduced process burden on engineers
Improved scalability and performance of the system

Client Profile

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.

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.

Agilisium Solution

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.

Tech-stacks Used

lambda
Amazon Aurora
AWS CodeCommit
AWS CodePipeline
AWS CloudTrail
AWS Cloudwatch
Amazon SNS
AWS Identity and Access Management

Business outcome

  • 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
Get the Most Out of Embedded Analytics with Amazon Opensearch
From data rich visual analytics to interactive dashboards that make data fetching a piece of cake, Team Agilisium is with you, every step of the way.
Talk to Us
Got a question? Don’t hesitate to give us a call today or shoot us an email. 
Please enter a business email
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.