You’re now reading the 4th and final post in my SQL Server Backups to Azure series. In previous posts I’ve described how to set up an Azure Storage Account, Blob Storage container and how to take SQL Server backups there. This time we’ll take a look at one more option that puts our backups on autopilot, with destination to cloud!
The feature I am writing about is Managed Backups, an option that has been available since SQL Server 2014 but one that I have not seen often used. One reason for that, maybe being, that there’s an unfixed feature that makes enabling it a bit difficult if you’re not aware of the workaround.
Despite this shortcoming, if you have SQL Servers (on-premise or Cloud) but no DBA to care for them, I’d recommend that you take a look at this option.
What are managed backups?
Managed Backups is precisely what it sounds like, a system that automates the process of backing up your databases using Azure Blob Storage. It can be defined at the instance level to handle all the databases, or just for the specific database(s). You can also leave it to fully automatic mode, when it backups databases when certain criteria is met, or you can do some tuning to that as well.
Depending on your database workloads, the defaults might be enough for you. There is also one limitation to be aware of, it will only take Full and Log backups. If you have restore strategy designed around Full, Differential, and Log backups to meet business continuity requirements, this might not be the right option for you.
Managed Backup Defaults
Without any configuration, the Managed Backup will perform backups when the following criteria have been met. In my opinion, these aren’t bad defaults, even when considering the transaction log backups.
Full Backup
- When you enable Managed Backup
- Log has grown 1GB or more since the last full backup
- 7 days have passed without full backup
- Broken log chain is detected
Log Backup
- If no log backup history is found, like when you enable the Managed Backup for the first time
- 5MB or more of transaction log is used
- 2 hours have passed without transaction log backup
- If log backup is behind the full backup
Now that we know what the defaults are, let’s look at how to…
Set up Managed Backups
We’ll be using the same Storage Account and the Blob Storage we created in Part 2, so if you haven’t already created them, go to that post and check out how to do it. If you’ve already done that part, the first thing we’ll need to do is to tweak the SQL Server Credentials a little to be able to work around that unfixed issue mentioned earlier, and to enable the Managed Backups. To achieve this, go to Azure Portal and to the Storage Account and under Settings find Access keys.
Copy the Access key and now edit your credential, changing the password to the key, no need to change anything else.
After enabling the backups, we have to set the credential back to using Shared Access Signature, which means that you’ll have to generate a new one (see Part 2 for instructions on how to generate the SAS token).
One more thing to mention here is, that while I have been using GUI previously to enable these settings, etc., there’s no GUI to enable and to configure Managed Backup. To enable it and to configure anything, you need to use specific Stored Procedures for the tasks. The basic configuration is simple as you can see with the code below.
USE msdb;
GO
EXEC managed_backup.sp_backup_config_basic @enable_backup = 1
, @retention_days = 7
, @database_name = 'DBAUtility'
, @container_url = 'https://testingstoacc.blob.core.windows.net/db-backups'
GO
After executing the command you’ll see message similar to this.
After enabling the Managed Backup feature, two backup files should appear on the Blob Storage defined in the URL, one with BAK extension and another with LOG extension. Note that this might take some time, depending on the size of your databases. These are the initial Full and Log backups that are taken when the feature is enabled.
After we’ve verified that the initial backups were taken successfully, the next thing we need to do is to go and generate a new SAS token, which will replace the Access Key in the credential. So generate like previously and then copy and paste to credential password field in the Management Studio.
You can test the Managed Backup by either generating data and transactions to trigger the log backup, or just by waiting for it to run eventually. Since I didn’t have patience to wait for 2 hours I ran some INSERTS into my own database and true enough, a new log backup was generated.
Other configuration options
I will not go into too much details with these, but some of the configuration options that you might want to look into, if using Managed Backups in production are:
- Email notifications for warnings and errors with backups, configured with managed_backup.sp_set_parameter Stored Procedure
- Backup encryption, configured with managed_backup.sp_backup_config_advanced Stored Procedure
- Customized Backup Schedule, configured with managed_backup.sp_backup_config_schedule Stored Procedure
Wrapping it up
For any organization with no DBA to manage their backups and restores, I’d warmly recommend Managed Backups. There’s definitely some setup to do alongside the workaround for the annoying bug with the SAS Token / Access key (which, I assume, will get fixed at some point), but it’s still a reasonably good way to back up databases.
And with that, I’ll wrap up this 4 post series about SQL Server backup to Azure. Hopefully, you’ve enjoyed reading it! To read the previous posts in this series, follow the links below.
Leave a Reply