Build email notifications for SQL Database Automatic tuning recommendations
After reading this blogpost, you will be able to build your own custom email notifications for SQL Database Automatic tuning recommendations. We have listened to our customers requesting this functionality and have created a custom solution based on readily available technologies on Azure.
SQL Database performance tuning recommendations are generated by Azure SQL Database Automatic tuning. This solution provides peak performance and stable workloads through continuous database performance tuning utilizing Artificial Intelligence (AI).
Tuning recommendations are provided for each individual SQL database on Azure subscription for which Automatic tuning is enabled. Recommendations are related to index creation, index deletion, and optimization of query execution plans. Tuning recommendations are provided only in cases when AI considers them as beneficial to database performance.
Email notifications for Automatic tuning
Some of our customers have indicated a need to receive automated email notifications with suggested SQL Database Automatic tuning recommendations to be able to view and build automated alerts. For example, when the solution recommends that an index should be dropped to improve database performance, some customers would prefer to be notified of such event. Another customer scenario is, for example, emailing automated tuning recommendations to different database administrators in charge of different database assets they are looking over.
To build automated email notifications to receive automatic tuning recommendations, please follow our step-by-step instructions, see how to build custom SQL Database Automatic tuning email notifications.
The solution we have devised consists of automating execution of a PowerShell script retrieving tuning recommendations using Azure Automation, and automation of scheduling email delivery job using Microsoft Flow.
Azure Automation enables scheduling of your scripts on Azure that could be used in many ways. In our example, it is used for scheduling the retrieval of automatic tuning recommendations from SQL databases on your Azure subscription.
The below is a screenshot of executing automated PowerShell script to retrieve SQL Database Automatic tuning recommendations using Azure Automation. The automation allows users on-screen display of script inputs, outputs, log files, errors and warnings for monitoring and troubleshooting purposes.
Microsoft Flow is used as a readily available out of the box solution to schedule and automate email delivery job for forwarding the retrieved database tuning recommendations using Office 365 integration. The automated schedule can be set to run in increments anywhere from each minute, hour, day, to a week, depending on your needs and preferences.
With further customization of the provided PowerShell script and Microsoft Flow workflows, you can customize the solution to automate emailing of tuning recommendations to various individuals and for different SQL Databases.
Microsoft Flow provides on-screen stats on execution of the automated jobs, i.e. showing success of email notifications sent out. See the example from our solution in the screenshot below.
Microsoft Flow analytics is helpful for monitoring as well as for troubleshooting the automation flows. Please note that in case of troubleshooting, you also might want to examine the PowerShell script execution log accessible through the Azure Automation app.
The final output of the automated email notification will look similar to the following email received after building and running this solution:
By further customizing the sample PowerShell script provided, you can adjust the output and formatting of the automated email to suit your needs.
The solution we have provided for you is the starting point from which you can build further and customize for your own scenarios. Some of the possible custom scenarios are for example, creating notifications based on the type of tuning recommendation received, sending emails to multiple recipients, or perhaps to different database owners.
Summary
With the solution provided, you will be able to automate sending of email notifications for Azure SQL Database Automatic tuning recommendations. The solution is using PowerShell script to retrieve tuning recommendations and Azure Automation to run it. The flow of automating recurring email delivery job with outputs from the PowerShell script was built using Microsoft Flow.
You might further customize the solution to build email notifications based on a specific tuning event, to multiple recipients, for multiple subscriptions, or databases depending on your custom scenarios.
If you are inclined to programming, please note there are also alternative ways through which automatic tuning recommendations can be retrieved from SQL Database, for example, through REST API calls, or by using T-SQL, alongside with PowerShell commands.
Please let us know how are you using this solution, and perhaps examples of how you have customized it for your needs? Please leave your feedback and questions in the comments.
Source: Azure Blog Feed