Working with Commvault and Azure SQL
Technical Overview
When backing up Azure SQL it is important to understand the backup process and performance parameters. The following steps occur during backup.
- Start backup
- Make clone of database
- Export clone to temporary staging location
- Protect data from staging location to Commvault Storage Pool.
- Remove export and clone
- Complete backup
With Commvault versions 2023 and newer we can use DacFX for the export process. This means the export will go directly to the access nodes local drive. This is preferred. Prior to this, the export required a storage account with blob storage. In addition, there could not be any network restrictions to the storage account which may go against security requirements.
Why DacFX?
- Backup larger databases
- Mitigate security concerns by using storage local to the access node instead of blob storage for staging backup data.
- Easier configuration
Networking Requirements
The following ports need to be open bi-directionally between the Access Node(s) and the SQL Instance(s) to be protected.
- 8443 to
https://management.core.windows.net:8443 - 1433 to
*.database.windows.net - Also port 22, on the Access Node computer if using Red Hat Enterprise Linux operating system.
In addition, the networking for the SQL Instance must be setup to allow for access from the Access Node network. This can be via a private link or public but may need to be added if not already present. You can navigate to the Azure Portal > SQL server > Networking blade to make changes. These ports will also need to be opened on the network security group (NSG) associated with the access nodes.
Access Node Requirements
For sizing, the export and temp directories combined require up to 3 X the size of the database being protected. For example, if it is expected that 1 TB of databases will be protected concurrently then the Access Node will require potentially 3 TB of free space temporarily to stage the export.
In fact, the following excerpts from MS documentation suggests that for 2 databases with the exact same size but different nature of data, we may very well end up with different disk size requirements and can require disk space up to three times the size of the database.
The export staging directory defaults to the job results directory but any local drive can be used.
Whatever drive that is used for the staging directory should also be the same drive that the system environment variables TMP and TEMP utilize. This makes sizing the space required easier. NOTE: It is necessary to change both TMP and TEMP.
Service Account
- The service principal or managed identity associated with the Azure pseudo-client should be created and have the SQL Server Contributor role at the Subscription level.
- The database service account must be the instance server admin account that has access to the instance and database to perform all operations including backup, restore, and browse operations.
Installation
In the Commvault documentation it was not clear as to exactly what needs to be installed to support the solution. We were not able to even discover SQL databases until the SQL Agent was installed on the Access Node, but this is not listed in the system requirements. Also, the Virtual Server Agent will need to be installed on the Access Node to support application of security at the Azure subscription level. Additional components will be required on the Access Node if the account is an AD account. The following components should be installed on each Access Node
- Virtual Server Agent
- SQL Agent
-
If the SQL service account is an AD account, then the following additional components will need to be installed
Microsoft Visual C++ for 2015-2022 Redistributable x86 and x64. Both need to be installed. Can be downloaded from here.
Microsoft Active Directory Authentication Library for Microsoft SQL Server. Download the DacFramework.exe via the MSI installer for Windows.
Configuration
This Essential Online Documentation link can be followed for configuration steps
NOTE: When specifying an AD account use the formatuser@domain.com and not domain\user.
Make sure to select Use a staging path for import/export. The default path is the job results directory.
Backups & Restores
CPU (vCore) count is the most critical factor in determining Azure SQL backup performance . With four core you can expect approximately 50GB/hr. With one (1) core a 50GB database could take approximately 12 hours to complete. This is obviously a huge difference.
For backup performance the longest time will be during the export phase. This is essentially a Microsoft limitation.
If there is concern regarding performance or it is required to validate you can install SQL Management Studio on the Commvault Access Node and use that to run a backup as it uses DacFX as well. Another option to use is the SqlPackage /Action:Export command from the Access Node.
Restore performance will be similar to backup performance at ~50Gb/hr. However, during restore it will by default, restore SQL to the lowest pricing tier which is Standard S0: 10 DTUs. This pricing tier is the lowest. This will drastically reduce performance. To remedy this in 11.34 (2024) this can be controlled from the Command Center when initiating the restore. If running an earlier version of Commvault there are Additional Settings available that can be applied to the Access Node. A Commvault Support ticket would be required for this, however.
When restoring the max size for the new DB is set to 250GB. If the database to be restored is greater than 250GB you can modify in the Azure Portal during restore or a ticket can be opened with Commvault Support to obtain an Additional Setting to control this behavior.
Finally, Additional Settings can be applied to control Resource Lock behavior as well as the size of the size of the clone DB used during the export phase. If possible, I will detail these later but they are fairly new (as of Spring 2024).