Availability group

As explained in 100 SQL Server Mistakes and How to Avoid Them

An availability group is a SQL Server high-availability and disaster-recovery configuration that maintains synchronized database replicas.

An availability group is a SQL Server high-availability and disaster-recovery option that uses log streams to synchronize database replicas across servers. It can provide automatic failover for a primary-server outage, or manual failover for disaster recovery. The mistake is treating either capability as proof that every failure scenario is covered. section 9.11 section 13.1

A two-replica availability group in separate racks within one data center removes some local single points of failure, but it is not necessarily disaster recovery. A data-center power loss, flood, or core-network failure can still take down both replicas. Recovery may then require infrastructure elsewhere and restoration from backups. section 13.1

Figure 13.1 HA topology with availability groups providing automatic failover in a single site

Compare a single-site automatic-failover design with cross-site manual failover and a combined HA/DR layout. The placement of replicas determines which failures can be handled automatically and which require an operator. section 13.1

Use availability groups as part of an operational plan, not as a blanket answer:

• Patch secondary replicas first, perform a controlled failover, then patch the former primary. This reduces a maintenance outage from a potential patching window to a brief interruption. section 9.11
• Match failover behavior to the requirement: automatic failover serves HA, while cross-site recovery can require manual failover. section 13.1
• Test DR regularly with users. Synchronization alone does not prove that failover works or that performance after failover is acceptable. section 13.6
• Check write volume and workload characteristics before choosing the design. Capacity planning and DR testing help prevent extended downtime during failover. section 13.4 section 13.6

The overnight warehouse mistake

A large data warehouse uses synchronous commit and automatic failover. Heavy ETL activity builds a commit queue on the secondary because the primary waits for confirmation before committing. Switching to asynchronous commit improves performance but requires manual failover; even then, FULL recovery logging can add I/O and leave the ETL window unmet. A failover cluster may be a better fit. section 13.4

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains failover modes, topology risks, patching workflow, and workload limitations; defers actual HA/DR implementation guidance.

Examples

Uses single-site and cross-site replica topologies, rolling patching, and a high-write data warehouse ETL workload.

What it assumes

Assumes basic DBA awareness while requiring understanding of Windows clustering for availability-group implementations.

What you can do after

Evaluate/understand whether replica placement, failover mode, and workload constraints meet stated availability and recovery requirements.

How it connects

Connects availability groups with high availability, disaster recovery, Windows clustering, failover clustering, backups, and recovery models.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

• loguses · A log is a record of events, activity, or diagnostic information produced during the operation of a system or process, often ordered by time.
• basic availability groupimplemented by · A Basic Availability Group is a limited SQL Server availability-group feature supporting one replica that remains inactive until failover.
• follower nodehas part · A follower node is a non-primary replica that receives replicated data or state from a leader and generally does not initiate leadership.
• primary nodehas part · A primary node is a designated authoritative or coordinating member of a distributed system, typically responsible for accepting operations or coordinating replicated state.
• disaster recoveryimplements · Disaster recovery is the planning and execution of processes and capabilities for restoring systems, data, and services after a disruptive event.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.