Azure Automated Backup allows you to schedule regular backups for all databases on a SQL Server Azure IaaS VM. Backups are stored in Azure storage for up to 30 days. SQL Server 2016 and higher VMs offer more customization options with Automated Backup v2. These improvements include:
- System database backups
- Manual backup schedule and time window
- Full and log file backup frequency
For more information, check my previous article on How to backup SQL Server named instance to Microsoft Azure Blob Storage.
Once you have SQL Server managed backup up and running in Azure, then you need to make sure that backup is working properly and you don’t have any issue.
Starting with SQL Server 2014 and later, SQL Server Managed Backup to Microsoft Azure has built-in measures to identify problems and errors during backup processes and remedy with corrective action when possible. However, there are certain situations where your intervention is required.
In this article, I will show you how to enable monitoring for SQL Server managed backup in Azure, and finally enable email notification for any errors that need to be addressed.
Monitoring SQL Server Managed Backup
The SQL Server Managed Backup to Microsoft Azure periodically reviews scheduled backups and attempts to reschedule any failed backups. It polls the storage account periodically to identify breaks in log chains affecting recoverability of the database, and schedules new backups accordingly.
First, you can poll the backup health status by calling managed_backup.fn_get_health_status.
Open SQL Server Management Studio (SSMS) and run the following query. This query returns a table of aggregated error counts for each category that can be used to monitor the health status of SQL Server Managed Backup to Microsoft Azure.
SELECT * FROM managed_backup.fn_get_health_status(NULL, NULL)
These aggregated counts can be used to monitor system health. For example, if the number_ of_retention_loops column is 0 in 30 minutes, it is possible that retention management is taking long time or even not working correctly. Non-zero error columns may indicate problems and Extended Events logs should be checked to discover the problem.
Next, you can call managed_backup.sp_get_backup_diagnostics stored procedure to find the details of the error and start investigating.
Open SQL Server Management Studio (SSMS) and run the following query. The following example returns all the backup events logged for the past 30 minutes.
Use msdb Go EXEC managed_backup.sp_get_backup_diagnostics
This was the manual monitoring approach.
Enabling Email Notification for SQL Backup Health
Another option is to automate the monitoring of SQL Server Managed Backup by taking advantage of the built-in Database Mail feature for notifications.
SQL Server Managed Backup to Microsoft Azure includes a notification mechanism that is based on SQL Server policy based management policies. Now take the following steps:
- You need to configure Database Mail in SQL Server, you can use the steps described here to enable Database Mail.
- Set database as the Mail System for SQL Server Alert System: In SQL Server Management Studio (SSMS), right click on SQL Server Agent, select Alert System, check the Enable mail profile box, Select Database Mail as the Mail System, and select the Mail profile that you created in Step 1.
- Verify that the SMTP port is allowed both through the local VM firewall and the Network Security Group (NSG) for the VM in Azure.
- You need to set the value for the specified Smart Admin system parameter by calling managed_backup.sp_set_parameter. Run the following query in SSMS query window and provide the e-mail address. The following example enables email notifications for errors and warnings only and sets the email ID to use to send the notifications to. This creates a SQL Server Agent job that is used to gather health status and send notifications when there is an error or an issue with SQL backups.
Use msdb Go EXEC managed_backup.sp_set_parameter @parameter_name = 'SSMBackup2WANotificationEmailIds', @parameter_value = '<email@example.com>'
- Yon can run the following query to check if Backup is enabled and set to True, and the email ID specified for notifications.
Use msdb Go SELECT * FROM managed_backup.fn_get_parameter (NULL)
- The last step is to test that you are receiving notifications when your backup is not in healthy state. To simulate this scenario, you can delete few backup files from your Microsoft Azure Storage container, and then wait for 15 minutes and see if you get any email notification. In my example, I deleted few backup files and received the following email.
Hope this article was useful to you and happy monitoring day!
Thank you for reading my blog.
If you have any questions or feedback, please leave a comment.