Minimal Always On Availability Group Design for Zero Data Loss in SQL Server

Problem Statement

You need a high‑availability solution that guarantees no committed transaction is lost when the primary SQL Server fails, while keeping the infrastructure as small as possible.

Requirements

• SQL Server Enterprise Edition (readable secondaries and >2 replicas require Enterprise).
• Windows Server Failover Clustering (WSFC) – at least two nodes.
• Dedicated subnet for AG replication traffic (to avoid interference with client traffic).
• Listener DNS name for client connections.
• Synchronous commit mode for zero data loss.

Minimal Design

The smallest viable AG consists of:

1. Two WSFC nodes (NodeA, NodeB).
2. SQL Server Enterprise Edition installed identically on both nodes.
3. One availability database.
4. Primary replica on NodeA, synchronous secondary replica on NodeB.
5. AG listener pointing to the cluster IP address.

Example PowerShell deployment (run as Domain Administrator on each node)

# 1. Create WSFC
New-Cluster -Name SQLCluster -Node NodeA,NodeB -StaticAddress 10.0.0.100 -NoStorage

# 2. Add cluster quorum witness (file share)
Set-ClusterQuorum -NodeAndFileShareWitness \\fileserver\witness

# 3. Install SQL Server Enterprise Edition (identical version, same patch level)
#    Use ConfigurationFile.ini with /QUIET /QS /ACTION=Install etc.

# 4. Enable Always On feature
Enable-SqlAlwaysOn -ServerInstance NodeA -Force
Enable-SqlAlwaysOn -ServerInstance NodeB -Force

# 5. Create database backup & restore with NORECOVERY on secondary
sqlcmd -S NodeA -Q "BACKUP DATABASE MyDB TO DISK='\\backup\MyDB.bak' WITH FORMAT"
sqlcmd -S NodeB -Q "RESTORE DATABASE MyDB FROM DISK='\\backup\MyDB.bak' WITH NORECOVERY"

# 6. Create endpoint (if not using default)
New-SqlHadrEndpoint MyEndpoint -Port 5022 -ServerInstance NodeA
New-SqlHadrEndpoint MyEndpoint -Port 5022 -ServerInstance NodeB

# 7. Create the AG
New-SqlAvailabilityGroup -Name MyAG -Path "SQLSERVER:\Sql\NodeA\Default" \
    -AvailabilityReplica @(
        New-SqlAvailabilityReplica -Name NodeA -EndpointUrl "TCP://NodeA.domain.com:5022" -AvailabilityMode SynchronousCommit -FailoverMode Automatic -ConnectionStringPrimary 'NodeA',
        New-SqlAvailabilityReplica -Name NodeB -EndpointUrl "TCP://NodeB.domain.com:5022" -AvailabilityMode SynchronousCommit -FailoverMode Automatic -ConnectionStringPrimary 'NodeB'
    ) -Database MyDB

# 8. Join secondary to AG
Join-SqlAvailabilityGroup -Path "SQLSERVER:\Sql\NodeB\Default" -Name MyAG

# 9. Create listener
Add-SqlAvailabilityGroupListener -Path "SQLSERVER:\Sql\NodeA\Default\AvailabilityGroups\MyAG" -Name MyAGListener -StaticIp "10.0.0.150/255.255.255.0"

# 10. Grant client network access (firewall rule)
New-NetFirewallRule -DisplayName "SQL AG Endpoint" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow

Trust and Data Boundaries

• Transaction log flow: primary → secondary over TCP endpoint (port 5022). Encryption is enforced via certificates; mismatched certs break the connection.
• Client connections: go via listener DNS → cluster network name → active replica. No direct access to secondary unless read‑only routing is configured.
• WSFC quorum: protects against split‑brain; only the partition with majority of votes can host the primary role.

Operational Checks

1. Verify cluster quorum: Get-ClusterQuorum should show NodeAndFileShareWitness and QuorumState = Quorum.
2. Check AG health: SELECT * FROM sys.dm_hadr_availability_replica_states; – look for role_desc = 'PRIMARY' on one replica and role_desc = 'SECONDARY' on the other, with synchronization_state_desc = 'SYNCHRONIZED'.
3. Confirm zero redo queue: rollback_queue_size = 0 and log_send_queue_size = 0 on the secondary.
4. Backup health: run BACKUP DATABASE MyDB TO DISK='...' WITH COPY_ONLY on either replica; verify last_backup_date in msdb.dbm_backupset is recent.
5. Listener connectivity: sqlcmd -S MyAGListener -Q "SELECT @@SERVERNAME" should return the current primary.

Failure Modes

• Network partition: if the cluster loses quorum, no replica can assume primary; applications see connection failures until quorum restored.
• Replica crash: with automatic failover, the secondary takes over within seconds; no data loss because synchronous commit ensured log hardening.
• Catastrophic site loss with asynchronous commit: possible data loss equal to unsent log records; not applicable to the minimal synchronous design.
• Certificate mismatch: endpoint encryption fails, AG goes to NOT_SYNCHRONIZED; manual cert sync required.
• Version/edition mismatch: AG will not join; upgrade requires rolling upgrade, temporarily reducing redundancy.

When to Reconsider the Design

• Latency >10 ms between nodes causes noticeable commit delays; consider asynchronous commit or moving replicas closer.
• Need more than two replicas or readable secondaries: requires Enterprise Edition and may need additional network bandwidth.
• Regulatory restrictions forbid WSFC (e.g., certain cloud‑only environments): evaluate Azure SQL Database auto‑failover groups or SQL Managed Instance.
• Application cannot tolerate the brief failover window (<30 s): investigate faster failover mechanisms or application‑level retry logic.