LLI Wiki — My Digital Knowledge Base

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:

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.

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

Pitfalls

Mastodon LLIMastodon SocialFosstodon