Img-Reboot

Streamlining Pricing Engine Automation: Migrating MS Access Processes to AWS

Summary

The client's current Pricing Engine relies on data from MS Access and the third-party service Revionics. With the planned decommissioning of Revionics, there is a need to reduce manual intervention and automate over 30 processes currently managed through MS Access. This initiative aims to streamline operations and enhance efficiency through automation.

Goal

Migrate over 30 MS Access processes to AWS, featuring a custom UI on AWS RDS. This involves using Lambda, Glue, S3, EMR, Step Functions, and ECRs. The migration process includes thorough testing in development and QA environments, followed by deployment using CloudFormation Templates (CFTs). Comprehensive post-deployment support will be provided before the final handover to the client.

Approach

The project involves creating a custom UI supported by a combination of AWS services, including AWS RDS for the database, AWS S3 for storage, AWS Lambda (triggered by S3 events) to initiate AWS Glue for data movement across buckets, AWS SNS for notifications in case of file arrival or data quality check failures, and AWS EMR for running the pricing engine business rules with complex transformations. The EMR clusters' start/stop operations are managed through Jenkins.

The system facilitates user access via registered domain names for different environments: DEV, QA, and PROD. An application load balancer routes incoming traffic to the appropriate front-end or back-end clusters based on the domain names.

Requests for the dev application API are routed to the DEV cluster of the Node application on AWS ECR, while requests for the dev application are routed to the DEV cluster of the React application on AWS ECR. This routing is similarly applied for the QA and PROD domains.

Incoming requests undergo Azure AD authentication via Okta SSO, granting authenticated users access to the dashboard. The Node application contains microservices to execute diverse business logic. In the backend, Node interacts with various AWS services like AWS Step Functions for workflow orchestration and AWS Lambda for CRUD operations on the database.

Sensitive data, such as database usernames and passwords, is securely retrieved from AWS Secrets Manager, while essential URLs for AWS services are obtained from AWS Parameter Store within the Lambda layer. Correct routing to read-write replicas is ensured through RDS Proxy services, which establish connections with the RDS database. End-users are notified of task completion or failures via AWS SNS notifications.

For logging system information for the backend/Node, Scalyr is deployed on AWS clusters, collecting log files (e.g., info.log) generated at the application level using Winston logger. For logging and monitoring system information for both the backend/Node and frontend/React, New Relic is integrated with the application, collecting vital application metrics and displaying them on a monitoring dashboard.

Below are architecture diagrams for Web and Data, clearly illustrating all the moving parts and service interactions.

Tools and AWS Services Used

  • Amazon EMR
  • AWS Lambda
  • AWS Glue
  • Amazon S3
  • AWS Step Functions

 

Key Benefits

  • Modernized processes
  • Custom UI development
  • Robust testing and deployment

Results

Icon Boost

~30 MS Access processes migrated to AWS

Icon Boost

Developed a custom UI backed by AWS RDS

Icon Boost

Achieved a seamless production deployment using CloudFormation Templates (CFTs)

Results

Talk To Us