There has always been a requirement for a solution to receive automated email notifications with suggested SQL Database Automatic tuning recommendations to be able to view and build automated alerts. To follow up with this need Microsoft advised the solution that consists of automating the execution of a PowerShell script retrieving tuning recommendations using Azure Automation, and automation of scheduling email delivery jobs using Microsoft Flow.
[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Set up Azure Automation in a few easy steps with no coding required. Use Netreo preconfigured templates to get started in less than 5 minutes. Learn more > [/su_note]
Azure Automation enables scheduling of client scripts on Azure. We will cover scheduling the retrieval of automatic tuning recommendations from SQL databases on an Azure subscription.
The screenshot below demonstrates execution of an automated PowerShell script to retrieve SQL Database Automatic tuning recommendations using Azure Automation. This setup allows for the on-screen display of script inputs, outputs, log files, errors, and warnings for monitoring and troubleshooting purposes.
Microsoft Flow is used as an out-of-the-box solution to automate the delivery jobs for database tuning recommendations with the help of Office 365 integration. Delivery schedule can be set to run per the required time increments. Further customization of the PowerShell and Microsoft Flow workflow allows automating delivery of tuning recommendations to different recipients and for different databases.
Microsoft Flow provides on-screen stats on the execution of automated jobs. See the screenshot below for an example of the provided statistics.
The above analytics is useful for monitoring and troubleshooting the automation flows.
Here is a sample of the automated email notification received after running this solution:
It is possible to adjust the final output of the email by further customizing the PowerShell script.
The current solution is the starting point one can further build upon. Some possible use-cases could be notifications based on the type of tuning recommendation, sending emails to multiple recipients or different database owners.
The solution provided is built using PowerShell script to get the tuning recommendations, Azure Automation to run it and Microsoft Flow for automating the recurring email delivery job.
The solution may further be customized to collect notifications based on events, recipients, subscriptions or databases.
For those, comfortable with programming there are also the alternative ways to retrieve automatic tuning recommendations from SQL Databases. Some of the more common examples include REST API calls, or by using T-SQL, alongside with PowerShell commands.
[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Set up Azure resources monitoring and automation in a few easy steps with no coding required. Learn more > [/su_note]
