LLI Wiki — My Digital Knowledge Base

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:

-- 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

Mastodon LLIMastodon SocialFosstodon