Using Azure Backup with SQL Server on Azure VM with PSC Dedicated volume
This post details a brief test of Azure Backup for SQL databases. The objective was to evaluate Azure's native backup solution when the SQL Server database, residing on an Azure Virtual Machine, had its data files stored on a Pure Storage Cloud (PSC) Dedicated volume. As a disclaimer, many variations of this test can be done and results may vary, intention here is only to record my recent experience with the technology. The setup I set-up the following environment for the test: PSC Dedicated V20MP2R2 deployed into West Europe region Azure VM Standard D8ds v5 (8 vcpus, 32 GiB memory) running Windows Server 2019 Datacenter gen 2 MS SQL 2019 evaluation edition installed manually on the VM A connection was made between PSC Dedicated and the SQL VM, with MPIO enabled on the VM and one iSCSI session established to each controller. On the PSC Dedicated, I mounted 1TB volume to the host, which I then formatted to NTFS with 64k allocation unit size and mounted as drive F: on the Windows OS. To populate the SQL server with test databases, I made an empty testDB and downloaded 2 sample databases from MS site (AdventureWorks and AdventureWorksLT). The PSC Dedicated volume (F:) was used as the Data Directory of the SQL Server. On the Azure side, Recovery Services Vault (via Backup and Site Recovery from the Marketplace) was created and the VM running the SQL server discovered. The service installs an agent on the VM, enabling it to discover SQL databases instances. Furthermore, an NT Service\AzureWLBackupPluginSvc account used for orchestration of the backup is created on the SQL Server side. The backup For the purpose of the test, I set only a basic policy for all our databases. All three were discovered without issues (along with default ones). Assigning backup policies to databases creates backup items. These can be reviewed on the Azure Portal. Similarly for backup jobs, navigating to the Vault resources allows to review the type of operation (configuration, type of backup etc.) and status. In this case, all completed as scheduled (including an additional manual backup created separately). Another place where it is possible to review the state of the backup is Azure Business Continuity Center. The Restore Testing the restore is a crucial part of any backup, as without that, all we have is a Schrodinger's backup - it might work or it might not. In the testDB I created a small sample table. This only contains a few names as examples. I ran a manual backup of the testDB to capture its current state. Then deleted an item. Bye Bob. Confirmed Bob is gone. Afterwards, I run the restore operation, selecting the manual backup created in the previous step. The restore operation was triggered. And after successful completion, Bob was back. The restore can also be confirmed in the Azure portal. The Summary The testing recorded above indicates the Azures Backup for SQL Server running on Azure VM can be used to discover databases within the SQL instance and help with setting up protection, even if the data resides on an external storage such as Pure Storage Cloud Dedicated. As mentioned in the disclaimer in the beginning of this post however, there may be scenarios that could show different results, so always run tests before committing any decision to production environments.
