Azure Windows Server 2022 image name

It was surprisingly complicated to find the correct version of the Windows Server 2022 image name or so call offer ID.

The version of Windows Server 2022 for use in Azure has id:

MicrosoftWindowsServer:WindowsServer:2022-datacenter-azure-edition:latest

The version of Windows Server 2022 with SQL 2022 has id:

MicrosoftSQLServer:sql2022-ws2022:standard-gen2:latest

The version of Windows Server 2022 with SQL 2019 has id:

MicrosoftSQLServer:sql2019-ws2022:standard:latest

 

How to find more codes of Offer / Publisher / Sku / Urn / UrnAliasidentification? Answer is via Azure CloudShell by an az-cli command

az vm image list --location <location> --subscription <subscriptionId> -o table --publisher MicrosoftWindowsServer --all > allMs.txt

az vm image list --location <location> --subscription <subscriptionId> -o table --publisher microsoftsqlserver --all > allMsSql.txt

Note: you can't see WS2022 in default view of az vm image list without "-all" so far (1/2022).

And the tricky part is you can also deploy the new WS2022 via Azure Portal where you can find deployment URN in Template.json. Those look similar but aren't the same.

MicrosoftWindowsServer.WindowsServer2022-datacenter-azure-edition:latest

MicrosoftSQLServer.sql2019-ws2022sqldev:latest

Using MarketPlaceOfferId in Az-cli (az vm create command) will cause following error message

ERROR: (ResourceNotFound) The Resource 'Microsoft.Compute/images/microsoftsqlserver.sql2019-ws2022sqldev' under resource group '<resourceGroup>' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix

Figure this out took me an unnecessarily long, so hopefully, you can be faster with my help.

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.