Problem
I have a number of SQL Servers running on Azure SQL VMs. As a DBA, I am looking to back up the SQL databases running on the Azure SQL VMs. What are the database backup options that are available for disaster recovery?
Solution
This tip assumes that you already have resources in your Azure environment and you are familiar with the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200 towards your pricing.
In this tip, we will discuss the options that are available for performing backups of the SQL Server databases that are running on Azure SQL VMs. The Azure SQL VM is basically an Infrastructure as a Service (IaaS) offering in Azure and will be similar to you managing your SQL Server databases running on on-premises servers. You can deploy an Azure VM in a few simple steps from a template. You can refer to this tip which describes the steps for deploying an Azure VM from a template.
Option 1- Backup databases on to a separate data disk
This option is quite straight forward. This is similar to configuring your backups to occur directly on disks like how you perform backups on your on-premises servers. You may configure the automated backups using maintenance plans or scripts (T-SQL or PowerShell) and point the backup path to a disk within the Azure virtual machine. With this approach, you have the additional overhead of managing an additional data disk. However, there may be limits on the number of data disks you can attach based on the size of the Azure VM. You can refer the screenshot below where you can select the option to create a new data disk to perform the database backups.
Once the new disk is created as per the required storage type and size, you can configure maintenance plans or jobs to perform the backups directly on the data disk. You can refer to this tip for steps on how to configure maintenance plans.
Option 2-Backup SQL databases to Azure storage accounts
Another option is to use the Azure storage accounts using the “backup to URL” option.
As a first step, you need to create an Azure storage account within your subscription in order to make use of the Azure blob storage service. Once it is done, we can create containers within the storage account to store the database backups. It is preferable and safer to use a concept known as Shared Access Signatures (SAS) for granting access to the containers as opposed to you sharing the storage account name and its associated access keys. To learn more about shared access signatures, you may refer to this tip which describes the details and benefits of using shared access signatures.
We have the option to use either the block blob or page block for SQL Server backups. However, it is advisable to use block blob for SQL Server backups due to added benefits like lower cost, performance benefits and security. You can refer to this tip for complete steps on how you can perform backups using Azure blob storage. As you can see from this tip, the instructions include steps for creating the storage account, container, credentials and performing the backup. The backups can be performed using usual backup commands or through maintenance plans. In order to configure backups using maintenance plans, you may refer to this tip which has the step by step instructions.
The tips linked so far make use of access keys used in the storage account which is not the preferred option. In order to make use of shared access signatures we need to make use of the SAS tokens. This will help us to perform block blob SQL backups instead of page blob SQL backups. You can get the SAS token when you click on the “Shared access signature” option of the container.
You can click on the option to copy the blob SAS token so that we can create the new credential.
CREATE CREDENTIAL [sas_method] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sp=r&st=2021-06-05T08:41:12Z&se=2021-06-05T16:41:12Z&spr=https&sv=2020-02-10&sr=c&sig=S9TA%2FBdjHzmV5UE2DUamxBW9%2BX7svhPBWRSpfljo0hI%3D';
You can provide any name for the credential. However, make sure to copy the correct Blob SAS token. Once the credential is created, you can use below backup command to perform the backup.
BACKUP DATABASE moun -- database name TO URL = 'https://sqlbackupsmo.blob.core.windows.net/sqlbackup/moun_DB.bak'; -- give correct path GO
You can get the full URL from the properties option in the container. Once the backup completes, you can view the database backup file in the container. If you review the blob type it will be of type “Block blob”.
If we have had used the storage account access keys instead of shared access signature, it would have created a SQL backup file of type “Page blob”. You can refer to this earlier tip which makes use of the storage account access keys. You can use the below script to create a credential which makes use of storage account access keys.
CREATE CREDENTIAL [storage_account_access_key] WITH IDENTITY = 'sqlbackupsmo' -- storage account name ,SECRET = '/DVe809tRAHKKGNeHE+P4cxNljvz0SGCZbjyQJHpr4+6iNnPFxiEiItUvD3HsY0Fkkv1aDzi/he19etwAb2mg=='; -- Access keys
Once this credential is created, perform a SQL database backup using the below script.
BACKUP DATABASE moun
TO URL = 'https://sqlbackupsmo.blob.core.windows.net/sqlbackup/moun_access_key.bak' -- get this from properties
WITH CREDENTIAL = 'storage_account_access_key' -- enter credential name
GO
Once the backup completes, you can validate the backup file in the container. The blob type of the backup file will be that of “page blob”.
In this option, you saw the ways by which we can perform SQL database backups to a URL.
Option 3- Perform SQL backups using Microsoft Azure backup service
Another easy option to perform SQL Server backups on Azure VMs is to make use of the Azure backup service.
With the Azure backup service, it is quite easy to perform backups of SQL Server databases on Azure VMs. You can refer to this tip which has detailed instructions to configure backups for SQL Server databases on Azure VMs. This is a zero-infrastructure solution which is cost effective and at the same time it is easily scalable. Imagine, if you have to build or develop an on-premises solution in your data center for this purpose. It would be quite expensive to build and there will be additional overhead of managing the on-premises infrastructure. All of which will substantially add to the cost of the managing the backups. But with performing SQL backups using Azure backup service, it eliminates all of these unnecessary costs if you have to manage something like this on an on-premises backup infrastructure.
Moreover, if you notice, in recent days there have been a number of cases of high visibility incidents related to security where critical infrastructure is compromised leading to complete loss of access to data for critical applications for some organizations globally. If you refer to this tip, you will notice that the Azure backup makes use of something known as the Recovery Services Vault which handles the backups and helps in database discovery for SQL databases in Azure VMs. All of these steps are very straight forward and can be configured in a few simple steps thereby helping to centrally manage the backups across Azure VMs.
Another important point is the process to restore the databases is also simple and straightforward to follow. You can refer to all of the steps to restore SQL databases using the Azure backup service in this tip. As you can see, the restore operation is quite intuitive and can be easily performed in few simple steps with very little training.
To sum it up below are some important points regarding this option:
- Easy to manage
- Zero infrastructure backup solution so removes unnecessary management overhead
- Highly secure
- Easily scalable as it is in the cloud
- Cost effective
Option 4- Perform backups using file snapshot backups
If the SQL Server database files themselves are stored on the Azure blob storage, then we can make use of this option which helps to perform snapshots of the database files directly on Azure blob storage.
This feature was introduced starting in SQL Server 2014 where the data files can be stored on Azure blob storage instead of locally on the server. There are number of benefits with this approach especially at the time of database migration where it helps in fast migrations. Other benefits were around cost advantages and management of storage capacity as ability to scale on the cloud was limitless and we need not worry about disk space shortages like we usually do while dealing with capacity for on-premises storage. One of the other benefits of this feature was the ability to perform file snapshot backups.
The file snapshot backup will perform snapshots of the database files stored in Azure storage. Using this option, we can perform full database backups as well as transaction log backup similar to traditional backup methods. As a first step, create the storage account, then the container and then follow the steps to make use of the Shared Access Signature. You can refer to this tip on steps to create the storage account and container. Once done, you can make use of normal commands in order to create database files on Azure blob storage. You use the below command for performing a file snapshot backup of database in SQL Server Management Studio (SSMS).
BACKUP DATABASE motest TO URL = 'https://sqlbackupsmo.blob.core.windows.net/sqldbcontainer/motestdb.bak' -- provide correct path WITH FILE_SNAPSHOT; GO
Basically, you make use of the option “File_snapshot” for performing this type of backup. This will work as long as the database files are within Azure blob storage. If you run the same command on a database whose database files reside locally, you will get an error message as shown below.
As you can see, for file snapshot backups to work all database files need to be in Azure storage. The restore process is also much simpler as compared to using backup files from native SQL backups. These will be discussed in detail in another tip.
Next Steps
This tip highlighted the various options that are available to perform database backups of SQL Server running on Azure VM. However, you could try out these options for your SQL Servers running locally on premises as well.
- In this tip, you learned options to perform SQL backups on an Azure VM.
- As a first step, you can deploy an Azure VM using a template as described in this tip.
- You could try the steps described in option 1 by referring to this tip.
- You could try the steps described in option 2 by referring to the tips linked in that section.
- You could try the steps described in option 3 by creating a Recovery services vault and following the steps described in the tips linked in that section.
- You could try the steps described in option 4 by first creating a database with the database files directly on Azure blob storage, once done perform the backup using the command in that section.
