How do you force Azure SQL (Serverless) to pause?

We were running an Azure SQL database (Serverless) as part of a PoC. We are now testing some other solutions, so we'd like to force pause the database for a few days. Ie. we want to only incur storage billing for a few days.

The database is showing a low amount of usage that keeps the db form pausing automatically (resulting in constant billing in line with the minimum vCore associated with my db's storage use)

How can I force serverless db to pause?

Solution

You can't explicitly / programmatically force Azure SQL Serverless to Pause - the database will only pause after no connection or CPU activity is detected for the configured 'Auto Pause Delay' setting (minimum 1 hour). Any connections (e.g. connection pool from an app) or other inadvertent background activity will reset the pause timer (and incurring cost). The only way to (almost) guarantee a pause would be to stop all apps using the database and kill all connections, then wait (at least) an hour.

What you might consider as an alternative is to automate a tier change during periods of inactivity (instead of relying on pause to trigger), especially in a test environment.

e.g. if you don't foresee significant database load over a weekend, you could schedule a job on Friday evening:

  ALTER DATABASE [MY_TEST_DB] 
  MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S0');

S0 will take the database down to an 10 DTU level, around $20 per month at time of writing. If your database size is under 2GB, then you can even drop to 'Basic' level.

Depending on the current size of your database, the lowest tiers of pricing might not be available - chart here.

The database can be returned to Serverless on Monday morning:

 ALTER DATABASE [MY_TEST_DB] 
 MODIFY(EDITION='GeneralPurpose' , SERVICE_OBJECTIVE='GP_S_Gen5_1');

where the above changes the database to serverless vCore Gen 5, max 1 vCore. Before you downscale your serverless SKU, recommend you run:

  SELECT DATABASEPROPERTYEX('MY_TEST_DB', 'ServiceObjective');

and make a note of your original Serverless objective setting so that you can return to that value.

Notes:

When on a cheap DTU based tier, the database will always be available (never pause), but will bill at a lower rate as the capability will be greatly reduced.
You'll need at least db_owner, and perhaps other AD resource permissions to execute ALTER DATABASE .. MODIFY
It can take a few minutes for the database tier to change between tiers
Seemingly the Edition isn't validated, so you won't get an error if you mis-type the Editions. Valid values appear to be Basic, Standard and GeneralPurpose.
You can confirm the tier change on the Azure portal