LLI Wiki — My Digital Knowledge Base

Distributing databases across Azure SQL elastic pools: analysis, plan and safe moves

Analyse load per database, build a move plan, rebalance online, choose the timing and verify the result.

Over time elastic pools drift out of balance: one pool concentrates the hungry databases and nears saturation while its neighbour idles. Redistributing databases is a classic DBA job, but in production it needs a method: measure, plan, move safely, verify. Here is mine.

Overview

flowchart TD
    A["Per-database metrics (CPU, I/O, storage)"] --> B["History table"]
    B --> C["Move plan computation"]
    C --> D["Human review"]
    D --> E["Online move (one database at a time)"]
    E --> F["Check the real pool"]
    F --> G["Update the inventory"]
    F -->|"mismatch"| H["Stop and investigate"]

1. Analyse the load per database

Every night I collect a few indicators per database from Log Analytics: average and peak CPU, physical reads, log writes, storage growth, PAGEIOLATCH waits. They are stored in a history table, so I can reason over several weeks instead of yesterday only.

AzureMetrics
| where TimeGenerated > ago(14d)
| where ResourceProvider =~ "MICROSOFT.SQL" and MetricName == "cpu_percent"
| extend rid = tolower(_ResourceId)
| where rid has "/databases/"
| extend Srv = extract(@"servers/([^/]+)", 1, rid), Db = extract(@"databases/([^/]+)", 1, rid)
| where Db != "master"
| summarize AvgCpu = avg(Average), P95Cpu = percentile(Maximum, 95) by Srv, Db
| order by P95Cpu desc

Average CPU is misleading: a database spiking to 90 % for one hour each night weighs more than a steady 10 % one. I weigh with the 95th percentile.

2. Build the plan

The principle is bin packing: each pool has a capacity (vCores, max size), each database a "weight" (a score mixing CPU, I/O and storage). The goal is to bring all pools close to the mean, with a tolerance (for example ±10 %) to avoid pointless moves.

Rules I apply:

3. Move safely

Moving between pools is an online operation:

ALTER DATABASE [AppDb]
MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL(name = [pool-elp-02]));

The database stays available during the copy; a short connection drop happens at the final switch, so applications must retry connections. In practice:

Point Practice
Timing Off-peak, never during a heavy batch; I schedule moves in quiet daytime hours or in the evening
Pace One database at a time, or a small batch, never the whole plan at once
Tracking sys.dm_operation_status in master for progress
Geo-replication Both copies must land in a pool with the same name/profile; move both sides
Restart The script is idempotent: rerun, it only does what is still open

4. Verify

Do not trust the return code: read the reality back.

-- in master
SELECT d.name, dso.elastic_pool_name
FROM sys.databases d
JOIN sys.database_service_objectives dso ON dso.database_id = d.database_id
WHERE d.name = 'AppDb';

I then compare with my inventory and fix it only if the real pool is the expected one. A daily check flags any mismatch.

Lessons learned

Mastodon LLIMastodon SocialFosstodon