As you know, Azure SQL is available either as PaaS or IaaS.
Well, you can now enable and run SQL best practices assessment on SQL server running on virtual machine.
To do so, you need:
- to have deployed an Azure SQL virtual machine
- the SQL virtual machine must be with the SQL Server IaaS extension in full mode (https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-agent-extension-manually-register-single-vm?tabs=bash%2Cazure-cli#full-mode/)
- SQL Server version must be 2012 or later
- a Log Analytics workspace deployed in the same region than your SQL virtual machine
The SQL best practices assessment feature of the Azure portal identifies possible performance issues and evaluates that your SQL Server on Azure Virtual Machines (VMs) is configured to follow best practices.
If you have all the prerequisites in place you can now enable the best practices assessment by accessing your SQL virtual machine (not the virtual machine itself) (https://portal.azure.com/#blade/HubsExtension/BrowseResource/resourceType/Microsoft.SqlVirtualMachine%2FSqlVirtualMachines)
Then select the SQL virtual machine on which you want to enable the assessments and access the Settings\SQL best practices assessment blade to enable it.
When enabling the assessments you will be ask to select the Log Analytics workspace to use for uploading the results.
When enabling the assessments you can also enable a scheduling to run the assessment regularly.
Once enabled you can manually start the assessment from the same blade using the Run assessment button.
An assessment can take some time to complete depending of the number of databases and objects.
You can also review the results from the same blade under the Assessment list by clicking on the assessment you want to see
You can also have a trend view to help you identify the progress on conforming with best practices
If you want/need to change the Log Analytics workspace used, you will need to access the Configuration to disable the assessment and then enable it again
How large is the log analytics workspace for this tool?