Problem
Is there any way by which we can centrally manage database backups across a number of SQL Server instances running on different Azure Virtual Machines (IaaS)? I have heard of a new feature that is available using Azure backups which allows us to easily perform backups of databases on the Azure Virtual Machines (IaaS). Can we have a demo in this tip?
Solution
Azure backup is a zero-infrastructure solution that is available in Azure that can be used to perform database backups of SQL Server instances running on Azure. This is an easy to use tool that can perform the all-important function of backing up the databases in a few simple steps. Comparing this to traditional backup solutions, this utility is far ahead in terms of safeguarding your databases as everything is centrally manageable and offers features like long-term retention and auto protection. The backups are performed using the Recovery Services vault which is the central entity for backing up the resources.
Azure Recovery Services Vault
As a first step, logon to the Azure portal and then set up the recovery services fault. Go to the “All Services” section as shown and search for “Recovery Services Vault”.
Click on the “Recovery Services vaults” option and you will get this window. Click on the “Add” option as shown.
Once done, enter the details for the Recovery Services Vault as shown. Click on “Create” after filling the details.
Under the “Notifications” section on the top corner of the Azure portal, you will see confirmation that the deployment completed successfully.
Click on the “Go to resource” option to view the newly created Recovery Services vault. Once done, you will view the “Overview” section of the Recovery Services vault.
Click on the “Backup” option to check the details of any backup items. In the screen, you see zero backup items.
Click next on the +Backup symbol to start your backup goal. You will get this screen as shown. In the first question “Where is your workload running”, you will see these options to select.
In our case, for this demo, we select “Azure”. Once done, for the next question “What do you want to backup”, you select “SQL Server in Azure VM” as our plan is to back up the SQL Server databases running on an Azure VM.
Discovery of databases for Azure Recovery Services Vault
Once you select the options for these questions, the next step is to start the discovery process to identify the databases that need to be backed up. Refer the screenshot below. Click on the “Start Discovery” option to start the discovery process.
This will start the process of discovery and usually takes a few minutes based on the number of servers that are available. In this screenshot, you can see the process running to discover the Azure VMs.
Once the process completes, you can view the list of servers that were discovered.
In this demo, we would like to back up the databases on “sql2019”. Select this server as shown and click on “Discover DBs”. Once done, the deployment process will start which you can view by clicking on the top right hand corner of the portal.
Once you click on the “Deployment in progress” option, you can view details of the deployment.
This is an operation that happens in the background in order to manage the backup workload.
Confirm that the databases were discovered correctly for Azure Recovery Services Vault
In order to confirm that the databases were discovered as per your selection, go back to the Recovery Services Vault you created and select the Backup goal (by selecting +Backup) and click on the “View Details” as shown. Here, you can view the confirmation of the selection and the number of databases that are available to be backed up.
Once you click on “View Details”, you can view the confirmation of the backup readiness of the selected server. In our case, it is the demo “sql2019” server.
Click on the “Backup Goal” option to go back to the menu and select the Step 2 to Configure Backup.
Configure Backup for Azure Recovery Services Vault
This has two steps - first select the items to backup and then select the backup policy. Here, database [C100] is selected to be backed up. Click “OK” once done. You can also see the option - Autoprotect. This can be useful especially in dynamic environments where databases are constantly created or deleted. This feature will ensure that no new databases are missed from the backup. With Autoprotect, Azure takes care of the process of discovering the databases and you do not have to do this manually.
In the next section, configure the backup policy. I have just selected the default, that is, “HourlyLogBackup” and click OK. You can see in this backup policy, the full backup occurs once followed by the hourly log backups.
Once done, make sure to “Enable Backup” which will start the process for deployment which you can see in the right-hand side top corner of the Azure portal.
Once the deployment process completes, you can see the status of deployment as shown on the top right-hand corner.
Reviewing the configuration on the Recovery Services Vault
Now that you have configured the backup of the databases on the Azure VM, we can check the recovery services vault to validate if everything is correct. Go back to the Recovery Services Vault home page as shown to check the backup items that were configured as part of the setup. Refer the screenshot below.
Once you click on the “Backup Jobs” item on the left you can see that the SQL Server is registered successfully and also the configuration for the backup job for the database c100 is completed successfully. Next click on the “Backup Items” section.
Click on the “SQL in Azure VM” to check details of the Azure VM. Once done, you can view details of the database -C100 that was configured for performing the backup.
You can see that the backup status is “Healthy” and when you login to the Azure VM and check the database, you can validate that the full backup for the database has completed successfully.
I noticed that this backup process takes some time to trigger, but you can validate if it completed successfully as shown above or you can manually trigger the backup from the backup item configuration in the recovery services vault.
With these steps, you have successfully configured backups for your databases using the Azure Backup for SQL Server on Azure VMs. In the next tip, you will see a demo on how to restore the databases that you configured for backup. You will see in detail step by step instructions for the restore operations using Azure backup.
Next Steps
- In this tip, you learned about using the Azure backup for SQL Server on Azure VM
- In this tip, you learned how the backups can be configured in few simple steps
- You also learned that this backup solution requires zero infrastructure and can be centrally managed
- Features like Auto-protect further enables automation as Azure itself takes care of any new databases that are added
- Try out the Azure backup feature described in this tip and explore the options using a demo database
- Refer to the different Azure articles available on MSSQLTips
