Solution
EposNow partnered with Kloia, an AWS Premier Consulting Partner with AWS Competencies in DevOps and Microsoft Workloads , to lead the database modernization. The engagement followed a structured, phased approach:
1. Initial Assessment & Strategy: Kloia conducted a comprehensive analysis of EposNow's SQL Server database schema using the AWS Schema Conversion Tool (SCT) and outlined a tailored modernization roadmap covering:
- Database Schema Analysis
- Cloud Architecture Design
- Dual Database Operation and Incremental Migration Strategy
- Data Migration Strategy
- Performance Optimization
2. Database Schema Analysis: The SCT analysis revealed that 97% of the 1,649 database storage objects and 74% of the 1,879 database code objects could be automatically converted to Aurora/RDS with minimal changes. The remaining 3% of storage objects and 26% of code objects (primarily stored procedures) required medium to complex manual actions.
We have categorized the stored procedures into 3 subcategories: Complex, Simple and Manual:
Stored procedures were categorized into three groups:
- Simple SPs, Converted automatically using Kloia's open-source SP2code tool, or moved to .NET application level.
- Complex SPs, Manually rewritten in .NET to ensure full compatibility with the PostgreSQL environment.
- Manual SPs, Handled individually due to unique complexity.
3. Cloud Architecture Design: Kloia designed a cloud-native architecture leveraging Amazon Aurora for PostgreSQL as the managed database service, featuring automated backups, multi-AZ deployment for high availability, and seamless integration with EposNow's existing applications.
4. Zero-Downtime Migration Strategy: To meet strict business continuity requirements, Kloia employed a parallel run (Strangler-Fig) strategy:
- The legacy SQL Server and the new Aurora PostgreSQL database ran in parallel simultaneously.
- A decision layer at the application level (powered by Redis caching) routed each tenant's queries to the appropriate database.
-Migration proceeded incrementally, one tenant at a time, minimizing risk and avoiding downtime.
- AWS Database Migration Service (DMS) enabled continuous real-time data replication from SQL Server to PostgreSQL throughout the transition.
5. Performance Optimization: After migration, kloia optimized the PostgreSQL environment by fine-tuning complex queries, implementing caching strategies, and configuring real-time monitoring via Amazon CloudWatch to ensure system reliability and responsiveness.
