Healing Through Innovation: Final Steps in SQL Server to RDS Migration

In my last post, I shared a big win in the medical field: we successfully migrated a client’s SQL Server environment from Amazon EC2 to Amazon RDS Custom. This was no small feat — we moved over 600 databases, tackling some serious technical challenges along the way. While we initially considered using AWS Database Migration Service (DMS), we ran into some roadblocks and ultimately found that a backup/restore approach was much more efficient. This decision not only simplified the process but also led to over $600,000 in annual infrastructure cost savings by choosing RDS Custom over RDS Standard.

But the benefits didn’t stop there. The RDS migration also gave us the opportunity to upgrade to SQL Server 2022, which improved performance and future-proofed the client’s environment. Given the client’s multi-database model — with a central pointer database directing traffic across six EC2 instances — we had to plan carefully to update database locations without any issues. To minimize service interruptions, we divided the databases into seven migration groups and executed a highly automated process.

Here’s how we pulled it off:

• We took full backups on the day of the cutover and transferred them to AWS S3 using PowerShell scripts.
• We restored those backups on RDS Custom and applied differential backups at the cutover time to keep downtime to a minimum.
• We used automation through dynamic scripts and a Migration Master Table, which made it possible to manage over 600 databases efficiently.

Thanks to careful planning and automation, we wrapped up the entire RDS migration two hours ahead of the planned downtime window. The result? Massive cost savings and a scalable, future-proofed environment.

But like with any big project, there were a few bumps in the road after the RDS migration. Let’s dive into one of the key post-migration challenges we faced.

Post-Migration Challenge: Compatibility Level and Performance Issues

One of the big changes during the migration was the upgrade from SQL Server 2017 to SQL Server 2022. Before the move, the client was running all their databases at a compatibility level of 100 — that’s SQL Server 2008. As part of the upgrade, we also updated the compatibility level to SQL Server 2022 (160). We tested this in lower environments without any issues, but the client didn’t have the ability to fully load test those environments.

At first, everything looked great. But a day or two later, we started seeing performance issues with one of the larger databases. The client noticed their queries slowing down, which caused blocking and compounded the slowdown. When I jumped on a call with the client to investigate, we realized the problem: their code had been built and optimized for compatibility level 100 (SQL 2008). Now, running at compatibility level 160 (SQL 2022), it just wasn’t performing as well.

SQL Server lets you set the compatibility level on a per-database basis, which provides some backward compatibility with earlier versions. The compatibility level affects how the database behaves, and as we saw here, it can make a big difference in performance. In this case, the client’s code simply wasn’t optimized for the new compatibility level — and that only became apparent when the workload increased in production.

To fix the issue, we rolled back the compatibility level from 160 to 100. As soon as we did that, performance returned to normal. At the client’s request, we applied this change across all their databases to prevent similar issues elsewhere.

That said, I made sure to explain that running SQL Server 2022 with a compatibility level of 100 isn’t a long-term solution. It’s not optimized, and they’ll need to work on updating their code to take full advantage of SQL Server 2022’s capabilities. I also flagged that the option to roll back to compatibility level 100 might not be available in future SQL Server releases — it’s already the oldest supported level in SQL 2022, and it’s likely next on the list for deprecation.

Key Takeaways from this RDS Migration

This migration journey was filled with wins and lessons. Moving over 600 databases from Amazon EC2 to Amazon RDS Custom simplified infrastructure management and cut annual costs by more than $600,000 with moving to RDS Custom over RDS Standard. Upgrading to SQL Server 2022 created a scalable, future-proof environment, but it also surfaced performance challenges tied to compatibility level changes.

The post-migration performance issues underscored the importance of understanding legacy code dependencies. Even with lower-environment testing, real-world workloads can reveal unexpected problems — and that’s exactly what happened here. Rolling back the compatibility level to SQL 2008 (100) solved the immediate issue, but it also highlighted the need for long-term code optimization.

Moving forward, the client plans to refactor and optimize their code for SQL Server 2022 compatibility. This experience reinforced the importance of strategic planning, automation, and collaboration — not just for a smooth transition, but for setting the stage for future growth and performance improvements.