Azure Backup - Understanding the backup policy for SQL Server in Azure VM

Azure Backup can be leveraged to discover and backup the SQL Server deployed in the Azure VMs. The backup behavior is governed by the "Backup Policies". There are different policies specific to different kinds of workloads. In this post, we are looking at the backup policy for the backup of the SQL Server in Azure VM.

Policy Creation

Navigate to the recovery services key vault and then under the Manage, click on the Backup Policies. To create a new policy click on the "+ Add" button.

For the policy type, select "SQL Server in Azure VM". This will open the blade to Create the policy for "SQL Server in Azure VM".

In the blade to create the policy, you need to provide the below information:

1. Policy name - provide a descriptive name
2. Full Backup - this is the schedule and retention of the full backup of the databases on the SQL Server in Azure VM.
3. Differential Backup - this controls the differential backup of the databases on the SQL Server in Azure VM
4. Log Backup - this defines the log backup for the databases
5. SQL Backup Compression - you can enable or disable the backup compression. The default value is disabled.

For the full backup in the policy, if you enable it then at minimum you need to define:

1. Frequency - daily or weekly
2. Time and Time zone - when the backup will occur
3. Retention for the daily backup

Optionally you can also configure the monthly, weekly, and yearly backup retention policies.

The log backup policy determines the behavior of the log backups. At minimum you need to define:

1. The frequency of the log backups - it can range from a minimum of 15 minutes to a maximum of once every 24 hours
2. Retention of the log backups - it can range from minimum of 7 days to a maximum of 35 days

Practical Backup Considerations

Below are some general backup considerations based on my experience in different projects. These are only generic considerations and should be evaluated against your organization's compliance standards and specific requirements.

• The time for backup should be when the systems are not being used by any of the teams. If you work in global teams i.e. working around the clock, then ensure the timing when the system is under the least load.
• Use smaller retention windows in the non-production environments than the production environments
• If the data is not critical then usually Full backup is taken Daily and retained for around 14 days in non-prod environments. Weekly, Monthly, and Yearly retention periods are not required.
• In the prod environment, the Full backup is still taken Daily but the retention period is increased to more than 30 days. Max you can select is 9999 days. Also, based on your SLAs and compliance standards you should enable the weekly, monthly, and yearly backup retention periods as well.
• Differential backup can be skipped in the non-production environments based on the criticality of the data in the lower environments
• Log backup can be reduced to once every 2 or 4 hours with a retention period of 7-14 days in the non-prod environments.
• In the prod environments the Log backup can be set to 1 hour or less. The retention should be 14-35 days based on the SLAs. Note that 35 is the max you can go.