Azure Backup for SQL Server public preview

by Pythian Marketing on Jun 5, 2018 12:00:00 AM

Backup for SQL Server on an Azure VM was released on June 4, 2018 for public preview by Microsoft. This feature provides the ability to centrally manage your database backups via Azure Backup service in the portal, and offers various other features such as alerting and reporting. You can see the pricing for this feature while in public preview here. While in preview, you will get 50% off the expected full pricing of the feature when it goes to General Availability (GA).

NOTE: The feature discussed in this post is NOT production ready. Microsoft is releasing it to the public for you to begin testing and so they can gather feedback.

SQL Server backups

To be sure we are on the same page starting out, I want to be clear that when I (and Microsoft) refer to "backing up SQL Server on Azure VMs," it is strictly the database-level backups. You can back up both system and user databases in SQL Server. This allows you to recover a database from a potential corruption of data, accidental deletion of data, or any other reason you may need to recover a database running in SQL Server. This does not cover databases in the PaaS service Azure SQL Database, only SQL Server running on Azure VMs. While running SQL Server on an Azure VM, we have had various methods to use for database backups starting with SQL Server 2012 and up. The majority of the list below works on your on-premise servers as well. The list below is a general synopsis of what is currently offered for production workloads:

Backup to URL (SQL Server 2012+)

With this method, you utilize Azure blob storage, and SQL Server has the ability to write the backup directly to the cloud storage. This is commonly used to meet offsite backup requirements for various compliance requirements. It is available for SQL Server 2012 and higher. You can find more information using this link.

Managed Backup (SQL Server 2014+)

This method was first released in SQL Server 2014. You are letting SQL Server itself determine the frequency and type of backup to take for the databases on that instance. It utilizes Backup To URL under the covers and is backing up the database to the blog storage in the same manner. You can find more information using this link.

Automated Backup for SQL Server VM (SQL Server 2014+)

You have to love the naming convention Microsoft sometimes uses for features. This particular backup method is enabled and configured at the VM level of an Azure VM. It utilizes a SQL Server IaaS Agent Extension that just basically configures and utilizes the Managed Backup method previously discussed. You can find more information using this link.

Azure Backup for SQL Server

This preview feature allows you to take backups of the system databases and user databases found via the Azure Backup service on an Azure VM running SQL Server. The backup types provided: full, differential and log. It is a VDI backup so it is similar to using 3rd party backup tools like NetBackup or Symantec BackupExec. The benefit you have here is that it is baked into the Azure portal for configuration, and includes monitoring and alerting options. You can also link PowerBI into the backup history and logs that are kept by the service for reporting purposes.

Getting started

Microsoft has published the details around the preview as well as a walk-through that covers getting started so I won't rehash all of it in this post. You can find that information here.

Quirks and limits

Understandably, features that are in preview will have their little quirks (maybe bugs) when you are using them, but that is the purpose of the preview program. This allows us to work with the feature and provide Microsoft with feedback on what we don't like or what we would like to see added. While the documentation (at least current version) shows the limitations you will have in the public preview, I have found a few others, and just some odd quirks using the feature. I cover these below. Overall, I do see this feature as something many of our clients can benefit from as it makes it more centralized to control and maintain backups of critical systems (once it goes GA) that require fairly common backup schemes.

Replication, site-to-site

A feature that Azure Backup has had for a while is the ability to take the backups of your Azure VMs from one region and replicate them over to another region for a host standby. This covers the event where a whole region outage is experienced for whatever reason. At this time it does not appear you can replicate the backups taken from SQL Server by Azure Backup.

Restoring and discovery

You have the option to restore a database backup taken by Azure Backup to another instance and database. You can only restore to a SQL Server instance that you have discovered. Which I guess you could relate this to a similar setup with, say, LiteSpeed or NetBackup, neither of which can restore a database backup to a cold system. It has to have been prepared to accept those backups by installing the agent and access granted. The main issue I have with this right now is the process of getting the services and login on the Azure VM with SQL Server. You will likely have read that part of the requirement to back up a database in SQL Server requires you to grant the service account NT Service\AzureWLBackupPluginSvc sysadmin privileges on each instance. Well, there is no one-click ability to deploy the services or that login to the Azure VM. If you happen to be using a SQL Server Azure VM from the marketplace, all of these will already exist, but not on an Azure VM you took and just installed SQL Server on yourself with your own license. In order to get the service and login created, you have to do one of two things: (1) enable Azure Backup of the VM itself or (2) run the database discovery on the Azure VM and just let it fail the first time, then add the login and run the discovery again. I think there needs to be a smoother method for deploying this to Azure VMs. Note that none of the processes right now require any type of restart or outage on SQL Server or the VM itself, just a bit of a pill to go through all that to me.

Backup policy one-to-one

When you go to configure a backup policy, there are some limitations to how you can configure it. Now one thing to note in that regard is the limitations around the policy indicate if you have fancy or very granular requirements for backups, this may not be the best option for you to consider. I have listed out a few of the things I found:

A full and diff cannot be done on the same day.
A database can only be joined to one backup policy, a 1-to-1 ratio. So you cannot use multiple policies for the same database.
Log backups at this time are limited to a frequency of 15 minutes up to 24 hours.

There are, however, some positives in the configuration options for the backup policies:

One scenario would be your application utilizes multiple SQL Server databases that are spread across multiple Azure VMs within a region. You can configure one policy for the backup scheme requirements and then just go through assigning those databases to that one policy.
You can have a grandfather-father-son backup scheme for your backup files with full backups. This can be useful for compliance requirements and make the cleanup/purge of backups much easier to maintain.

Final thoughts, at least right now

Overall, I do see this feature as something many will benefit from, providing centralized control of the database backups for production applications (and other environments). A large majority of recovery needs for databases can be covered using the feature, but there will likely be some that don't find it suitable. Obviously being in public preview there are some quirks and limitations that will be fixed it progresses to going GA. In the meantime though, I encourage you to give it a try with your Azure subscriptions. Test it out and if you find something that is broken or it is missing something you might need, provide feedback to Microsoft.

Topics: Oracle Amazon RDS Technical Track Cloud RDS Amazon Web Services (AWS) Oracle Datase

No Comments Yet

Let us know what you think

Share this