DEV Community

Antoine
Antoine

Posted on

Change your autoPauseDelay settings with Azure Automation

Here is the Powershell script i use to change from a SQL Database using Serverless hosting option with auto pause disable, to an enable one.

It goes through all database of a server and change all of them except master, and one specified by name (a demo database in my case).

I host it on an Azure Automation Account on a Powershell runbook.
You will have to add first the following Powershell modules (in the order) by going to Modules > Browse Gallery:

  • Az.Accounts
  • Az.Automation
  • Az.SQL

Hope this helps !

You can find the documentation for the main command set-azsqldatabase if you want to modify the option.

Import-Module Az.Accounts
Import-Module Az.Automation

$connectionName = "AzureRunAsConnection"
$resourceGroupName = "myResourceGroupName"
$sqlServerName = "mySQLServerName"
$databaseNameNotAffected = "prodDatabaseName"
$maxSizeDatabaseInBytes = 10737418240 
$AutoPauseDelayInMinutes = 60
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName       
    "Logging in to Azure..."
    Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}
Get-AzSqlDatabase -ResourceGroupName resourceGroupName -ServerName $sqlServerName  | forEach-Object { if($_.DatabaseName -ne $databaseNameNotAffected -And $_.DatabaseName -ne "master") { Set-AzSqlDatabase -DatabaseName $_.DatabaseName -MaxSizeBytes $maxSizeDatabaseInBytes -Edition GeneralPurpose -ComputeModel Serverless -Vcore 1 -ComputeGeneration "Gen5" -ResourceGroupName $_.ResourceGroupName -ServerName $_.ServerName -AutoPauseDelayInMinutes $AutoPauseDelayInMinutes } }

Top comments (0)