Decommissioning an Azure SQL database safely: checks, approvals and audit
Check connections, backup and long-term copy, leave the failover group, drop, keep an audit trail, all driven by an approval-based queue.
Dropping a database takes one command and cannot be undone the way people expect. The risk is not the command, it is everything that was not checked before it: a connection still alive, a backup that never ran, a long-term copy that was never kept. I automated decommissioning as a queue-driven workflow where the drop is the last step, and only runs when every check is green and an approval exists.
Principles
- Nothing is dropped on a request alone. A request becomes eligible only when its approval and its waiting period (retention) are over.
- Checks come first and are read-only. If any check fails, nothing has changed and the request goes back to the queue with a reason.
- Every action leaves a trace. Who requested, who approved, what was checked, what was removed, and when.
- Each step is idempotent, so a run interrupted halfway can be launched again.
flowchart TD
A["Request + approval"] --> B["Queue: retention reached?"]
B -->|"no"| Z["Wait"]
B -->|"yes"| C["Pre-checks"]
C --> C1["No active connections"]
C --> C2["Recent backup exists"]
C --> C3["Long-term copy kept"]
C1 --> D{"All green?"}
C2 --> D
C3 --> D
D -->|"no"| E["Stop: status ERROR + reason"]
D -->|"yes"| F["Remove from failover group"]
F --> G["Drop database (and secondary)"]
G --> H["Clean inventory and related objects"]
H --> I["Audit row + report mail"]
The pre-checks
| Check | How | Blocks the drop when |
|---|---|---|
| No connections | sys.dm_exec_sessions in the database, excluding your own session and system sessions; optionally the Azure Monitor connection metrics over the last days |
any real session in the last window |
| Last backup | restore point information of the database (Get-AzSqlDatabase gives EarliestRestoreDate, restore points are listed with Get-AzSqlDatabaseRestorePoint) |
no recent restore point |
| Long-term copy | Get-AzSqlDatabaseLongTermRetentionBackup for the server location, server and database name |
none found while policy says one must exist |
$sessions = Invoke-Sqlcmd -ConnectionString $cs -Query @"
SELECT COUNT(*) AS n FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND session_id <> @@SPID
"@
if ($sessions.n -gt 0) { throw "Database still has $($sessions.n) user session(s)" }
$ltr = Get-AzSqlDatabaseLongTermRetentionBackup -Location $loc `
-ServerName $server -DatabaseName $db
if (-not $ltr) { throw "No long-term backup kept for $db" }
The point of the LTR check: after the drop, long-term backups remain available for restore, which is the safety net. If the policy was never applied, you would find out too late.
Removing from the failover group, then dropping
A database that belongs to a failover group must leave it first (Remove-AzSqlDatabaseFromFailoverGroup). Leaving the group does not delete the secondary: it stays as a standalone database on the other server and has to be dropped too. Then Remove-AzSqlDatabase on each side. Each call is preceded by an existence check, so a second run simply finds nothing to do.
Approvals and the queue
The queue row carries a status and dates: request date, approval, retention end. The processor selects rows where the status is "approved" and the retention date has passed. In production I keep the approval human (a manager or the data owner), the drop itself machine. An option processes one specific request only, ignoring the usual filters, for the rare urgent case; a cap per run limits what one execution can delete.
Audit trail
At the end of each run, one audit row per database: what the checks returned (session count, last restore point, LTR backup identifiers), which objects were removed and the final status. The same information goes into an attached report and a mail. When someone asks six months later "what happened to that database?", the answer is a query, not an archaeology session.
Retour d'expérience
- Resource locks. Protective locks on resource groups block deletion. Decide explicitly who removes them and when; remove right before the delete, not at the start of the run.
- Warnings versus errors. Removing related items (inventory rows, access objects) can fail without making the drop unsafe. I treat those as warnings with a resume option, while a failure in the database listing or the drop itself is a hard stop.
- Exact name matching. Match the database name exactly. A prefix or "like" match will one day drop the neighbour.
- Never test with real data. The same script runs on a non-production environment first; production runs only on request.
- Keep the order. Checks, group, drop, cleanup, audit. Cleaning the inventory before the drop leaves orphans when the drop fails.