A database move plan that respects failover group pairing
Moving a geo-replicated database between pools: secondary first, capacity checks on both sides, geo-link health, pausing consumers, verification and rollback.
A database that sits in a failover group exists twice: a primary copy in a pool on one server, a geo-secondary copy in a pool on another server. Moving it to another pool means touching both copies, in the right order, without breaking the geo-link, and often without telling the applications. Here is the plan I follow, written once as a script so that it is the same every time.
The model
In my estate each pool has a twin with the same name on the partner server, and each pair of pools has its own failover group. A database therefore has a placement made of four facts: primary pool, secondary pool, failover group, and the server that is currently primary. A move can change the pool only, or the pool and the failover group (when the target pool belongs to another group). I split it in two tasks:
| Task | When | What |
|---|---|---|
| Task 1, pool | During the day, low risk | Move the secondary copy, then the primary copy, into the target pool |
| Task 2, failover group | Evening, outside business hours | Remove the database from its group, add it to the group of the target pool |
Splitting them matters: Task 1 is an online scale operation, Task 2 changes the endpoint the applications use, so it needs a quiet window.
Sequence
sequenceDiagram
participant Plan as Move plan table
participant Run as Move script
participant Sec as Secondary server
participant Pri as Primary server
participant Cons as Consumers
Plan->>Run: open move (source pool, target pool)
Run->>Run: capacity checks on both target pools
Run->>Sec: move secondary copy to target pool
Sec-->>Run: pool verified
Run->>Pri: move primary copy to target pool
Pri-->>Run: pool verified
Run->>Plan: stamp task 1
Run->>Cons: pause consumers
Run->>Pri: remove from old group, add to new group
Run->>Run: check geo-link state
Run->>Cons: resume consumers
Run->>Plan: stamp task 2
Why secondary first
The secondary must be able to keep up with the primary. If the primary lands in a pool first, it can generate changes that a secondary still sitting in a smaller or busier pool cannot absorb. The documented guidance for scaling geo-replicated databases is the same: change the secondary first. Moving the secondary first also gives a free test: if the target pool refuses the database, nothing has happened to production yet.
Decision flow
flowchart TD
A["Open move"] --> B{"Both target pools have room?"}
B -->|"no"| X["Skip, record the reason"]
B -->|"yes"| C{"Max databases per pool respected?"}
C -->|"no"| X
C -->|"yes"| D{"Geo-link healthy?"}
D -->|"no"| X
D -->|"yes"| E{"Group changes?"}
E -->|"no"| F["Task 1 only"]
E -->|"yes"| G["Task 1 now, task 2 in the evening"]
Capacity checks on both sides
Before any change I read the real state of both target pools, not my inventory:
- number of databases against the maximum allowed for the pool;
- allocated data space after the move, with a safety margin (I keep the pool at least 15 % below its storage limit);
- the pool must be in the same orientation: a database whose primary is on server A must land in a pool whose group is also primary on A.
A pool that exists on one server only is ignored by the plan.
The move itself
function Move-DbToPool {
param($Rg, $Server, $Db, $Pool)
$current = Get-AzSqlDatabase -ResourceGroupName $Rg -ServerName $Server -DatabaseName $Db
if ($current.ElasticPoolName -eq $Pool) { return 'AlreadyThere' } # idempotent
Set-AzSqlDatabase -ResourceGroupName $Rg -ServerName $Server `
-DatabaseName $Db -ElasticPoolName $Pool | Out-Null
$after = Get-AzSqlDatabase -ResourceGroupName $Rg -ServerName $Server -DatabaseName $Db
if ($after.ElasticPoolName -ne $Pool) { throw "Pool of $Db is $($after.ElasticPoolName)" }
'Moved'
}
# order: secondary first, then primary
Move-DbToPool $rg $secondaryServer $db $targetPool
Move-DbToPool $rg $primaryServer $db $targetPool
For the group change I remove the database from the group and add it to the other one on the primary server. The geo-replication link is not broken by this: the new group inherits the existing link. That is why I never use a helper that deletes a "stray" copy on the partner server before adding: that copy is the live secondary.
Keeping the geo-link healthy
After each task I read the replication link state and stop if it is not healthy. A secondary that is catching up after a move is normal; a link that stays unhealthy is a reason to halt the plan, not to push on.
Pausing consumers
Between removing the database from its group and the moment the applications know the new endpoint, connections through the old listener fail. In my case, background workers that connect to the databases are scaled down or excluded for the databases being moved, and I wait until they are idle before the group change. I wait with a time limit; after the limit I go on but flag the run as failed, so a human looks at it.
Verification and rollback
- Verify by reading back: each task stamps the plan row only once the real pool, group and link state match the target.
- Idempotent reruns: the script reads open rows and does only what is left; a step done by hand is detected and stamped.
- Rollback is a move in the other direction, written as a new plan row. I never edit history: every task writes a history row with the pool and group before and after.
- A stale row is refused, for example if a failover swapped roles since the plan was written.
Pitfalls
- Check the geo role at execution time, not at planning time.
- Do not run the logging step by hand: it adds a row each time.
- Start with one database end to end to validate permissions on every system touched.