iPromote Migrates and Modernizes Microsoft SQL Server Database on AWS

Executive Summary

iPromote is a provider of digital advertising solutions that makes it easier for small and mid-sized businesses to leverage digital advertising. The organization’s product suite covers the full gamut of digital advertising needs, from presales tools and automated ad creation to budget management and performance analytics. Since forming in 2004, iPromote has grown to serve more than 30,000 customers across a multitude of industries.

After nearly 20 years of operation, iPromote decided it was time to make some improvements to its IT infrastructure and core application. The company worked with ClearScale to modernize its platform, execute a much-needed database migration, and implement a cloud-native data lake. As an AWS Premier Tier Services Partner with Data and Analytics and Migration competencies, ClearScale was the ideal partner for the job.

The Challenge

iPromote recognized it had an opportunity to upgrade its core platform in important ways. The company hoped to boost efficiency, scalability, and performance by adopting a more cloud-forward, modernized solution. A big part of this involved offloading database management and running transformations and batch processing within a cloud data lake. To make this work, the application would require strategic refactoring.

Furthermore, the client hoped to migrate away from a legacy Microsoft SQL Server database, which would eliminate licensing requirements and open the door for more cloud-optimized engines. iPromote had been using Microsoft SQL Server for many years for both data storage and analytical workloads. Furthermore, the way the application was designed put unnecessary load on the database.

While it was possible to improve performance by increasing Microsoft SQL Server cluster instance sizes, this solution wasn’t sustainable for two reasons. First, vertical scalability has an upper limit, as individual instances can only grow so much. Second, the most powerful instances available are expensive. Adopting a cloud-based architecture would enable horizontal scalability and give iPromote the ultimate flexibility to decide when and how to deploy different instance sizes based on platform demand.

With these challenges in mind, ClearScale was all set to get started.

The ClearScale Solution

Our team began by going through a thorough discovery and planning process. The project was big enough to justify running a separate project for the discovery work. ClearScale wanted to make sure iPromote’s vision was viable and made sense for the business before making any technical changes.

During this discovery phase, our team created several Proofs-of-Concept and ran a multitude of performance tests. We also refined our initial estimates of how much value iPromote would see by going forward with the proposed solution. After validating each part of the project, we proposed the following order of operations:

• Migrate the Microsoft SQL Server to Aurora PostgreSQL
• Modernize the application layer
• Move the analytical workloads from the database level to a S3 data lake and process that data with Spark Jobs

On the database migration side, moving to Amazon Aurora was the right decision for a few reasons. Amazon Aurora is AWS’ proprietary relational database technology that offers PostgreSQL compatibility, which reduces migration time from SQL Server. With Aurora, iPromote was able to easily provision a read replica without any manual setup.

The company was also able to benefit from Aurora’s continuous backups right away, which replaced a more complex homegrown backup and recovery solution from its SQL Server environment. In summary, Aurora provided a fully hosted, highly available, durable, and recoverable solution. In the future, Aurora’s scalability and integration with other AWS services will allow iPromote to maximize the value of its data.

This part of the project also required modernizing iPromote’s .NET-based application to be compatible with Aurora PostgreSQL. We accomplished this by enabling API communication to the new database.

After setting up the database, ClearScale deployed a data lake on Amazon S3 with automated ingestion and processing pipelines. This further enhanced iPromote’s operational efficiency and cost structure. With all the pieces in place, we deployed the solution to a production environment with iPromote’s help.

Architecture Diagrams

DB Migration

Data Lake (Simplified)

The Benefits

iPromote experienced many benefits from migrating and modernizing its database on AWS. The company was able to offload database management and split its database and analytical workloads between Aurora and S3. iPromote also addressed its vertical scalability problem and reduced its database size to half of what it was originally, thanks to the refactoring work by ClearScale. What’s more, iPromote was able to reduce some costs by avoiding Microsoft SQL Server licensing fees.

Overall, ClearScale’s solutions architects and cloud engineers accomplished the following:

• Refactoring of 600 stored procedures on the database front
• Refactoring of 200 stored procedures on the data lake front
• Tested the performance of 500 stored procedures
• 15 TB of source data moved
• 100 hours to execute the production migration and final cutover

iPromote is now able to offer even better digital advertising services to its SMB customers and has what it needs to continue moving forward.