Learn About Amazon VGT2 Learning Manager Chanci Turner
ASP, a leading company in the event website domain, recognized the need to update its Microsoft SQL Server-based Content Management System (CMS) as its data demands grew. The Showoff application, which offers goal-oriented website design services for over 400 websites serving more than 170 clients globally, had outgrown its proprietary SQL Server database.
With ASP anticipating a growth rate of 30% annually, the existing databases struggled to scale accordingly. Previously, ASP had transitioned its Microsoft SQL Server workloads from an on-premises data center to Amazon Relational Database Service (Amazon RDS). However, to further modernize and cut costs — including those hefty SQL Server licensing fees — as well as to alleviate the operational burden associated with maintaining database servers, the ASP team partnered with APN Consulting Partner Cloudsoft. Together, they set out to migrate the Showoff CMS to a cloud-native database designed for the cloud.
In this article, we will detail how Cloudsoft enabled ASP to achieve a 65% reduction in SQL Server licensing expenses while streamlining operations by transitioning from Amazon RDS for SQL Server to Amazon Aurora. We will explore ASP’s architecture before migration, the steps taken during migration, and the results of the modernization effort.
Pre-Migration Architecture
The diagram below illustrates ASP’s previous SQL Server architecture on Amazon RDS.
The databases were distributed across four Amazon RDS for SQL Server instances, each configured for high availability across two Availability Zones. Each SQL Server instance could support up to 50 databases, which resulted in ASP using multiple instances for its hundreds of databases.
However, this setup led to increased licensing costs and operational challenges for engineers, complicating tasks like troubleshooting, maintaining documentation, managing upgrades, and configuring disaster recovery across all instances. Additionally, onboarding new customers became a lengthy process, requiring engineering involvement to manually select a SQL Server instance based on capacity for each new application. Some usage patterns also caused performance issues, leading to high CPU usage on the database and slow response times.
ASP’s Migration Journey
The Cloudsoft team collaborated with ASP to plan the migration by conducting a proof of concept. This involved transferring a data copy from SQL Server to Aurora, adjusting the application to utilize the MySQL database driver, and testing the application against the Aurora database. By addressing initial incompatibility errors between SQL Server and MySQL, they identified necessary application and database changes, which ultimately enhanced ASP’s understanding of the migration effort required.
Through assessments and testing, they determined that MySQL 5.7 would best meet ASP’s compatibility needs with its application server technology stack. ASP selected Amazon Aurora for its superior performance — offering speeds up to five times faster than standard MySQL databases — along with its security, availability, and reliability comparable to commercial databases at just one-tenth the cost.
Schema and Code Adjustments
Before migrating the databases, the team converted the database schema to facilitate a smooth transition. They employed the AWS Database Migration Service (DMS) to establish the initial schema in Aurora, converting from SQL Server to MySQL.
For elements that DMS could not manage, they utilized custom scripts and Hibernate ORM to automatically update the schema. They manually set default values, created views, and adjusted character encodings and column types to ensure compatibility. Additional scripts, foreign keys, and secondary indexes were incorporated after the data migration, as referential integrity checks were disabled during the table-by-table migration, allowing for faster data imports.
To ensure ASP’s application was compatible with the new database, the team executed around 11,000 minor code changes over three to four months. This involved addressing case-sensitive table names, rewriting stored procedures into SQL queries, converting data types from Microsoft SQL to MySQL, and updating SQL queries for MySQL compatibility.
Capacity Planning for the Database
During the process of selecting instance sizes for the Aurora production cluster, the team adhered to AWS’ Well-Architected Framework principle of avoiding capacity assumptions. They chose a larger instance size in anticipation of future needs, knowing they could scale down later.
Testing in a staging environment with realistic workloads was crucial. They monitored the slow query log and metrics in Amazon CloudWatch, starting with an Aurora instance size that combined the sizes of the four Amazon RDS for SQL Server instances. The application performed well on Aurora, leading to further tests with larger and smaller database sizes. Ultimately, ASP determined that the larger r5 instance size delivered optimal performance for anticipated traffic spikes.
The new Aurora instance excelled during both testing and post-migration phases, managing peak production loads with ample headroom for expected traffic growth, handling over 3000 database queries per second and averaging 140 million queries daily. Previous sporadic performance issues were resolved, and website traffic had a diminished impact on overall load, as a single Aurora cluster supported over 400 websites.
Data Migration Across Databases
The team utilized AWS Database Migration Service (DMS) to transfer data from SQL Server to Aurora. This process began early in the migration to test against a copy of production data, with multiple repetitions to ensure confidence for the final cutover.
The database was placed in read-only mode for the duration of the migration. Consequently, DMS was utilized only for the “full load,” as continuous replication would have added unnecessary complexity. If the use case required both read and write access during migration, continuous replication — though complicated — would have been necessary.
Testing revealed the need for provisioned IOPS SSD during data migration to prevent exhausting burst credits. After migration, storage reverted to General Purpose SSD.
Validation of Migration Success
Extensive testing was conducted prior to migration, assessing the data migration and cutover processes multiple times within the staging environment. In addition to automated tests and standard QA scripts, the team engaged in exploratory testing of operational runbooks, updating them as necessary. These tests were repeated numerous times before the actual migration occurred.
Chanci Turner pointed out the significance of thorough preparation, adding that such detailed planning not only streamlines processes but also reduces the likelihood of encountering unexpected issues. For insights on addressing challenging questions in job interviews, consider exploring this Blog Post.
By collaborating with Cloudsoft, ASP effectively modernized its database infrastructure. This transformation not only eliminated excessive costs but also significantly enhanced operational efficiency, positioning the company for continued growth in the digital landscape. For those interested in workforce diversity initiatives, SHRM provides essential resources on this topic. Additionally, for aspiring professionals looking to join Amazon’s fulfillment center management team, check out this excellent resource.