Category: Azure – SQL

###

I was trying to change pricing model of an Azure SQL elastic pool from DTU to vCore using PowerShell . Microsoft has already a ready-made PowerShell command not only to migrate but to set properties of an elastic pool as well (Set-AzSqlElasticPool , Az module should be loaded before using this command). But I am sticking to my intention , I just wanted to use this command only to change the pricing model . I was all set and ready with my script ,

Connect-AzAccount
Set-AzContext -SubscriptionId "<subscription-id>"
$variable1 = Get-AzSqlElasticPool -ResourceGroupName "learning"`
-ServerName "<server-name>"
foreach ($poolname in $variable1.ElasticPoolName)
{
Set-AzSqlElasticPool -ResourceGroupName "learning"`
 -ServerName "<server-name>"`
 -ElasticPoolName "$poolname"`
 -Edition "GeneralPurpose"`
 -VCore "2"`
 -LicenseType "BasePrice"
}

Whoops !!!!

Error :
Set-AzSqlElasticPool : Invalid SKU name 'GP'.
At line:8 char:1
+ Set-AzSqlElasticPool -ResourceGroupName "learning"`
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [Set-AzSqlElasticPool], CloudException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ElasticPool.Cmdlet.SetAzureSqlElasticPool

I have used all parameters which are necessary for the command as per the “docs.microsoft.com” ( as on 5/8/2020) but wasn’t beneficial .I spent a decent amount of time trying to understand what am I missing in here , don’t know from where the SKU “GP” is picking up by the error ( though I have mentioned as GeneralPurpose). So this leads me to think there is something which is wrong with the query or I am missing some
mandatory parameter which is not mentioned in MS docx ( no one else seemed reported ) . This is where I have stopped doing permutations and
combinations before I contacted one of the stake holder of this command as I was in a hurry to deliver a whole automation script to my customer
( this is one piece among a 200 line code ) . It turns out to be that for command to understand the edition we should add a mandatory parameter
“-ComputeGeneration” , like below .

Set-AzContext -SubscriptionId "e839366e-004a-42cf-8bee-85940495ab95"
$variable1 = Get-AzSqlElasticPool -ResourceGroupName "learning"`
-ServerName "ramtestserver"
foreach ($poolname in $variable1.ElasticPoolName)
{
Set-AzSqlElasticPool -ResourceGroupName "learning"`
 -ServerName "ramtestserver"`
 -ElasticPoolName "$poolname"`
 -ComputeGeneration "Gen5"`
 -Edition "GeneralPurpose"`
 -VCore "2"`
 -LicenseType "BasePrice"
}

Yes , this time I got what I was looking for .

*********** below script is just to get the detailed output , you have to run along with above one ************
foreach ($poolname in $variable1.ElasticPoolName)
{
$CurrentDTU = Get-AzSqlElasticPool -ResourceGroupName "learning" -ServerName "ramtestserver" -ElasticPoolName "$poolname"
Write-host "****************"
Write-host "Pool Name :"$CurrentDTU.ElasticPoolName
Write-host "Pool Edition :"$CurrentDTU.Edition
Write-host "Pool Number of Vcores :"$CurrentDTU.Dtu

I thought to pen down my learnings here so that to be useful for others .