Azure vm set tempdb location

It is possible to configure location for temporary databases internally used by SQL server to specified location during server deployment imperatively via Az cli. I realized that not all newly created VMs have that item available in JSON representation even GUI of Azure Portal is reporting some value.

Read query

az vm show --name <VMname> --resource-group <RG> --query resources[0].settings.ServerConfigurationsManagementSettings.SQLStorageUpdateSettingsV2.SQLTempDbSettings.DefaultFilePath -o tsv

Write command

az sql vm update --name <VMname> --resource-group <RG> --set resources[0].settings.ServerConfigurationsManagementSettings.SQLStorageUpdateSettingsV2.SQLTempDbSettings='{"DefaultFilePath":"D:\\SQLTemp"}' -o none

Some more configuration option

• SQLStorageSettings
• diskConfigurationType:"NEW"
• SQLTempDbSettings
• .defaultFilePath:"<path>"
• .storageWorkloadType:"OLTP"
• SQLLogSettings
• .defaultFilePath
• .luns
• SQLDataSettings
• .defaultFilePath:"F:\\data"
• .luns

I spotted that some VMs created from the image is not having the option available for reading TempDB location under ServerConfigurationsManagementSettings. It was possible to change basic SQLDataSettings and SQLLogSettings via RDP or SMSS tool but even that configuration didn't propagate to Azure. That happened because pre-prerequisites are not met.

 

Conditions to fulfill: There is an installed VM extension SqlIaasExtension and its state is deployed (visible via Portal). The second crucial condition isis that local user NT service\SQLIaasExtension has granted admin rights within the host. Last but not least the target location where is placed the defaultFilePath must be accessible for read/write/modify for a non-admin account or granted these rights for NT Service\MSSQLSERVER or granted local admin to that user as a whole.

 

It is easy to break SqlIaasExtension agent installation to get stuck in "provisioning" and then "failed" state. I have to discover the solution still to finish this tutorial.