🧹 Clean up unused Azure SQL databases for ⭐ Rewards

[ssw-yakshaver]

Requested by: @chrisschultzssw via YakShaver.ai 🦬
cc: @chrisschultzssw, @Freego1783, @jernejk

Hi Team!

  🟥  Watch the video (1 min 31 sec)

  Url from screen share: https://portal.azure.com/#view/Microsoft_Azure_Sql/SqlAzureExtension/AzureSqlHub/SingleDatabase

Pain

Review Azure SQL Server and databases tagged or named as SSW Consulting / Rewards and identify any unused or legacy databases related to the ⭐ Rewards (Mobile app). Confirm they are no longer required, then safely remove them to reduce unnecessary Azure costs.

Acceptance Criteria

1. All Azure SQL databases related to ⭐ Rewards are reviewed (including dev and prod).
2. Databases confirmed as unused or legacy are clearly identified and documented.
3. Confirmation is obtained that identified databases are not required by the ⭐ Rewards mobile app.
4. Unused databases are deleted from Azure.
5. Ongoing Azure costs are reduced with no impact to production or active development environments.

Screenshot

Figure: Unused rewards databases identified for confirmation and deletion

[jernejk]

cc: @chrisschultzssw

◾ Progress — All Rewards-related SQL databases reviewed; 4 databases + 2 servers identified as unused (~$114/mo potential saving). Nothing deleted yet.

We reviewed every Azure SQL database related to ⭐ Rewards — the six db-sswrewards-* databases from the video plus the two legacy sswconsulting-* servers (tagged Product: SSW Rewards, from the 2019/2020 Rewards-v1 era) — using 30 days of Azure Monitor connection/DTU telemetry and June 2026 billing actuals.

In use — keep (evidence: daily connections and real DTU load):

• db-sswrewards-prod + db-sswrewards-hangfire-prod
• db-sswrewards-staging + db-sswrewards-hangfire-staging

Confirmed unused — safe to delete (zero connections over 30 days):

• db-sswrewards-staging_old — superseded by the new staging DB in Jan 2024; zero connections, zero DTU, zero sessions, no replication links ($7.75/mo)
• sswconsulting-dev DB + server, sswconsulting-dev copy on the prod server, and sswconsulting-prod DB + server — all three databases show zero application connections in 30 days (~$89/mo combined). Provenance confirmed: these are Rewards v1's own infrastructure — this repo was originally named SSW.Consulting and its 2019 ARM templates created these servers, which is why they're tagged Product: SSW Rewards. No other team owns them.

Abandoned — recommend retiring the whole dev environment:

• The dev environment stopped being deployed to in January 2025 (PR 💚 Disabling dev (not used?) and Front Door cache purge #1190 deliberately disabled it, and today's CI only deploys staging → prod). Its database served 4 connections in 30 days and the app idles unused. Retiring the whole SSW.Rewards.Dev resource group saves ~$17/mo on SQL plus the rest of the RG. One check first: whether the multi-tenancy work still intends to use dev for test-tenant validation.

Before deleting the legacy Consulting servers, two loose ends to close:

1. Confirm the sswemployeecheck web app (still Running in SSW.Consulting.Dev) doesn't use these databases — its config needs higher Azure permissions to read than we had.
2. Three TechQuizCompleted* Logic Apps (untouched since Sep 2022) still reference the old SQL connection — they should be disabled/removed as part of the same cleanup.

Recommended safety step before any deletion: take a one-off bacpac export of staging_old and the two dev copies (they have no long-term backups; sswconsulting-prod already retains ~2 months of LTR backups).

Potential saving once all confirmed: $114 AUD/mo ($1,370/yr).

Technical details

• Evidence window: 2026-06-03 → 2026-07-03, Azure Monitor connection_successful / dtu_consumption_percent / sessions_count per database; costs from Cost Management API (June 2026 actuals, AUD).
• Subscription: SSW Primary Account. Servers: sql-sswrewards-{dev,staging,prod}, sswconsulting-{dev,prod}.
• The three legacy S0 DBs' only DTU activity was one synchronized 1% blip on 2026-06-20 across all three — an Azure platform probe, not traffic.
• App Insights corroborates the DB metrics: over 90 days, each environment's app calls only its own database (prod → db-sswrewards-prod, staging → db-sswrewards-staging, dev → db-sswrewards-dev), and db-sswrewards-staging_old appears in zero SQL dependencies.
• Side cost observations: Azure Front Door rewards profile $49.64/mo (biggest single line in the Rewards RGs), law-sswrewards-prod Log Analytics $16.74/mo.

🤖