How We Migrated 1,200+ SQL Server Databases in Under 3 Hours (While Cutting Storage Costs by 84%)

Editor’s Note: This is part one of a two-part series. In next week’s follow-up, we’ll cover the project management and coordination strategies that helped make this migration a success.

What if you could retire two aging SQL Server 2016 failover clustered instances, migrate 1,200+ databases to SQL Server 2022, and slash storage spend by 84%—all in the length of a weekend maintenance window?

In this post, I’ll tell you exactly how we did it for a client using AWS.

We had multiple goals for this migration:

• Move to SQL Server 2022 and Windows Server 2022
• Reduce Amazon AWS storage costs
• Minimize the downtime required for the migration

The environment consisted of two SQL Server 2016 Enterprise failover clustered instances hosting over 1,200 production databases. As you may know, mainstream support for SQL Server 2016 ended on July 13, 2021, with extended support ending on July 14, 2026 (which is approaching quickly). This post explains how we successfully migrated this complex environment with less than 3 hours of planned downtime while simultaneously reducing infrastructure costs by transforming their storage architecture and backup strategy.

Key Results:

• 1,200+ databases migrated with minimal business disruption.
• Infrastructure storage costs reduced by 84% (estimated).
• SQL Server platform modernized from 2016 to 2022.

Client Profile

Industry: Education software

Environment:

• Two SQL Server 2016 Enterprise failover clustered instances in Amazon AWS
• FSx file share volumes for database storage
• FSx file share for backups
• Multi-subnet configuration
• 1,200+ production databases ranging from 1GB to 3TB in size
• 24/7 business operations with minimal tolerance for downtime

The Challenge

Business Drivers:

The client approached us with several business requirements:

Cost Reduction: Their existing FSx storage and backup architecture was consuming an unsustainable portion of their IT budget.

Technology Modernization: SQL Server 2016 was approaching the end of extended support, requiring an upgrade path to maintain vendor support and security compliance.

Minimal Disruption: With 1,200+ production databases supporting critical business functions, any migration solution needed to minimize downtime.

Technical Challenges:

The technical landscape presented several significant obstacles:

FSx Storage Cost and Performance

The client’s reliance on Amazon FSx for Windows File Server provided the necessary shared storage functionality for their SQL Server failover clusters, but at a premium price point. The FSx architecture also introduced additional network latency for I/O operations, affecting database performance.

Complex Multi-Subnet Configuration

The existing environment leveraged a multi-subnet configuration, adding complexity to any potential migration solution.

Database Scale and Diversity

With over 1,200 databases that vary dramatically in size, usage patterns, and business criticality, a one-size-fits-all migration approach would be insufficient.

Backup Architecture Limitations

The existing backup strategy relied on additional FSx storage, increasing the storage costs while creating management overhead.

Strict Downtime Constraints

The business could tolerate a maximum of 8 hours of downtime, with a strong preference for staying under 4 hours to minimize impact on operations.

The Solution

After a comprehensive analysis and planning, we designed a phased approach that would transform the client’s database infrastructure while meeting their strict requirements for minimal disruption.

Solution Architecture

We architected a modern SQL Server environment on AWS with the following components:

1. Platform Upgrade:
   • SQL Server 2022 Enterprise Edition
   • Leveraged latest features and extended support lifecycle
2. Storage Transformation:
   • From FSx file shares to Multi-Attached EBS volumes
   • io2 Block Express volumes for critical data files
   • Optimized configuration for SQL Server workloads
3. Backup Modernization:
   • Implement S3-based backup strategy
     • Direct SQL Server native backup to S3
   • Intelligent lifecycle policies for cost-effective long-term retention
   • Elimination of dedicated backup storage infrastructure
4. High Availability Design:
   • SQL Server 2022 FCI
   • Modern monitoring integration

Implementation Approach

We divided the implementation into distinct phases to manage risk and ensure successful outcomes.

Phase 1: Development Environment Migration

We began with the client’s development environment to validate our approach and identify potential issues. We worked with the client’s internal system admins to complete the EC2 instance and Windows Server Failover Cluster builds. This allowed us to focus mainly on the SQL Server FCI components:

1. Infrastructure Provisioning:
   • Deployed new EC2 instances optimized for SQL Server 2022
   • Configured multi-attached EBS volumes
   • Established networking and security configurations
2. Initial Testing:
   • Validated storage performance
   • Confirmed failover functionality and timing
   • Verified backup and restore operations to S3
3. Migration Dry Run:
   • Executed migration process on a subset of development databases
   • Measured migration times
   • Refined automation scripts based on observations

Phase 2: Production Environment Preparation

With lessons from the development migration incorporated, we prepared for the production migration:

1. Pre-Migration Activities:
   • Comprehensive database inventory and dependency mapping (i.e., agent jobs, applications, etc.)
     • Planning, communication, and task tracking were crucial to the overall success of the migration.
   • Database health checks to identify potential migration blockers
     • Including utilizing Microsoft’s Data Migration Assistant to ensure database compatibility
   • Implementation of enhanced monitoring
