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 Files | Backup Time | Instance Size | EBS Type | EBS Size GB | IOPS Configured | Throughput Configured |
| 1 | 30 Minutes | db.m5.large | GP3 | 400 | 6,000 | 600 |
| 4 | 29 Minutes | db.m5.large | GP3 | 400 | 6,000 | 600 |
| 8 | 30 Minutes | db.m5.large | GP3 | 400 | 6,000 | 600 |
| 1 | 13 Minutes | db.m5.4xlarge | GP3 | 400 | 6,000 | 600 |
| 4 | 6 Minutes | db.m5.4xlarge | GP3 | 400 | 6,000 | 600 |
| 8 | 4 Minutes | db.m5.4xlarge | GP3 | 400 | 6,000 | 600 |
| 1 | 25 Minutes | db.m6i.large | GP3 | 400 | 6,000 | 600 |
| 4 | 25 Minutes | db.m6i.large | GP3 | 400 | 6,000 | 600 |
| 8 | 25 Minutes | db.m6i.large | GP3 | 400 | 6,000 | 600 |
| 1 | 25 minutes | db.m6i.large | IO1 | 400 | 6,000 | |
| 4 | 25 minutes | db.m6i.large | IO1 | 400 | 6,000 | |
| 8 | 24 Minutes | db.m6i.large | IO1 | 400 | 6,000 | |
| 1 | 25 minutes | db.m6i.large | IO1 | 400 | 12,000 | |
| 4 | 25 minutes | db.m6i.large | IO1 | 400 | 12,000 | |
| 8 | 24 Minutes | db.m6i.large | IO1 | 400 | 12,000 | |
| 1 | 13 Minutes | db.m6i.4xlarge | GP3 | 400 | 6,000 | 600 |
| 4 | 6 Minutes | db.m6i.4xlarge | GP3 | 400 | 6,000 | 600 |
| 8 | 4 minutes | db.m6i.4xlarge | GP3 | 400 | 6,000 | 600 |
| 1 | 18 Minutes | db.r5.xlarge | GP3 | 400 | 6,000 | 600 |
| 4 | 15 Minutes | db.r5.xlarge | GP3 | 400 | 6,000 | 600 |
| 8 | 15 Minutes | db.r5.xlarge | GP3 | 400 | 6,000 | 600 |
