You can evaluate your SQL Server on Azure VM for configuration best practices using SQL best practices assessment feature on Azure portal. GA announcement post explains how to use this feature using Azure portal. In this post, we'll talk about how to enable and configure this feature, and start assessment runs at scale.
If you have more than a handful of SQL Servers on Azure VMs, you probably want to automate both the one-time operations like enabling and configuring the feature, as well as recurring operations like starting assessment runs. Ideally you run a script to enable the feature, configure it to use the correct Log Analytics workspace (to upload results), and set a schedule to do assessment runs in the frequency that works for your environment.
We have introduced new Azure CLI commands to enable this scenario. You can find the full list of commands and options here. You still need to use SQL virtual machine resource page on Azure portal to view the assessment results for a given SQL VM. Let's look at some examples.
Enable and configure
# Just enable the feature
az sql vm update --enable-assessment true --workspace-name "myLAWorkspace" --workspace-rg "myLARg" -g "myRg" -n "myVM"
# Enable the feature and set a schedule for assessment runs
# Schedule is set to every 2 weeks starting on Sunday at 11 pm (VM OS time)
az sql vm update --assessment-weekly-interval 2 --assessment-day-of-week Sunday --assessment-start-time-local "23:00" --workspace-name "myLAWorkspace" --workspace-rg "myLARg" -g "myRg" -n "myVM"
Start on-demand assessment
# This will start an on-demand assessment run. You can track progress of the run or view results on the SQL virtual machine resource via Azure Portal
az sql vm start-assessment -g "myRg" -n "myVM"
Disable schedule
# This will disable an existing schedule, however the feature will remain enabled. You can still run on-demand assessments.
az sql vm update --enable-assessment-schedule false -g "myRg" -n "myVM"
Disable feature
# This will disable the feature including any set schedules
az sql vm update --enable-assessment false -g "myRg" -n "myVM"
Sample script to enable and configure the feature on all SQL Servers on Azure VMs in a subscription
# This script is formatted for use with Az CLI on Windows PowerShell. You may need to update the script for use with Az CLI on other shells.
# This script enables SQL best practices assessment feature for all SQL Servers on Azure VMs in a given subscription. It configures the VMs to use a Log Analytics workspace to upload assessment results. It sets a schedule to start an assessment run every Sunday at 11pm (local VM time).
# Please note that if a VM is already associated with another Log Analytics workspace, it will give an error.
$subscriptionId = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
# Resource Group where the Log Analytics workspace belongs
$myWsRg = 'myWsRg'
# Log Analytics workspace where assessment results will be stored
$myWsName = 'myWsName'
# Ensure in correct subscription
az account set --subscription $subscriptionId
$sqlvms = az sql vm list | ConvertFrom-Json
foreach ($sqlvm in $sqlvms)
{
echo "Configuring feature on $($sqlvm.id)"
az sql vm update --assessment-weekly-interval 1 --assessment-day-of-week Sunday --assessment-start-time-local "23:00" --workspace-name $myWsName --workspace-rg $myWsRg -g $sqlvm.resourceGroup -n $sqlvm.name
# Alternatively you can use this command to only enable the feature without setting a schedule
# az sql vm update --enable-assessment true --workspace-name $myWsName --workspace-rg $myWsRg -g $sqlvm.resourceGroup -n $sqlvm.name
}
