LLI Wiki — My Digital Knowledge Base

Building an inventory (CMDB) of an Azure SQL estate with Resource Graph

Resource Graph for servers, pools, databases and failover groups, per-database SQL collection, a central database and how to handle inventory lag.

With a handful of Azure SQL servers you know everything by heart. With dozens of them, elastic pools, failover groups and databases that appear and vanish every week, you need a central inventory: a small database (a CMDB) that answers "which databases exist, on which server, in which pool, who is primary?" without opening the portal. Here is how I build it.

Two complementary sources

No single source is enough:

Source What it knows What it does not know
Azure Resource Graph (ARM) Servers, pools, databases, failover groups, SKU, max size, location What is inside the databases
SQL query inside each database Actual used size, users, roles, objects What does not exist at database level (pool, failover group)

The first gives the infrastructure skeleton, the second the inside. Both feed tables in a central database.

flowchart LR
    A["Azure Resource Graph"] --> B["Scheduled script (nightly)"]
    C["SQL connection to each database"] --> B
    B --> D[("Central CMDB database")]
    D --> E["Views: geo roles, capacity"]
    E --> F["Other operations scripts"]
    E --> G["Daily report"]

The Resource Graph queries

Resource Graph queries every subscription at once, with no loop over resource groups. A few of mine:

// Logical servers
resources
| where type =~ "microsoft.sql/servers"
| project subscriptionId, resourceGroup, serverName = name, location,
          version = tostring(properties.version)

// Databases (excluding master), with their pool if any
resources
| where type =~ "microsoft.sql/servers/databases"
| where name !endswith "/master"
| extend serverName = tostring(split(name, "/")[0]),
         dbName = tostring(split(name, "/")[1]),
         poolId = tostring(properties.elasticPoolId)
| project subscriptionId, resourceGroup, serverName, dbName, location,
          sku = tostring(sku.name), maxBytes = tolong(properties.maxSizeBytes), poolId
// Elastic pools
resources
| where type =~ "microsoft.sql/servers/elasticpools"
| extend serverName = tostring(split(name, "/")[0]),
         poolName = tostring(split(name, "/")[1])
| project subscriptionId, resourceGroup, serverName, poolName,
          sku = tostring(sku.name), maxBytes = tolong(properties.maxSizeBytes)

// Failover groups and current role
resources
| where type =~ "microsoft.sql/servers/failovergroups"
| extend serverName = tostring(split(name, "/")[0]),
         fogName = tostring(split(name, "/")[1])
| project subscriptionId, resourceGroup, serverName, fogName,
          role = tostring(properties.replicationRole)

In PowerShell, Search-AzGraph -Query $q -First 1000 is enough, with -SkipToken to page beyond that. A database name appears as server/database there, hence the split calls.

The SQL collection inside each database

For what ARM cannot see, the script connects to each primary database with a managed identity (Entra token, no password) and runs a short query:

SELECT DB_NAME() AS db_name,
       CAST(SUM(size) * 8.0 / 1024 AS decimal(18,1)) AS allocated_mb,
       CAST(SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)) * 8.0 / 1024 AS decimal(18,1)) AS used_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Same idea for users and roles (sys.database_principals, sys.database_role_members). Geo-secondaries are skipped: they are read-only and carry the same name as on the partner.

Storage: staging, then merge

Each collector writes to a staging table, then a single step runs a MERGE into the final tables. Rows missing from the collection are not deleted: they get an IsDeleted flag and a date. History is kept, and a partial collection does not empty the inventory. A useful guard: if more than half of the servers seem to have disappeared, mark nothing and raise an alert, because it is almost certainly an access problem.

Inventory lag

A CMDB is never real time. With a nightly collection it is up to a day behind. Concretely:

How I live with it:

  1. Split the rhythms. Heavy inventory at night; geo roles (primary/secondary) refreshed every 15 minutes in a dedicated view, because that is what changes fast.
  2. Store the last collection time next to each row and show it in reports.
  3. Treat "not found" as a lag signal, not a bug: before opening an incident, check in ARM whether the resource still exists.
  4. Never decide a deletion from the CMDB alone.

Lessons learned

Mastodon LLIMastodon SocialFosstodon