How to enable SQL best practices assessments for SQL VMs via Powershell (not CLI)

Hi Paul Sebestyen
Welcome to the Microsoft Q&A Platform.
Turning on “SQL best practices assessments” needs two things: (1) the toggle and (2) a Log Analytics workspace to land the results. The switch alone won’t light it up, and the parameter is plural: -AssessmentSettingsEnable. Depending on your Az module version, PowerShell may or may not expose a workspace parameter; if it doesn’t, use one Azure CLI line from your PowerShell session to attach the workspace once.

here’s a clean, copy-paste path you can choose from.

1. easiest (run in PowerShell, calls Azure CLI once)

This attaches the workspace and sets a schedule; then you can trigger an immediate run with PowerShell.

# If 'az sql vm' isn't available:
# az extension add --name sqlvm

# Attach workspace and set a weekly schedule (example: Mondays 23:00 local)
az sql vm update `
  -g <rg> -n <sqlVmName> `
  --enable-assessment true `
  --workspace-name <laWorkspaceName> `
  --workspace-rg <laWorkspaceRg> `
  --assessment-day-of-week Monday `
  --assessment-weekly-interval 1 `
  --assessment-start-time-local "23:00"

# (Optional) kick off a run right now from PowerShell:
Update-AzSqlVM -ResourceGroupName <rg> -Name <sqlVmName> -AssessmentSettingRunImmediately

pure PowerShell when your module exposes workspace parameter (Some Az.SqlVirtualMachine versions do. If you have it, use this.)

# Get LA workspace resourceId
$ws   = Get-AzOperationalInsightsWorkspace -ResourceGroupName "<laWorkspaceRg>" -Name "<laWorkspaceName>"
$wsId = $ws.ResourceId

# Enable assessment + bind workspace
Update-AzSqlVM `
  -ResourceGroupName "<rg>" `
  -Name "<sqlVmName>" `
  -AssessmentSettingsEnable $true `
  -AssessmentSettingsWorkspaceResourceId $wsId

# (Optional) schedule weekly and/or run once immediately
Update-AzSqlVM -ResourceGroupName "<rg>" -Name "<sqlVmName>" `
  -ScheduleEnable -ScheduleDayOfWeek Monday -ScheduleWeeklyInterval 1 -ScheduleStartTime "23:00"
Update-AzSqlVM -ResourceGroupName "<rg>" -Name "<sqlVmName>" -AssessmentSettingRunImmediately

pure PowerShell when the workspace parameter isn’t available (“Wire up” ingestion yourself, then use the existing switches.)

Ensure SQL IaaS extension is in Full mode (you already set -SqlManagementType Full).

Install/verify Azure Monitor Agent (AMA) on the VM:

Set-AzVMExtension -ResourceGroupName <rg> -VMName <vm> -Location <location> `
  -Publisher "Microsoft.Azure.Monitor" -ExtensionType "AzureMonitorWindowsAgent" `
  -Name "AzureMonitorWindowsAgent" -TypeHandlerVersion "1.10" -EnableAutomaticUpgrade $true

Create/choose a Log Analytics workspace; capture its ResourceId to $wsId.

Create a Data Collection Rule that ingests the SQL Assessment output (custom text logs) and associate it to the VM and workspace:

# sketch only – fill in inputs for New-AzDataCollectionRule and New-AzDataCollectionRuleAssociation
New-AzDataCollectionRule ...
New-AzDataCollectionRuleAssociation ...

Then enable/schedule with:

Update-AzSqlVM -ResourceGroupName <rg> -Name <sqlVmName> -AssessmentSettingsEnable $true
Update-AzSqlVM -ResourceGroupName <rg> -Name <sqlVmName> -ScheduleEnable -ScheduleDayOfWeek Monday -ScheduleWeeklyInterval 1 -ScheduleStartTime "23:00"

why your previous command didn’t work

-AssessmentSettingEnable → the correct switch is plural: -AssessmentSettingsEnable.

A workspace is mandatory. Without attaching one, the toggle alone won’t produce results.

Some Az.SqlVirtualMachine versions don’t expose a workspace parameter; that’s why the CLI route works immediately.

pre-checks (quick)

SQL IaaS extension state: Healthy, mode = Full.

VM has outbound 443 to Azure Monitor/Log Analytics endpoints.

Role: you (or the VM’s managed identity) can write to the chosen workspace.

Keep Az modules current (Get-Module Az.SqlVirtualMachine -List | select Name,Version).

how to verify it’s really enabled

Portal: SQL VM → “SQL best practices assessments” shows Enabled; runs start appearing.

Log Analytics: after a run, query for results (first run may take a bit):

SqlAssessment_CL
| take 10

Learn --> https://free.blessedness.top/en-us/azure/azure-sql/virtual-machines/windows/sql-assessment-for-sql-vm?view=azuresql&tabs=azure-portal

@Swapnesh Panchal Thank you for the reply. I have a few follow up questions:

• What version of the Az.SqlVirtualMachine module has the -AssessmentSettingsWorkspaceResourceId parameter? I can't seem to find it
• The docs for Update-AzSqlVM show the -AssessmentSettingEnable parameter as singular, not plural. What version of the module has that parameter as plural?

I hope I can just find the correct powershell module version to accomplish this with the necessary parameters.

Thank you.

Hi @Paul Sebestyen ,
Thank you for getting back to me. I’ll review and have shared my input below.

1. There isn’t a -AssessmentSettingsWorkspaceResourceId parameter in Az.SqlVirtualMachine (any version). The PowerShell cmdlet doesn’t take a workspace ID. If you need to wire up the Log Analytics workspace from script, use the Azure CLI flags --workspace-name and --workspace-rg (that path also handles the AMA/DCR bits).
2. The switch is singular: -AssessmentSettingEnable (not “Settings”). That’s how it’s documented today and how the module implements it. See the cmdlet docs and the module source (parameters list) for confirmation.
3. “What version do I need?” -AssessmentSettingEnable is present in current Az.SqlVirtualMachine releases (e.g., 2.4.0). There’s still no workspace parameter in those releases—workspace selection remains portal/CLI-only.

Why there’s no workspace flag in the PS cmdlet The underlying ARM resource for the SQL VM exposes assessmentSettings (enable/runImmediately/schedule) but not a workspace property—the workspace association is handled via Azure Monitor (AMA/DCR). That’s why the CLI offers --workspace-* convenience flags and the PS cmdlet does not.

Handy commands to verify locally

# see what version you’re on
Get-InstalledModule Az.SqlVirtualMachine | Select Name, Version

# list the parameters your Update-AzSqlVM exposes
(Get-Command Update-AzSqlVM).Parameters.Keys | Sort

Examples (for completeness)

PowerShell (enable the feature + schedule via PS):

Update-AzSqlVM -ResourceGroupName "<rg>" -Name "<vm>" `
  -AssessmentSettingEnable `
  -ScheduleEnable `
  -ScheduleDayOfWeek Sunday `
  -ScheduleWeeklyInterval 1 `
  -ScheduleStartTime "23:00"

CLI (set the workspace + optionally enable):

az sql vm update -g <rg> -n <vm> \
  --enable-assessment true \
  --workspace-name <laWorkspace> --workspace-rg <laRg> \
  --agent-rg <agentRg>