2. Automation Development:
   • Custom PowerShell script creation for orchestrating the migration
   • Integration with DBATOOLS for database backup and restore operations
3. Infrastructure Deployment:
   • Production SQL Server 2022 cluster provisioning
   • Multi-attached storage configuration and testing
   • S3 bucket preparation with appropriate security and lifecycle policies
   • Tested access to the current backup location from the new SQL Server 2022 instances (permissions and network access)

Phase 3: Production Migration Execution

The production migration followed our carefully orchestrated plan:

1. Pre-Stage Initial Data:
   • Performed initial data copy without taking systems offline
   • Utilized a combination of native Full, Differential, and Log backups
   • Leveraged the rebuilt PowerShell script to perform the initial full restore and differential restore
   • Restored log backups every 30 minutes
   • Scheduled restores via the SQL Server Agent on the new SQL Server 2022 FCIs
   • Established baseline synchronization to minimize final transfer time.
2. Migration Window Execution:
   • Implemented application maintenance mode at the start of the maintenance window, blocking access
   • Executed the final data synchronization using our custom migration script
     • Tail-log backup was used to capture all transactions and place the database on the SQL Server 2016 instances into “recovering” status
       • This accomplished two important goals:
         • Capture all transactions that have occurred since the last transaction log backup
         • Place the database into a “recovering” state so that new connections were not allowed in the old environment
   • Performed database restores on the new SQL Server 2022 instances using all transaction log backups, including the final tail-log backup
     • Databases were then recovered and placed into a multi-user read-write state
   • Raised the database compatibility level to 160
   • Changed the database owner
   • Validated each database post-migration
   • Initiated post-migration full backups
3. Cutover:
   • Updated connection strings and DNS entries
   • Performed final validation testing
   • Opened systems for production use

The Technology

Multi-Attached Storage Architecture

The shift from FSX to multi-attached EBS volumes represented one of the most significant technical innovations in this project. We configured the volumes with performance characteristics matched to their specific workloads:

• Data volumes: 3,000 IOPS
• Log volumes: 3,000 IOPs
• TempDB volumes: Amazon EC2 instance store volume (local to each EC2 instance)

This approach delivered both performance improvements and significant cost savings compared to the FSx solution.

Custom Migration Automation

The scale of this migration—over 1,200 databases—demanded sophisticated automation. We developed a custom PowerShell script that utilized multiple commandlets from the DBATools module.

DBATools Integration

While our custom script orchestrated the overall migration process, we leveraged the power of DBATools—an open-source PowerShell module for SQL Server management—for many of the core database operations. This combination of custom automation with proven community tools provided the perfect balance of flexibility and reliability.

Key DBATools functions employed included:

• Get-DBABackupHistory to retrieve the latest backup information for each database.
• Restore-DbaDatabase for restoring the initial full, differential, and all t-log backups.

The Results

The migration was completed successfully with impressive metrics:

Cost Reduction

• 84% reduction in monthly storage costs

Business Impact

• Minimal disruption: Total downtime of only 2 hours and 55 minutes, well under the 8-hour target
• Extended support lifecycle: Movement to SQL Server 2022 provides support through 2032
• Performance headroom for growth: New infrastructure accommodates projected 55% database growth

Migration Metrics

• Total databases migrated: 1,235

Key Lessons Learned

Throughout this project, we gained valuable insights that would benefit any organization undertaking a similar database migration:

1. Thorough database inventory is critical
   • Understanding dependencies between databases was crucial
   • Early identification of current database sizes allowed for proper allocation of storage to support the migration.
2. Storage strategy has an outsized impact
   • Moving to multi-attached EBS volumes delivered both cost and performance benefits
   • SQL Server 2022 introduced the ability to backup directly to S3, delivering significant storage savings vs. FSx
3. Automation is non-negotiable at scale
   • Manual migration for 1,200+ databases not possible within time constraints
   • Investment in custom automation paid dividends in reliability and speed
4. Pre-staging reduces risk
   • Copying the bulk of data before the migration window dramatically reduced the final migration time
   • Delta synchronization proved highly efficient using t-log backups
5. Multi-phase approach validates methodology
   • Starting with the development environment allowed refinement of techniques
   • Lessons learned in initial phases improved production migration execution

Conclusion

This post demonstrates how even the most challenging database migrations can be accomplished with minimal downtime when approached with careful planning, appropriate technology choices, and custom automation. By transforming the storage architecture and leveraging modern AWS services, we not only successfully migrated 1,200+ SQL Server databases but also delivered substantial cost savings and performance improvements.

For organizations facing similar challenges with aging SQL Server environments, this project provides a proven blueprint for success—combining the best of cloud infrastructure, modern database platforms, and sophisticated automation techniques.

The client now enjoys a modern, cost-effective SQL Server environment that provides room for growth while reducing ongoing operational costs. Most importantly, they achieved this transformation with minimal disruption to their business operations—proving that with the right approach, large-scale database migrations don’t have to be painful or disruptive.

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.