Automated backups for SQL Server on Azure VMs.

Date created: May 20, 2020.
 

There are 4 options available when it comes to make backups of SQL Server databases on Azure virtual machines: Automated Backups, also known as Managed Backups; there is also Azure Backups for SQL VMs; the third option is manual backups; and the last option is third party backup software like Veeam, Commvault, and Netbackup.

In this article we are covering Automated Backups. Automated Backups allows you to schedule regular backups for all databases on a SQL Server VM. Backups are stored in Azure storage for up to 30 days. Beginning with SQL Server 2016, Automated Backup v2 offers additional options such as configuring manual scheduling and the frequency of full and log backups. SQL Server 2014 instances can also be backed up using Automated Backups.

Some limitations of this type of Automated Backups are: the maximum retention period is 30 days, it is not intended to be used with AlwaysOn Availability Groups configurations, it is not intended for long-term backup policies, you cannot customize monitoring based on Azure Monitor logs, no centralized backup reports, you cannot consolidate email alerts for failures, you cannot use Azure Portal to restore a database, and the maximum database size that is supported is 12 TB.

The bright side about Automated Backups as a backup option is: it has a 5-minute RPO (Recovery Point Objective); you pay only for the price of the storage account, there are no extra fees for protecting the SQL Server instance; you can restore the database using SSMS and T-SQL; it supports user and system databases; it supports all recovery models; this type of backup has the support of most of existent tools and interfaces like PowerShell, CLI, ARM and the Azure Portal.

The SQL IaaS Extension Agent is required to configure the managed backup at the SQL Server instance level.

To configure automated backups when you are creating a SQL Server VM, please try the following steps:

First visit this URL to start on Azure SQL blade shown below. Make a click on "+ Add" to start creating a SQL resource,

On the screen shown below, select the image of SQL Server you would like to deploy. After that click on the Create button as shown on the next image.






On the "Create a virtual machine" screen provide the name of the machine, resource group and some basic information, and after that go to the "SQL Server Settings". After that scroll down on that screen until you see "Automated backup".





Enable "Automated backup" as shown on the next image.





Once you enable the "Automated backup" option, you can specify the Azure storage account you will use for this purpose, the retention period in days, if you would like to encrypt backups (the key use for encryption will be placed on the storage account), you can also specify if you will like to have backup of system databases, and finally you need to choose what type of schedule the backup will have.

Let's explain a little bit about the types of backup schedules. The automated backup schedule is based on retention period selected. You will have backup full with maximum interval of one week or when the log growth more than 1 GB. This type of backup schedule also provides backup of log files with a maximum interval of 2 hours or a log space used greater than 5 MB.

Meanwhile", the manual backup schedule provides daily or weekly full backups, and backup of logs that occur on intervals between 5 and 60 minutes.













After configuring automated backups you can click on "Review + create" button to review all the configurations you have made and then proceed to create the VM.


 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.