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

Question

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

Paul Sebestyen 0

I am using Powershell and the Update-AzSqlVM cmdlet to enable the features included with the SQL Server IaaS extension. I have been able to successfully Configure Storage via -SqlManagementType 'Full' parameter, but having trouble with the Enable SQL best practices assessments option. Looking at the help, it would appear that -AssessmentSettingEnable is the parameter to use for this. However, there is a required Log Analytics workspace name in the configuration. I do not see an option for this in the help. Running the cmdlet with the -AssessmentSettingEnable switch parameter does not produce an error, but it does not enable this as expected either. Any help would be greatly appreciated.

2 answers

Your answer

Answer 1

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.

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://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-assessment-for-sql-vm?view=azuresql&tabs=azure-portal

Paul Sebestyen 0 Reputation points

2025-10-22T13:16:42.6633333+00:00

@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.
Swapnesh Panchal 1,380 Reputation points Microsoft External Staff Moderator

2025-10-24T13:42:41.0733333+00:00

Hi @Paul Sebestyen quick check-in. Were you able to attach the Log Analytics workspace via az sql vm update and then enable/schedule the assessment?

if not please share required details in private message.
VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-10-29T06:36:34.5633333+00:00

Hi @Paul Sebestyen

We haven't heard back from you. Please reply if you have any questions in this matter and we will gladly continue the discussion.

Thanks,
Vrishabh
Paul Sebestyen 0 Reputation points

2025-10-30T19:05:49.47+00:00

@Swapnesh Panchal / @VRISHABHANATH PATIL I was looking for a non-CLI PowerShell option. It does not appear like that is an option despite the info given in the first response. Perhaps that functionality will be coming in a future version?
Swapnesh Panchal 1,380 Reputation points Microsoft External Staff Moderator

2025-11-03T21:19:02.8133333+00:00

Hi @Paul Sebestyen,

Right now, the PowerShell cmdlets for managing SQL VMs don’t let you fully set up the SQL best practices assessment without using Azure CLI. The main missing piece is linking the Log Analytics workspace, which is required for collecting assessment data.

This limitation exists because the workspace connection is managed through Azure Monitor settings, which the PowerShell module hasn’t yet included as configurable parameters. So, although you can enable assessment scheduling and run assessments from PowerShell, you can’t specify the workspace directly there at this time.

Because of this, the best option today is to use a mix of Azure CLI (to set up the workspace association) and PowerShell (to manage the scheduling and assessment runs). Microsoft hasn’t announced when, but hopefully, PowerShell will support this fully in a future update.

For now, if you want a full automation without the portal, you have to call Azure CLI within your PowerShell scripts or use REST API calls. This is a temporary workaround until PowerShell covers all the required options.

Microsoft reference Documents:

Az.SqlVirtualMachine module documentation and issues (official source for updates and future capabilities)

Official Microsoft docs about SQL best practices assessment for SQL VMs

Kindly let us know if the above helps or you need further assistance on this issue.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-11-05T03:20:05.7333333+00:00

Hi Paul Sebestyen,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

Answer 2

Paul Sebestyen 0

TL;DR - This cannot be done with just powershell at this time.

Swapnesh Panchal 1,380 Reputation points Microsoft External Staff Moderator

2025-11-20T20:39:46.3733333+00:00

TL;DR: You cannot fully enable SQL best practices assessments using only PowerShell right now — you still need CLI / Portal / REST to hook up the Log Analytics workspace.

Share via

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

2 answers

Your answer