Azure-docs: Monitor Automated Backup status

[Ayanmullick]

How do I Monitor Automated Backup status thru Log Analytics and create an email alert on failure?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 14384c8d-73a4-f0e9-e749-e6b02c0fceab
• Version Independent ID: 0a657108-aa72-d843-e640-c94bafa1fdb7
• Content: Automated Backup for SQL Server 2014 Azure Virtual Machines
• Content Source: articles/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-backup.md
• Service: virtual-machines-sql
• GitHub Login: @rothja
• Microsoft Alias: jroth

[mimckitt]

@Ayanmullick thanks for reaching out. Seeing as the information you are looking for is along the lines of guidance and implementation rather than on this specific document I suggest you ask your inquiry in the forums.

Please post your question here so our engineers and properly assist.

[Ayanmullick]

Monitoring is a basic functionality and a link to it's documentation should be mentioned in the 'Next steps' section of this document.

[mimckitt]

I can assign this to the author to review and consider adding those steps.

@rothja can you please take a look?

[rothja]

@Ayanmullick Thanks for the question, and you're right that how to monitor is an important question. Give me a chance to look into this and I'll see if I can provide a best practice. Thank you.

[rothja]

@Ayanmullick I've asked internally and have had some initial feedback from our support engineers. Basically, Automated Backup is using SQL's managed backup feature behind the scenes. So monitoring techniques that support managed backup should work with Automated Backup as well. You can get alerts by using the smart_admin.sp_set_parameter procedure like this:

Use msdb
Go
EXEC smart_admin.sp_set_parameter @parameter_name = 'SSMBackup2WANotificationEmailIds', @parameter_value = '[email protected]'

To get the emails we need to configure database mail configured and SQL agent alert system. On azure we need to enable SendGrid for azure to send emails, https://docs.microsoft.com/en-us/azure/sendgrid-dotnet-how-to-send-email. Once sendGrid is created, configuration will contain user name and SMTP server name. You have to use these settings is SSMS on your SQL VM when you enable "Database Mail". You also have to change the properties of SQL Agent under Alert System and Enable Mail Profile. Finally, you'll have to make sure that SMTP port is allowed both through the VM's local firewall and through the network security group rules associated with the VM.

This is one way to receive alerts by email, and I know it is probably too much information for a reply in this issue. I'm looking to see if there are other suggestions as well and what we would like to recommend in the article. However, the main point is that if you find resources that tell you how to monitor managed backup, you can likely work with those techniques to get reporting for Automated Backup for SQL VMs.

Does this help? Thanks!

[Ayanmullick]

Identified source and category in Application event logs. Testing to simulate backup failure to get the event ids to create Log Analytics alerts.

Source|Category
-- | --
MSSQLSERVER| Backup
SQLBackupToUrl| -

[rothja]

@Ayanmullick Were you able to test the backup failure to see if these event log entries helped you? Thanks!

[Ayanmullick]

Identified 2 event ids 57008 and 55523 occurring during backup failures. Created rules. Simulated failure. Awaiting notification.

Couldn't find a way to edit the Smart Admin automated backup schedule thru the Azure Portal or the Sqlserver powershell module.

[rothja]

@Ayanmullick Great. Let me know if you get the notification you were expecting. You should be able to edit the Automated Backup schedule if you are using SQL Server 2016 images. They updated managed backup in 2016 to allow for this. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-backup-v2#configuration-in-the-portal

[Ayanmullick]

It has proved difficult to reproduce non-informational events for Source : Microsoft SQL Server Automated Backup. Still on it.

We are using SQL 2014. SQL smart-admin schedule-editing should be doable thru Powershell too.

[rothja]

@Ayanmullick Hi. Here are some other suggestions for monitoring that might help. I'm passing these on from a contact in our product team:

1. Configure email notifications, this is not configurable beyond who the mail is sent to.
2. Use the msdb view vw_autoadmin_health_status. This is what the email notification do, just reports counts of errors and loops over the past ~30 minutes.
3. Use the table valued function managed_backup.fn_get_health_status. This will report the same things as above but the window is configurable.
4. Roll your own. Customers can look at the definition of managed_backup.fn_get_health_status to query the Managed Backup xevent log and raise whatever information they would like.

Also, I don't think you can edit the schedule of automated backups for 2014. Are you finding that working for you? Thanks!

[rothja]

@Ayanmullick I'm going to summarize some of the monitoring suggestions in the topic. At that point, I'm not sure if I have any more monitoring suggestions to propose to you. Do you have some ideas here that you can work with for monitoring? If so, I'll make the article updates and close this issue. Thank you!

[rothja]

@Ayanmullick I'm checking in my monitoring suggestions now and will publish them soon. In doing so, I did think of one thing I should point out. It appears you are on SQL Server 2014? If so, then the schema for Managed Backup functions and procedures is always smart_admin rather than managed_backup. In fact on 2016/2017, the smart_admin versions still work but simply call the managed_backup implementations with the same name. Thanks, and I hope that some of these ideas got you closer to the monitoring solution you were looking for. I apologize if they were not exactly what you wanted.

[Ayanmullick]

The below query creates Smart-admin SQL-backup failure-alerts thru Log Analytics.

Event |where Source contains "Microsoft SQL Server Automated Backup" and EventLevelName != "Information"

We tested, it works on an Azure VM with SQL 2016 and not on 2014

[rothja]

@Ayanmullick Thanks for the info on how to do it with Log Analytics. Does SQL Server 2014 not generate the same event log messages on failure?

[Ayanmullick]

Correct. SQL 2014 isn't creating any non-informational events for Source: Microsoft SQL Server Automated Backup upon Smart admin backup failure.

[rothja]

@Ayanmullick Sorry about that. It must have been a 2016 improvement to do so. The only solution I could see would be to have some type of background service that generates and event you could capture based on queries to the stored procedures / functions I provided earlier. Sorry there is not a better solution for 2014.

[rothja]

@Ayanmullick I've published the changes to the Automated backup docs about the monitoring recommendations. We have not yet tested capturing events as you have, so I did not include that suggestion yet. At this point, I don't have any better recommendations, so I'm closing the issue. But please reach out if you have more questions or information on this and we can reopen if necessary. Thanks for improving the docs with your feedback!