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

What’s on the Horizon for Cybersecurity in 2024?

Another year is behind us, a year filled with remarkable advancements, jaw-dropping breaches, and the good guys still struggling to keep up with the bad guys. Before jumping into how 2024 may shape out or diving right into the doom and gloom of what went wrong in...

Streamlining Cybersecurity Using the DOD Replicator Initiative

The Department of Defense (DOD) has taken a bold step in addressing the need for more innovation and capabilities in fighting modern adversaries. Deputy Secretary of Defense Kathleen Hicks recently introduced the department’s new Replicator initiative intended to use...

Want To Read More?

Categories

You May Also Like…

Let’s Talk