Azure sql database

Question

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

Answer 1

I do not know

Answer 2

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"  
$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

Answer 3

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

Answer 4

Hi

@Martin Cairney Please reply. Thanks