LLI Wiki — My Digital Knowledge Base

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

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

Mastodon LLIMastodon SocialFosstodon