Azure sql database

HASSAN BIN NASIR DAR 306 Reputation points
2022-04-19T18:20:58.883+00:00

Hi,

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 ?

Regards

Azure SQL Database
{count} votes

4 answers

Sort by: Most helpful
  1. HASSAN BIN NASIR DAR 306 Reputation points
    2022-04-19T19:44:36.897+00:00

    I do not know

    0 comments No comments

  2. Martin Cairney 2,246 Reputation points
    2022-04-20T02:50:12.407+00:00

    Hi @HASSAN BIN NASIR DAR

    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"  
    $Connection.Open()  
      
    $cmd = new-object system.Data.SqlClient.SqlCommand($Sql, $Connection)  
      
    $DataTable = New-Object System.Data.DataTable  
    $SqlDataReader = $cmd.ExecuteReader()  
    $DataTable.Load($SqlDataReader)  
      
    $Connection.Close()  
    $Connection = $null  
      
    $DataTable.Rows.Count  
      
    foreach ($r in $DataTable.Rows)  
    {  
        $edition = $r.Edition  
        $tier = $r.Tier      
    }  
    

    The second part then applies those parameters to the Cmdlet
    Set-AzSqlDatabase

    • use the parameters
      -Edition
      ` and
      -RequestedServiceObjectiveName

    For example:

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

    You can see this similarly in use at https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/monitor-and-scale-database-powershell

    0 comments No comments

  3. HASSAN BIN NASIR DAR 306 Reputation points
    2022-04-20T16:05:44.687+00:00

    Hi

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

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

    Regards

    0 comments No comments

  4. HASSAN BIN NASIR DAR 306 Reputation points
    2022-04-21T05:58:51.973+00:00

    Hi

    @Martin Cairney Please reply. Thanks

    0 comments No comments