Building an Azure SQL failover group with PowerShell and az CLI
Creating the group, adding databases from elastic pools, watching seeding, listener connection strings, logins, SIDs and Entra authentication on the secondary.
Creating a failover group from the portal is fine for a trial, not for a fleet. I always script the creation: it is repeatable, reviewable and can be replayed identically for every new group. Here is my sequence, with generic names (rg-dba-prd, sql-contoso-prd-a, sql-contoso-prd-b, pool-elp-01, AppDb).
flowchart TD
A["1. Secondary pool created and sized"] --> B["2. Create the group"]
B --> C["3. Add the databases"]
C --> D["4. Watch the seeding"]
D --> E["5. Logins and users on the secondary"]
E --> F["6. Connection strings use the listener"]
1. The secondary pool first
If the databases live in an elastic pool, the secondary is created in a pool with the same name on the partner server. It must exist and have enough free capacity before the add. I create it with the same edition, generation and vCore count as the primary.
New-AzSqlElasticPool -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-b `
-ElasticPoolName pool-elp-01 -Edition GeneralPurpose -ComputeGeneration Gen5 -VCore 4
2. Create the group
New-AzSqlDatabaseFailoverGroup -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-a `
-PartnerResourceGroupName rg-dba-prd -PartnerServerName sql-contoso-prd-b `
-FailoverGroupName fog-contoso-app-01 -FailoverPolicy Manual
az CLI equivalent (the grace period is in hours and only matters with the automatic policy):
az sql failover-group create --name fog-contoso-app-01 --resource-group rg-dba-prd \
--server sql-contoso-prd-a --partner-server sql-contoso-prd-b \
--partner-resource-group rg-dba-prd --failover-policy Manual
A new group contains no database. The name must be unique within database.windows.net.
3. Add the databases
$dbs = Get-AzSqlDatabase -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-a |
Where-Object DatabaseName -in 'AppDb'
Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-a `
-FailoverGroupName fog-contoso-app-01 -Database $dbs
az sql failover-group update --name fog-contoso-app-01 --resource-group rg-dba-prd \
--server sql-contoso-prd-a --add-db AppDb
Two checks before adding: the secondary server must not already hold a database with the same name (unless it is already its secondary, in which case the link is inherited), and the secondary pool must have room.
4. Watch the seeding
Initial seeding copies the whole database: it is the long phase. I look at the group state and, per database, at the DMV on the primary.
Get-AzSqlDatabaseFailoverGroup -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-a `
-FailoverGroupName fog-contoso-app-01 |
Select-Object FailoverGroupName, ReplicationRole, ReplicationState, DatabaseNames
SELECT partner_server, partner_database, replication_state_desc, replication_lag_sec
FROM sys.dm_geo_replication_link_status;
SEEDING then CATCH_UP: until it is done, the secondary cannot be connected to and I do not schedule a drill.
5. Logins and users on the secondary
The group replicates databases, not the server. Three approaches, from simplest to heaviest:
- Contained users (
CREATE USER ... WITH PASSWORD): they travel with the database, nothing to do. - Entra authentication:
CREATE USER [app-identity] FROM EXTERNAL PROVIDERin the database, and an Entra admin set on both servers. This is my default target: no password, no SID to align. - SQL logins: the login must exist in the secondary's
masterwith the same SID as on the primary, otherwise the database user becomes orphaned after a failover.
-- On the primary
SELECT name, sid FROM sys.sql_logins WHERE name = 'app_user';
-- On the secondary (master)
CREATE LOGIN app_user WITH PASSWORD = '<secret>', SID = 0x0123456789ABCDEF0123456789ABCDEF;
Also think about firewall rules: database-level rules are replicated, server-level rules must be recreated on the partner.
6. Connection strings
Write : Server=tcp:fog-contoso-app-01.database.windows.net,1433;Database=AppDb;...
Read : Server=tcp:fog-contoso-app-01.secondary.database.windows.net,1433;Database=AppDb;ApplicationIntent=ReadOnly;...
Lessons learned
- An add script must fail loudly if the secondary pool is missing or full: that is pitfall number one.
- I test an application connection on the listener and on the secondary before calling the group ready.
- Remaining SQL logins are debt: I migrate them to Entra or contained users.