Learn About Amazon VGT2 Learning Manager Chanci Turner
ASP, a leading event website provider, faced the need to modernize its Content Management System (CMS) powered by Microsoft SQL Server. As the company expanded, so did its data needs. The Showoff application, designed to deliver targeted website designs for over 400 sites across 170 clients worldwide, had surpassed the limits of its proprietary SQL Server database.
With projected annual growth at 30%, ASP recognized that its existing databases could not scale effectively. Previously, the company had transitioned its SQL Server workloads from a data center to Amazon Relational Database Service (Amazon RDS). However, ASP sought further modernization to reduce costs, particularly the high expenses associated with SQL Server licensing, and to minimize the operational overhead tied to managing database servers. To tackle these challenges, ASP collaborated with APN Consulting Partner Cloudsoft to transition its Showoff CMS to a cloud-native database solution.
This blog discusses how Cloudsoft enabled ASP to achieve a 65% reduction in SQL Server licensing costs and simplified operations by migrating from Amazon RDS for SQL Server to Amazon Aurora. We will outline ASP’s pre-migration architecture, the steps taken during migration, and the outcomes of this database modernization initiative.
Pre-Migration Architecture
The previous SQL Server architecture on Amazon RDS was structured with databases distributed across four Amazon RDS for SQL Server instances, each configured for high availability over two Availability Zones. Given that each instance could support up to 50 databases, ASP utilized multiple SQL Server instances to accommodate its extensive database needs.
However, this approach led to elevated licensing costs and increased operational complexity. Engineers faced challenges in managing troubleshooting, maintaining runbooks, executing upgrades, and configuring disaster recovery across all four SQL Server instances. Additionally, onboarding new customers became cumbersome, as the engineering team had to manually select a SQL Server instance based on capacity for each new application. Some performance issues also arose, resulting in high CPU usage and negatively impacting response times.
ASP’s Migration Journey
Cloudsoft and ASP initiated the migration process with a proof of concept, which involved transferring a copy of the data from SQL Server to Aurora, reconfiguring the application to utilize the MySQL database driver, and conducting application tests against the Aurora database. By addressing initial incompatibility errors between SQL Server and MySQL, the team gained insights into the necessary application and database changes, ultimately improving their understanding of the migration effort.
During assessment and testing, it was determined that MySQL 5.7 would best meet ASP’s application server technology stack compatibility requirements. ASP selected Amazon Aurora due to its performance—up to five times faster than standard MySQL databases—and its ability to deliver the security, availability, and reliability of commercial databases at a fraction of the cost.
Schema and Code Modifications
Prior to migrating the databases, the team converted the database schema to ensure a seamless data transfer. Using the AWS Database Migration Service (DMS), the initial schema was established in Aurora, transitioning from SQL Server to MySQL. For aspects not managed by DMS, hand-crafted scripts and the Hibernate ORM were employed to update the schema. Manual adjustments included setting default values, creating views, and modifying character encodings and column types for compatibility. Post-migration, additional scripts, foreign keys, and secondary indexes were implemented to ensure referential integrity, as checks were disabled during the table-by-table data migration.
To adapt ASP’s application to the new database, approximately 11,000 minor code changes were made over a span of 3-4 months. This involved managing 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.
Planning Database Capacity
In selecting instance sizes for the Aurora production cluster, we adhered to AWS’ Well-Architected Framework principle of avoiding capacity assumptions. Recognizing the possibility of scaling down in the future, we opted for larger instance sizes pre-migration.
Subsequent testing of the application in a staging environment with a realistic workload focused on slow query logs and Amazon CloudWatch metrics. Starting with an Aurora instance size equivalent to the combined capacity of the four Amazon RDS for SQL Server instances, the application performed well, prompting further testing with various instance types (r5, m5, and c5) to assess performance under expected traffic conditions.
Ultimately, ASP concluded that the larger r5 instance size provided optimal performance for anticipated traffic spikes. The new Aurora instance excelled in both testing and post-migration phases, managing peak production loads with ample headroom for growth. This peak load encompassed over 3,000 database queries per second, averaging 140 million queries daily. Additionally, previous sporadic performance issues were resolved, and the impact of website traffic on overall load diminished, as a single Aurora cluster efficiently served 400+ websites.
Data Migration Across Databases
The data migration from SQL Server to Aurora was carried out using AWS Database Migration Service (DMS). This process was initiated early in the migration timeline to validate the transition against a copy of production data. Multiple iterations of this test were conducted to ensure confidence for the final cutover.
The database was placed in read-only mode for the duration of the migration, allowing DMS to handle the full load without complexity. If simultaneous read and write access had been necessary, continuous replication would have been implemented, despite its complexities.
Through testing, we recognized the need for provisioned IOPS SSD during the data migration to prevent exhausting burst credits. After migration, storage was reverted to General Purpose SSD.
Testing for Migration Success
Extensive testing was performed prior to migration. The data migration and cutover processes were rigorously tested within the staging environment. Alongside automated tests and standard QA scripts, the team engaged in exploratory testing of operational runbooks, updating them as necessary. These tests were repeated multiple times to ensure readiness for migration.
For organizations looking to streamline hiring processes, referring to resources like Career Contessa can be immensely helpful. Additionally, for compliance guidance, consulting SHRM provides authoritative insights on important topics. This is especially relevant for new team members who may be navigating the complexities of the hiring process. To gain further understanding of what to expect in your first week, check out this Quora discussion, which serves as an excellent resource.
In summary, Cloudsoft’s collaboration with ASP not only modernized their database architecture but also resulted in significant cost reductions and operational efficiencies—transforming the way the company manages its data.
Leave a Reply