Configure Azure SQL Database Serverless Auto‑Pause and Resume

What the serverless auto‑pause feature does

When a database is in the serverless tier, Azure monitors CPU usage. If no activity is detected for the configured idle period, the compute layer is paused and you pay only for storage and backups. The next login or query triggers a resume, which typically takes a few seconds.

How to enable it with Azure CLI

az sql db create ^
  --resource-group myRG ^
  --server myserver ^
  --name mydb ^
  --service-objective SRSServerless ^
  --auto-pause-delay 300 ^
  --min-vcores 0.5 ^
  --max-vcores 4

Run the command in a Bash or PowerShell session where you have the Azure CLI installed and you are logged in with an account that has Microsoft.Sql/servers/databases/write permission on the target server.

What happens under the hood

• The service checks the auto_pause_delay value (in seconds) after each query finishes.
• If the elapsed idle time exceeds that value, the compute nodes are de‑allocated.
• While paused, the state shown in the portal is Paused and the serviceObjective remains SRSServerless.
• Any new connection attempt causes the service to provision compute again, respecting the minVCore and maxVCore bounds.

Limits to keep in mind

• Auto‑pause delay must be between 60 seconds and 1440 minutes (24 hours). Values outside this range are rejected.
• Compute cannot scale below the minVCore you set or above the maxVCore limit; setting maxVCore too low will cause throttling during spikes.
• When paused, you still incur storage charges (data, log, backups) and any configured long‑term retention.
• Resume latency is typically a few seconds but can vary with service load; design client retry logic accordingly.

Common configuration mistakes

• Too short a pause delay (e.g., 30 seconds) leads to frequent pause/resume cycles, increasing resume latency overhead and potentially higher compute charges.
• Undersizing maxVCore for the expected peak workload results in CPU throttling, query timeouts, and application errors.
• Overlooking storage cost – assuming a paused DB is free; monitor storage usage via Azure Cost Management to avoid surprise bills.
• Missing retry logic – applications that do not handle transient connection failures may see errors when the database is waking from pause.

How to verify the feature is working

1. In the Azure Portal, open the database Overview pane; the status will show Online or Paused.
2. Run a query to check the property:

   SELECT DATABASEPROPERTYEX('mydb','ServiceObjective') AS ServiceObj,
          DATABASEPROPERTYEX('mydb','Edition') AS Edition;
   

3. Inspect recent auto‑pause events with the DMV:

   SELECT * FROM sys.dm_db_resource_stats
   WHERE end_time > DATEADD(hour,-2,GET_SYSDATETIME())
   ORDER BY end_time DESC;
   

4. Use Azure Cost Management to view compute charges; they should drop to near‑zero during periods when the portal shows Paused.

Practical tips

• Set auto-pause-delay to a value that matches your typical idle window (e.g., 300 seconds for five minutes) after measuring usage patterns.
• Configure alerts on cpu_percent or dtu_consumption_percent via Azure Monitor to catch unexpected spikes.
• Implement exponential back‑off retry logic in your data access layer (e.g., Polly for .NET) to handle the brief resume delay.