Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from corruption or deletion. SQL Managed Instance (SQL MI) backup is a long-running process that is running in the background using the same resources as the workload. This means that the storage file throughput is shared amongst the backup and the main database workload. Therefore, it is important to make the backup operations as fast as possible with no or minimal interference with the customer workload.
What is new?
SQL MI backup used to be a sequential operation that was processing one file at the time. Now, with the parallel backup in SQL MI General Purpose tier, multiple files are backed up at the same time. Full backup is using the file resources in proportion with the file sizes and thereby spreading the load of the backup throughput across multiple files at the time. On the other hand, the customer-initiated (Copy-Only) backup is now having throughput equal to the total sum of the file throughputs it is backing up in parallel. This article explains these improvements in more details.
For better understanding, let us start with a short background about the SQL MI GP file architecture.
SQL MI GP file architecture
General Purpose is a SQL MI architecture where compute layer (which runs the SQL Server Database Engine process and is being used for query processing & data caching) is separated from the storage layer where all data & log files (.mdf, ldf, .ndf) are placed. The data layer is implemented using Azure Storage that has built-in replication and redundancy that guarantee that the data will be preserved. For more details about the SQL MI GP architecture take a look at this post.
Every database file is placed on a separate Azure Storage disk (one file per disk). These disks that are used in storage layer have fixed sizes (Figure 1), and SQL MI uses minimal disk size that is required to fit the database file. It is important to note that file performance depends on the disk size - larger disks have more IOPS and larger throughputs.
Figure 1 - File sizes
How does this help with the full backup performance?
Having SQL MI GP file architecture in mind (Figure 1), let’s do some simple math to illustrate the performance improvement that parallel backup is bringing.
Full backup performance improvement illustration
As a hypothetical scenario, let us imagine having a managed instance with the data layer consisted of two P10 files (100 MB/s) and one P30 file (200 MB/s) and let us assume that its backup process is consuming throughput of about 100 MB/second (note that we are using this value for illustration and it is not the real value).
The sequential backup will operate one file at the time and will consume the total throughput of P10 file, not allowing any workload to run on that file while it is in the process of backup. With parallel backup the files are backed up at the same time and the throughput is spread across the files as following:
TP10 : TP10 : TP30 = 100 MB/s : 100 MB/s : 200 MB/s = 1 : 1 : 2 (values from the table)
TP10 + TP10 + TP30 = 100 MB/s (backup throughput)
TP10 = 25 MB/s and TP30 = 50 MB/s
This means that P10 files will still have the remaining 75 MB/s (100 – 25) to devote to the workload activities and P30 file will have the remaining 150 MB/s (200 – 50) for the workload (Figure 2).
Figure 2 - Visualized example of the sequential and parallel backups
How does this help with the customer-initiated (Copy-Only) backups?
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Its throughput is limited only by a file’s throughput that is unused at the time.
Copy-Only backup performance improvement illustration
As a hypothetical scenario, let us imagine having a managed instance with the data layer consisted of two P20 files, and assume that the workload is consuming throughputs of about 50 MB/s and 100 MB/s. In this setup, the Copy-Only will have the throughput of 100MB/s (150 – 50) to back up the first file and 50MB/s (150 - 100) to backup the second file.
The sequential backup will back up one file at the time with the throughput of 100MB/s or 50MB/s depending on the file it is backing up, while the parallel backup will back up both files at the same time with the total throughput of 150MB/s, meaning that the backup will finish earlier.
We compared the sequential and the parallel backup on an MI with two P20 files and no workload at the time. Sequential backup ran with the throughput of ~150MB/s (Figure 3), while the parallel backup ran twice faster, with the throughput of ~300MB/s as expected (Figure 4). Improvement is clear in Figure 5.
Figure 3 - Old Sequential backup
Figure 4 - New Parallel backup
Figure 5 - Visualized backup performance improvement
Summary
Microsoft is constantly working on improving SQL MI performance. With parallel backup, your backup performance is way better now and the interruptions to your core database workload from the ongoing backups are going to be significantly reduced.
Recommended steps
This feature is automatically enabled for you on both new and the existing SQL MI GP instances such that the backup is backing up two files in parallel if your instance is having at least two files. This number might change in the future. There are no additional steps that you need to perform – just enjoy the improved performance of your SQL MI GP instances.