Azure SQL Dev/Test Excessive Backup Retention
What does ZopNight detect here?
Dev/test-named Azure SQL databases keeping short-term backup retention beyond 7 days are the target, with savings from billable backup gigabytes times the (retention − 7)/retention excess share times a per-GB-hour rate over 730 hours. Because no backup-storage meter is aggregated for Azure SQL yet, the rule abstains today.
Signal and threshold
| Field | Value |
|---|---|
| Rule IDs | RC-1333 |
| Category | rightsizing |
| Severity | low |
| Metric | none — pure configuration read |
| Source | sql_backup_retention.go |
Where it applies
Reading retention days from the ARM policy
- resource.Name: dev/test pattern (dev, test, qa, staging, sandbox)
- Metadata backup_retention_days (live: applySQLLookupSets enrichment.go secondary ARG lookup over backupshorttermretentionpolicies.retentionDays, keyed by DB ARM id): fail-closed, and it replaces the dead backup_retention_excessive Tag no producer wrote. Fires only when days > 7.
- Metadata max_size_gb (ARM max_size, provisioned-storage = free backup allowance) and backup_storage_redundancy (LRS vs GRS rate selection)
- MetricsAware SQLDBBackupStorageUsed + SKURatesAware/TargetSKUCandidates (per-GB-hour backup-storage rate)
Billing excess retention by the GB hour
(mirrors FlexServerBackupRule.excessBackupSavings): freeAllowanceGB = provisionedGB (max_size_gb); billableGB = max(0, backupUsedGB − provisionedGB); excessShare = (retentionDays − 7)/retentionDays; savings = billableGB × excessShare × perGBHourRate × 730, capped at cost. 730× unit note: LookupSKURate returns a per-GB-hour rate (the catalog per-GB-month price is divided by 730 at ingest for storage SKUs), so the monthly figure multiplies by sqlBackupHoursPerMonth (730); omitting it under-reports ~730× and the rec is swallowed by the $5 floor (GCP RC-147 precedent). The rule fails closed: any missing input (backup metric absent, provisioned size unavailable, no billable excess, or no per-GB rate) => saving 0 => abstain (no advisory/$0, no fabricated fraction). Producer gap: Azure SQL Database has no combined backup_storage_used meter (that REST name is a MySQL-flexible-server meter, already used for MySQLBackupStorageUsed); the aggregator must instead sum the daily (P1D) cumulative backup-size meters full_backup_size_bytes + diff_backup_size_bytes + log_backup_size_bytes (vCore tier; use snapshot_backup_size_bytes + log_backup_size_bytes for Hyperscale, which does not emit full/diff) and map the total onto SQLDBBackupStorageUsed, plus ingest sqldatabase-backup-lrs/-grs per-GB SKUs; until then the rule abstains. Category rightsizing (also declared via Categories() so cleanup-stale keeps the quantified rec).
Where retention policies live in the portal
- Go to Azure Portal → SQL Database → Backups → Retention policies
The missing Azure SQL backup storage meter
SQLDBBackupStorageUsed (30d): backup-storage bytes consumed