Migrating from On-Premises Microsoft SQL Server to AWS Serverless for Business Intelligence and Application Data

As companies aim to streamline operations, boost efficiency, and cut costs, many are turning to cloud computing and serverless technologies. One such company had long relied on an on-premises Microsoft stack – SQL Server, SSIS, and Reporting Services – to power their business intelligence and internal applications. Recognizing the limitations of this legacy infrastructure, we spearheaded an initiative to completely re-architect their data landscape on AWS leveraging serverless services. In this post, I will summarize how we migrated their business intelligence and transactional workloads from Microsoft SQL Server to a next-generation serverless architecture using Amazon Aurora, Lambda, EventBridge, Glue, S3, Redshift and QuickSight. I will provide a high-level overview of the original and new architectures along with key aspects of the migration process.

Old Architecture (On-Premises)

The legacy on-premises architecture consisted of two main data pipelines:

First, a Python script scheduled via Windows Task Scheduler would download daily worldwide sales and product data flat files from the corporate intranet to a Windows server in our European subsidiary.

Next, SQL Server Integration Services (SSIS) ETL packages would ingest these flat files, transforming and loading the worldwide data into a European SQL Server staging database. This fed both the sales website product catalog and CRM system. Further ETL jobs delivered aggregated sales data to downstream reporting databases.

Finally, internal staff accessed business intelligence reports built on SQL Server Reporting Services sourcing data from the reporting databases.

Over time, this architecture exhibited several drawbacks:

Scalability and performance degraded as data volumes grew exponentially.

Reliance on external consultants for specialized maintenance and troubleshooting.

Manual effort required for security patching and upgrades.

Increasing infrastructure and licensing costs.

This motivated our initiative to re-architect on AWS leveraging serverless technologies.

New Architecture (AWS Serverless)

To address the drawbacks of the legacy on-premises architecture, we embarked on an initiative to re-platform the entire data landscape on AWS leveraging serverless technologies.

We took an iterative approach – first building a proof of concept (PoC) focused on a subset of the data pipeline. The success of the PoC provided a compelling case to management for full adoption of the new serverless architecture on AWS.

Through leveraging AWS documentation and best practices, we gradually developed the complete new target architecture over time. Key components included:

S3 for raw, refined, and reporting data
Lambda functions orchestrated by EventBridge for automation
Glue ETL jobs for data transformation
Aurora Serverless for operational database
Redshift Serverless for analytical database
QuickSight for reporting and dashboards

This new serverless architecture on AWS addressed the limitations of the legacy on-premises environment and set the stage for enhanced scalability, reduced costs, and improved performance and security. The gradual migration approach enabled us to incrementally build expertise while minimizing risk.

Lambda Functions and EventBridge
Python Lambda functions are configured to fetch raw data files from the corporate intranet SFTP server into the S3 raw bucket
EventBridge scheduled rules trigger the Lambda functions to run daily at specified intervals
Additional error handling Lambda functions implemented to check for failures and retry
AWS Glue ETL Jobs
Glue crawlers used to infer schema and populate metadata in Glue Data Catalog
ETL jobs written in Python perform transformations like selecting specific columns, parsing dates, handling nulls
Jobs optimized with features like data framing, partitioning, compression
Parameterization used for easier maintenance like table names
Jobs orchestrated into sequences with job bookmarking to enable continuity
Aurora and Redshift Tables
Aurora database is 10 node cluster of db.r5.large instances with auto pause enabled
Aurora rows have TTL property to automatically delete older data
Redshift is ra3.4xlarge 3 node cluster with automatic snapshots and scaling
Late binding views created on Aurora and Redshift to abstract underlying tables
Infrastructure as Code
CloudFormation used to manage infrastructure for Lambda, Glue, Aurora, Redshift
Terraform configurations applied for foundational networking, IAM, S3
Git repository with version control for all configuration code

Database Migration:

To migrate the original SQL Server databases to the new AWS architecture, we utilized AWS Database Migration Service (DMS) and the Schema Conversion Tool.

DMS enabled us to replicate data from the source SQL Server instances into the target Aurora and Redshift databases with minimal downtime. Key capabilities like data validation and ongoing replication facilitated a smooth transition.

Previously, SQL Server served for both transactional and analytical workloads. With the new architecture, we implemented separate purpose-built databases for OLTP (Aurora) and OLAP (Redshift) to optimize performance, scalability, and costs.

Leveraging DMS and its integration with other AWS services, we were able to successfully migrate our databases from on-premises SQL Server to the new serverless paradigm on AWS.

Gained after moving to AWS

Migrating to AWS enabled several benefits and best practices including:

Improved cost transparency and scalability from pay-per-use services. We optimized spend by monitoring usage and implementing auto-scaling policies.

Performance gains by offloading read traffic to multi-AZ read replicas for Aurora and Redshift.

Automated database backups and simplified replication through native AWS services.

Infrastructure as code adoption using Terraform for provisioning resources and version control.

Reduced administrative overhead by leveraging AWS serverless services like Lambda and Glue.

Storage and compute managed by AWS, eliminating maintenance of on-premises hardware.

By moving to a modern serverless architecture on AWS leveraging database services purpose-built for transactional and analytical workloads, we were able to resolve scaling challenges and reduce costs while increasing performance, resilience, and development velocity.

Migrating our business intelligence and application data infrastructure to a serverless architecture on AWS delivered tremendous benefits for our organization. It enabled us to cost-effectively scale while improving performance, reducing maintenance burdens, and leveraging purpose-built database services.

This migration represented one of the best technology investments our company has made. We are now actively exploring additional AWS services like EMR and SageMaker to further capitalize on our data. Initiatives are underway applying machine learning and deeper analytics to derive enhanced business insights.

We hope this post summarizing our migration experience from on-premises SQL Server to AWS serverless helps provide valuable lessons learned for others undertaking similar modernization journeys.