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:
- a moved database must fit in the target pool: remaining storage, per-database maximum;
- move few databases, the heaviest first, towards the least loaded pools;
- very recent databases or those with too short a history are left alone;
- the plan is saved in a table (source, target, status, error) and reviewed by a human, who can cancel a line.
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
- Two steps beat one: pool first (daytime), application-side or failover-group changes after (evening).
- A plan without human review eventually moves a critical database at the wrong time.
- The gain is measurable: rerun the load report a week later and compare the gaps between pools.
- Keep headroom: rebalancing adds no capacity; if every pool is full, resize or add a pool.
- Log every move (before, after, duration, error): invaluable the day someone asks "why did this database move?".