Reducing Your Microsoft SQL Native Backup Duration on AWS RDS

Paul Ward
August 24, 2023
Advanced Analytics | Blogs

If you’re seeking efficient ways to minimize your Microsoft SQL native backup duration on AWS RDS, you’re not alone.

With an array of third-party and AWS-provided backup solutions for AWS RDS SQL Server, the SQL Server’s native backup feature still tops the list for many. But why do backups on RDS sometimes take an inordinate amount of time?

In this article, we’ll delve into how AWS RDS instance sizes and EBS types can directly impact the duration of SQL native backups.

A Peek Into Our Mock Workload

To best understand this, we simulated a typical workload. By setting up a Microsoft SQL Server on RDS with a database size of 152 GB filled with random data, we emulated the characteristics of a standard Microsoft SQL Server from a conventional application.

For reference, the database version in use was Microsoft SQL Server Standard, running on engine version 15.00.4312.2.v1.

Consistent with many clients’ setups, we:

  • Enabled compression
  • Activated support for native backup and restore in SQL Server, as outlined in the AWS documentation
  • Used one, four, and eight backup files in our tests to gauge any significant effects

 Notable Observations

Here’s a revelation: SQL Server’s native backup is notably taxing on the CPU. This becomes glaringly obvious when monitoring CPU Utilization, along with a corresponding surge in disk queue depth.

(Images showcasing utilization of the db.m5.large during SQL native backup)

Our findings? The choice of an instance optimized for CPU performance carries the most weight.

While one might think that EBS volumes with higher IOPS would reduce backup durations, our tests showed no significant advantage in that regard. However, configuring the backup with eight files when the instance is primed for native backup showed noticeable improvements.

To Resize or Not to Resize: That is the Question

Is it wise to resize your smaller RDS SQL server instances to amplify backup efficiency? The answer depends on your circumstances.

Users who are actively accessing the system while backups are running could likely experience a dip in performance. If this lag breaches the application’s Service Level Agreement, you might have to rethink your backup schedule to off-peak hours or contemplate resizing the RDS instance.

Then, there’s the inevitable question of budget — are you prepared for a possible spike in costs?

Wrapping Up

If you’re grappling with these questions or need more tailored advice, don’t hesitate to reach out. Let’s navigate this intricate terrain together.

Backup FilesBackup TimeInstance SizeEBS TypeEBS Size GBIOPS ConfiguredThroughput Configured
130 Minutesdb.m5.largeGP34006,000600
429 Minutesdb.m5.largeGP34006,000600
830 Minutesdb.m5.largeGP34006,000600
113 Minutesdb.m5.4xlargeGP34006,000600
46 Minutesdb.m5.4xlargeGP34006,000600
84 Minutesdb.m5.4xlargeGP34006,000600
125 Minutesdb.m6i.largeGP34006,000600
425 Minutesdb.m6i.largeGP34006,000600
825 Minutesdb.m6i.largeGP34006,000600
125 minutesdb.m6i.largeIO14006,000 
425 minutesdb.m6i.largeIO14006,000 
824 Minutesdb.m6i.largeIO14006,000 
125 minutesdb.m6i.largeIO140012,000 
425 minutesdb.m6i.largeIO140012,000 
824 Minutesdb.m6i.largeIO140012,000 
113 Minutesdb.m6i.4xlargeGP34006,000600
46 Minutesdb.m6i.4xlargeGP34006,000600
84 minutesdb.m6i.4xlargeGP34006,000600
118 Minutesdb.r5.xlargeGP34006,000600
415 Minutesdb.r5.xlargeGP34006,000600
815 Minutesdb.r5.xlargeGP34006,000600

Follow us

Recent Posts

VMware Explorer Las Vegas 2023: VMware Cloud Highlights

VMware Explorer Las Vegas has officially come and gone. It was another great event with a lot of announcements across the entire VMware portfolio, including some big announcements around Private AI. As always, there were good breakout sessions that included some of...

Want to Read More?

Categories

You May Also Like…

Get in Touch

We would love to hear from you!