Azure sql database

Chapter7-2723 296 Reputation points


I want to scale or shrink the azure sql database with some values in one config table.

Is there a proper way to do it automatically with powershell ? Can you provide me any script?

So that we can fetch the value from config table for user and after that, we can use those values to scale up or down the azure db ?


Azure SQL Database
{count} votes

4 answers

Sort by: Most helpful
  1. Chapter7-2723 296 Reputation points

    I do not know

    0 comments No comments

  2. Martin Cairney 2,226 Reputation points MVP

    Hi @Chapter7-2723

    If I understand your question, you have a table with different performance tier levels in it and you'd like to use that to apply parameters to scale up/down an Azure SQL DB?

    To start with, ensure that you have both the Edition (Basic/Standard/Premium) as well as the matching service tier (S1, P1 etc) in the table as both are normally used in the PowerShell cmdlet.

    You mention
    fetch the value from config table for user

    • however you need to be aware that the scaling up or down is for the entire database and not for user level experiences. If you want to limit per user then look at Resource Governor.

    Your script then needs 2 parts - first to get the parameters from your table.

    $Sql = 'select Edition, Tier from ConfigTable where <add your condition here to select the required row>'  
    $Database = 'XXX'  
    $Server = 'XXX'  
    $Connection = New-Object System.Data.SqlClient.SqlConnection  
    $Connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"  
    $cmd = new-object system.Data.SqlClient.SqlCommand($Sql, $Connection)  
    $DataTable = New-Object System.Data.DataTable  
    $SqlDataReader = $cmd.ExecuteReader()  
    $Connection = $null  
    foreach ($r in $DataTable.Rows)  
        $edition = $r.Edition  
        $tier = $r.Tier      

    The second part then applies those parameters to the Cmdlet

    • use the parameters
      ` and

    For example:

    Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `  
        -ServerName $servername `  
        -DatabaseName $databasename `  
        -Edition $edition `  
        -RequestedServiceObjectiveName $tier  

    You can see this similarly in use at

    0 comments No comments

  3. Chapter7-2723 296 Reputation points


    Where is the part where the config table should be fetch?

    Where is the part where the size will be scale up or down?


    0 comments No comments

  4. Chapter7-2723 296 Reputation points


    @Martin Cairney Please reply. Thanks

    0 comments No comments