CODEWITHSUNDEEP
azure-sql-database
MYK
MYK

Reputation: 3007

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?

Upvotes: 6

Views: 3844

Answers (3)

FFFffff
FFFffff

Reputation: 1070

One more option to automate a tier change for azure sql database is using Azure Logic Apps.

You can setup any schedule and execute a SQL command to change tier, e.g. switch to 100 DTUs every morning, and switch back to 10 DTUs every evening.

It's easy to do and compared to auto-pause it gives you total control. Lower tiers are almost free so it serves the purpose just as good.

IMO In the long run it's easier to maintain than troubleshooting random wake-up events.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107317

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

Upvotes: 3

Sarang Kulkarni
Sarang Kulkarni

Reputation: 367

you can take backup of db for later restoration and delete the database , else can switch to basic tier with 5 DTUs

Upvotes: -1

Related Questions