How can I get SQL VM retail rates using REST API?

Question

I want to get the platform retail rates of my SQL VMs that I have running, but through code using the REST API but can't find any rates for SQL VMs. I don't want to manually use the Azure Calculator and the rates and rate card attributes of resources are terrible to try and match with the ARM attributes unless there is a very specific "armSkuName" that can be used for the filter to look it up. I just need to know what to filter on to find them. Just getting all "Virtual Machines" yields 49,200 rate card entries, and only a 3 have SQL in any fields, but they are specific to southindia. I will need rates for Standard and Enterprise editions and I'm sure those will increase the cost with the increased number of vCPUs from the VM attributes. azure-retail-prices

Answer 1

OK, after some digging I can get the cost of just SQL License based on the edition pulled from the resource provider Microsoft.SqlVirtualMachine/sqlVirtualMachines -> $sqlvm.Properties.sqlImageSku. From the retail REST API lookup: there is a rate for skuName "1-4 vCPU VM" along with the serviceName "Virtual Machines Licenses". My first few mistakes were looking in serviceName = "Virtual Machines" and then also filtering on armRegionName (which is null for the licenses). I'm filtering also on priceType = "Consumption" to filter out DevTest or Reservation prices. The final code also gets the number of vCPUs of the VM object (less than or equal to 4 =1, or #vcpus divided by 4 = x) and multiplies by the hourly rate, then multiplies by 730 hours to get the monthly.

$SqlEdition = "Standard" #or "Enterprise" This gets passed to a function and below is the main code for the function so I can lookup prices and get up to date results.

$serviceName = "Virtual Machines Licenses"
$productName = "SQL Server "+$SqlEdition
$armRateDatas = @()
$r = Invoke-RestMethod -Method GET -Uri "https://prices.azure.com/api/retail/prices?`$filter=serviceName eq '$serviceName' and productName eq '$productName' and priceType eq 'Consumption' and skuName eq '1-4 vCPU VM'" -ContentType "application/json"

$armRateDatas += $r.items
return $armRateDatas