Migrate Azure SQL databases between elastic pools and failover groups with KEDA pause
Generic runbook: geo-replication checks, batch exclusion, KEDA pause, migration, location registry update and resume.
This runbook describes a pattern for moving Azure SQL databases between elastic pools or servers that are part of failover groups, while applications that use those databases are quiesced. The steps are: check geo-replication, block batch jobs for the affected tenants, pause KEDA-scaled workloads, run the migration, update the location registry, then resume everything.
Overview
The scenario is a multi-tenant application. Each tenant has its own database, hosted in an elastic pool. A central "admin" database holds a location table that tells applications which server and pool each tenant uses. Tenant databases are protected by failover groups (FOG), so applications connect through the failover group DNS name.
Migration scripts are organised per batch of elastic pools:
| Phase | Script pattern | Purpose |
|---|---|---|
| Pre-migration | 00_PRE_MIG_xx.ps1 |
Preparation before the cutover |
| Migration | 01_MIG_xx.ps1 |
Perform the move/switch |
| Migration retry | 01_MIG_FAILED_xx.ps1 |
Handle databases that failed during the normal phase |
| Post-migration | 02_POST_MIG_xx.ps1 |
Operations after the switch |
A shared parameter file (a PowerShell script) holds the mapping: database names, current and target elastic pools, current and target servers (subscription, resource group, server name), partner and secondary partner servers, the new failover group names, and a ValidateSet("test", "real") parameter to run in test or real mode. Check it before every run.
1. Check geo-replication before the switch
Run a status script on the source server before and after the migration. It can use these Azure SQL views:
sys.geo_replication_linkssys.databasessys.database_service_objectivessys.dm_geo_replication_link_statussys.dm_operation_status
For example, to list ongoing and recent operations:
SELECT *
FROM sys.dm_operation_status
ORDER BY start_time DESC;
Use it to verify the replication state, primary/secondary roles, the partner server, the elastic pool of each database, and that no Azure SQL operation is still running.
In the Azure portal, open the SQL server, then Failover groups, and check the failover group name, primary and secondary servers, the databases that belong to it, the failover policy and its current state. The configuration files show what should exist; the portal shows what actually exists.
2. Exclude the affected tenants from batch processing
Rather than stopping the whole batch infrastructure, insert the affected tenants into an exclusion table read by the batch engine (here dbo.batch_exclu), with a dedicated reason so that these rows can be identified later.
- On the admin database, run a generator query over the location table, filtered on the list of elastic pools in the current batch.
- It produces one
INSERTper tenant, for example:
INSERT INTO dbo.batch_exclu
(
[tenant_id],
[application_id],
[reason],
[last_user],
[last_date]
)
SELECT
'TENANT01',
'MIGRATION_APP',
'DB_MIGRATION',
'migration_user',
GETDATE();
- Review the generated statements, then run them on the batch database. Do it well before the batch window starts on the day of the migration.
3. Pause the KEDA-scaled workloads
Applications that keep processing against the tenant databases (message bridges, document engines, export engines...) must be stopped before the switch. When they are scaled by KEDA, pause the ScaledObject rather than scaling the deployment by hand, otherwise KEDA scales it back up.
In a Kubernetes UI (or with kubectl edit scaledobject <name> -n <namespace>), add these annotations to each ScaledObject:
metadata:
annotations:
autoscaling.keda.sh/paused: "true"
autoscaling.keda.sh/paused-replicas: "0"
| Annotation | Effect |
|---|---|
autoscaling.keda.sh/paused: "true" |
Pauses KEDA autoscaling |
autoscaling.keda.sh/paused-replicas: "0" |
Forces the workload to 0 replicas |
Before starting the cutover, verify that every concerned workload is at 0 replicas.
4. Run the migration
Run the migration script of the batch (01_MIG_xx.ps1). If some databases fail, investigate, then use the retry script (01_MIG_FAILED_xx.ps1).
5. Validate the databases
Run the geo-replication status script again and check the failover groups in the portal:
- the databases exist on the expected infrastructure;
- the primary and the secondary are the expected ones, and replication is healthy;
- the failover group and the elastic pool are the expected ones;
- no Azure SQL operation is running unexpectedly.
6. Update the location registry
Once the databases are moved, update the location table of the admin database so that applications resolve the migrated tenants through the new failover group. The important point: the server column does not point to a physical server, but to the failover group DNS name.
UPDATE dbo.TenantLocation
SET ServerName = 'myfog.database.windows.net,1433',
Elastic_pool_name = 'sqlelp-new-01-28',
env = REPLACE(REPLACE([env], 'GB', 'FG'), 'BC', 'FG')
WHERE ServerName LIKE 'oldserver.database.windows.net%'
AND Elastic_pool_name = 'sqlelp-old-28';
GO
Repeat the statement for each elastic pool of the batch. Before restarting anything, check that no migrated tenant still references the old server name or old pool, and that the new failover group DNS name, pool and environment value are correct.
7. Post-migration, then resume
- Run the post-migration script (
02_POST_MIG_xx.ps1). - Remove the batch exclusions created for this migration (same reason value). First run a
SELECTto confirm that the rows match the tenants and pools of this run, thenDELETEonly those rows. Never delete every row with the migration reason without checking. - Remove the two KEDA pause annotations from each
ScaledObject; KEDA resumes normal autoscaling. - Validate the applications: pods running, no CrashLoop, KEDA scaling active, processing resumed, no connectivity errors to the migrated databases.
- Validate the batch: no remaining exclusion for the tenants, processing resumed, no connection error, no reference to obsolete server or pool names.
Checklist
- [ ] Parameter file checked (databases, pools, target servers, failover groups)
- [ ] Geo-replication status and failover groups checked
- [ ] Batch exclusions generated, reviewed and inserted
- [ ] KEDA workloads paused and at 0 replicas
- [ ] Migration script run (retry script if needed)
- [ ] Geo-replication and failover groups validated
- [ ] Location registry updated and verified
- [ ] Post-migration script run
- [ ] Batch exclusions removed (only this run's rows)
- [ ] KEDA pause removed and applications validated
Notes
- Server, database, pool, tenant, application and script names are generic examples.
- The source did not verify the exact production
DELETEused to remove the exclusions; alwaysSELECTfirst.