Migrating On-Prem SQL Server to Amazon RDS for High Availability and Cost Optimization

Introduction

A mid-sized financial services company with over 500 employees was experiencing significant performance and availability issues with its on-premise Microsoft SQL Server infrastructure. The infrastructure supported core banking applications, customer data management, and internal reporting processes. The SQL Server environment was aging, with limited scalability, lack of built-in high availability features, and growing maintenance costs. Additionally, disaster recovery procedures were manual and error-prone, posing a risk to data integrity and SLA commitments. 

The client aimed to modernize their database infrastructure by migrating to a managed cloud service to improve availability, automate maintenance tasks, ensure scalability during peak loads, and reduce operational overhead. The decision was made to migrate to Amazon RDS for SQL Server due to its fully managed capabilities, native high availability features, and seamless integration with other AWS services.

Architecture Overview

The architecture was designed for high availability, security, scalability, and automation. The RDS SQL Server instance was deployed in a Multi-AZ configuration to ensure automatic failover. The migration solution included real-time replication, monitoring, secure access, and automated provisioning using Infrastructure as Code. 

Key Architectural Components: 

1. Amazon RDS (SQL Server): Deployed in Multi-AZ for automatic failover, backup automation, and patching.
2. AWS DMS: Used for schema and data migration with minimal downtime via CDC. 
3. Amazon S3: Used for storing migration scripts, exported data, and logs. 
4. Amazon CloudWatch: Real-time monitoring of database health and performance metrics. 
5. Amazon SNS: Notifications on critical events like failover or performance threshold breaches. 
6. Terraform: Automated provisioning of resources across multiple environments (dev, test, prod). 
7. AWS IAM & KMS: Role-based access and encryption at rest and in transit. 

Data Sources Integration

The source database was hosted on a legacy SQL Server 2016 setup within a VMware environment. The integration involved: 

1. Schema Extraction: The AWS Schema Conversion Tool (SCT) was used to analyze the current schema and identify compatibility issues. Necessary transformations were made. 
2. Initial Data Export: Tables were categorized based on size and criticality. Large historical tables were extracted in batches to minimize the load. 
3. Live Replication: Change Data Capture (CDC) was configured using DMS to capture real-time changes until cutover. 
4. Stored Procedures & Views: Extracted and manually validated for compatibility with RDS. 
5. Data Consistency: Verified by running parallel queries and checksums during test migration. 

AWS Services Architecture

The project utilized the following AWS services in a modular and scalable architecture: 

Data Flow Process

Step-by-Step Process: 

1. Assessment & Planning: 

1. Performed detailed workload analysis using CloudEndure and SCT. 
2. Identified long-running queries and performance bottlenecks
3. Defined RPO and RTO requirements. 

2. Infrastructure Provisioning: 

1. Wrote Terraform modules to provision: 
2. RDS SQL Server (db.m5.large) with Multi-AZ
3. Parameter groups and subnet groups 
4. CloudWatch alarms, IAM roles, and KMS keys 
5. Verified provisioning through AWS Config and Terraform state files. 

3. Schema & Data Migration: 

1. Ran AWS SCT to extract and convert schema. 
2. Applied schema on RDS using SQL Server Management Studio. 
3. DMS full load task created and started to replicate entire dataset. 
4. Enabled CDC for real-time synchronization of inserts, updates, deletes. 

4. Validation and Testing: 

1. Test applications pointed to staging RDS. 
2. Ran regression, functional, and performance tests. 
3. Validated row counts, referential integrity, and stored procedure execution. 

5. Final Cutover: 

1. Paused writes on legacy DB. 
2. Waited for CDC lag to reduce to zero. 
3. Switched endpoints in application config to point to RDS. 
4. Conducted post-migration smoke tests. 

6. Post-Migration Activities: 

1. Set up automated daily snapshots with 7-day retention. 
2. Enabled Enhanced Monitoring and Performance Insights. 
3. Applied IAM policies for devs, DBAs, BI users. 

Error Handling and Monitoring

1. Error Handling Strategy: 

1. DMS logs errors and warnings in CloudWatch; custom Lambda script checks logs every 15 minutes. 
2. Retry logic for DMS tasks with configurable retry intervals. 
3. Snapshot rollback plan in case of critical failures. 

2. Monitoring Implementation: 

1. Created CloudWatch dashboards for DB metrics: CPU, connections, IOPS, storage. 
2. Alarm, CPU > 85% (alarm + SNS email) 
3. Alarm, Storage > 90% (alarm + auto-scale trigger) 
4. Failed login attempts (alarm + security team alert) 
5. RDS Event Subscriptions: Used to track failovers, backup status, and patching events. 

Data Access and Security

1. Access Controls: 

1. IAM roles for DB admins, developers, support staff. 
2. RDS integrated with AWS Secrets Manager for secure credential management. 
3. Security groups locked to specific IPs and internal VPC CIDRs. 

2. Encryption & Compliance: 

1. Enabled KMS encryption for: 
2. RDS storage and automated backups 
3. Data in S3 used for schema/logs 
4. Snapshots and replicas 
5. Enforced SSL for all application connections to RDS. 
6. Enabled audit logging for compliance with PCI DSS and SOX. 

3. Best Practices Implementation

1. Multi-AZ Deployment: Ensured automatic failover and zero data loss. 
2. Backups & Snapshots: Enabled point-in-time recovery. 
3. Storage Auto-Scaling: Avoided manual intervention as data grew. 
4. Monitoring: CloudWatch + Performance Insights for complete visibility. 
5. Automation via Terraform: Reduced human error and enabled reproducible deployments.
6. Access Auditing: CloudTrail and RDS logs used to trace user activity. 
7. Security Enhancements: Periodic key rotation and VPC-based access restrictions. 

Conclusion

The migration of the on-prem SQL Server database to Amazon RDS resulted in significant improvements in reliability, scalability, and operational efficiency. Notable achievements included: 

1. 95% reduction in unplanned downtime thanks to Multi-AZ failover. 
2. 30% cost savings annually by eliminating licensing and hardware support. 
3. RPO < 5 minutes, RTO < 1 minute via CDC and managed failover. 
4. Increased developer velocity due to easier access to test environments from RDS snapshots. 
5. Compliance-ready infrastructure with encryption, auditing, and logging.

This case study underscores how organizations can confidently transition from legacy on-prem systems to a modern, fully managed AWS environment while improving SLAs, compliance, and cost structure.