Concurrency with Azure Backup for SQL Server

For SQL Server IaaS VMs, Microsoft provides Azure Backup for SQL Server – a PaaS service allowing you to leverage the Azure Backup service to back up SQL Server databases easily and in a centralised manner.

In this post, we’ll discuss a recent pitfall we came across with backup concurrency, and how this can be avoided with a simple configuration change.

As shown in the image below, Azure Backup uses a workload extension installed on the server to coordinate database backups. The backups are taken using scheduled jobs and are streamed into a Recovery Services vault using VDI.

Problem

Suppose you’re using Azure Backup on a SQL Server instance with many databases. In this case, it’s important to know that the workload extension can create up to 20 concurrent backup tasks at any given time by default.

Depending on the size of your databases and the VM resources available, this can cause considerable strain on an instance during the backup window. In our case, the SQL Server in question hit the I/O limits imposed by the Azure disk in use. Whilst upgrading the disk tier is an option, this would increase monthly VM costs, and the additional I/O throughput wasn’t required for the workload (only the backup routine).

Solution

Fortunately, there is a workaround to this issue, as Microsoft provide the ability to control the number of concurrent backup jobs run by Azure Backup through a configuration file and override value:

1. On the SQL Server, create a new file named ExtensionSettingsOverride.json in the “C:\Program Files\Azure Workload Backup\bin” directory. This is where the Azure Backup Extension is installed by default. 
2. In the JSON file you created, add an entry for DefaultBackupTasksThreshold, and set the value lower than the default of 20. In the example below, I set the new threshold to 5.

3. Save and close the file, then restart the AzureWLBackupCoordinatorSvc service for the changes to take effect.

It’s important to note that this configuration change is a trade-off, as reducing the number of concurrent jobs will increase the overall backup time for an instance. If you see issues with resource usage however, it can be a simple way to tweak backup performance.