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:
- A server deleted during the day is still "alive" in the CMDB until the next night; a job started in between fails with "not found".
- A database created at noon is invisible to afternoon jobs.
- A failover group switch changes the primary role within seconds.
How I live with it:
- 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.
- Store the last collection time next to each row and show it in reports.
- Treat "not found" as a lag signal, not a bug: before opening an incident, check in ARM whether the resource still exists.
- Never decide a deletion from the CMDB alone.
Lessons learned
- Resource Graph queries are fast and cheap; the database-by-database SQL collection is the bottleneck. Parallelise per server, stay sequential inside a pool so you do not saturate it.
- An explicit exclusion table keeps known-bad databases out of the error logs.
- A connection failure on one database must not stop the others: log, continue, summarise at the end.
- Keep a purge: 180 days of archives and 90 days of collection status are plenty.