Before jumping into the news and code, let’s start from the beginning: Why does someone use Azure SQL Serverless?

The answer is simple: Save money.

Considering my personal scenario, I can think about at least two different scenarios related to saving money.

  • In my personal scenario, I have lots of sample databases. Using the lowest DTU level possible costs US$ 5.00, but the sum of a lot can be too much. Changing them to serverless ensures they will only get online when I really need them.
  • In many enterprise scenarios, the reason is low usage. The database should only get online when in use

The News to Increase Your Savings with SQL Serverless

It was announced in the most recent PASS Summit: The minimal Auto Pause delay was reduced from 1 hour to 15 minutes.

This configuration defines the minimal time the database will be online when a user makes a query. If a user connects for one simple query and goes away, the database would be online for at least 1 hour.

The new feature allows us to reduce this value to 15 minutes.

When you would like to change the value: When you have lots of situations of users making single queries and going away, with considerable idle time between the queries.

When you wouldn’t like to change the value: When the idle time is not too small, but the users are active, and you wouldn’t like to always have the delay of the server getting online again.

How to Change the Value

In my situation, when I heard about the news, I immediately decided to change the default value in all my Azure SQL Serverless instances.

It’s simple, we can use a powershell script to get all the Azure SQL Serverless Databases and change this property.

I like to use the Cloud Shell in the portal. It doesn’t require much control over the login process, it uses your portal login.

Changing all Serverless instances in a Single Script

The script to change the properties of all serverless SQL Databases at once is the following:

# Set the desired minimum pause time in minutes

$desiredPauseTimeInMinutes = 15

Get-AzSqlServer `
| ForEach-Object {
# Retrieve databases for
# each server

Get-AzSqlDatabase
-ServerName $_.ServerName
-ResourceGroupName $_.ResourceGroupName `
| Where-Object { $_.Edition -eq
"GeneralPurpose" -and
$_.SkuName -match "_S_" } `
| ForEach-Object {
Write-Host "Updating minimum
pause time for database:
$($_.DatabaseName) in server:
$($_.ServerName)" -ForegroundColor Green


try {
# Update the minimum
# pause time for the
# database

Set-AzSqlDatabase -ResourceGroupName $_.ResourceGroupName `
-ServerName $_.ServerName `
-DatabaseName $_.DatabaseName `
-AutoPauseDelayInMinutes $desiredPauseTimeInMinutes `
| Out-Null

Write-Host "Successfully updated: $($_.DatabaseName)"
-ForegroundColor Cyan

}
catch {

Write-Host "Error updating $($_.DatabaseName): $_"
-ForegroundColor Red

}

}

}

Some notes about this script:

  • There is the need to loop the servers and the databases because the statement for the databases requires the server name and the resource group as parameters.
  • “_S_” is only part of the SKU name when the serverless is configured.
  • The statement to change the property is using “| Out Null” to avoid a bit output of all the properties
  • This is prepared to run in cloud shell. In other environments you need to control your login first
  • This will make the changes of all serverless SQL in a single subscription. For multiple subscriptions, you need to make a loop on the subscriptions as well.

Summary

This change was long requested by Microsoft. The minimal Auto Pause of 1 hour was too high and cost money for the ones with very low usage.

The post Azure SQL Serverless: Discover What’s new and Increase Your Savings appeared first on Simple Talk.

Source: View source