An availability group protects database availability through replicas; it does not automatically protect every dependency of the application. Build the cluster, database replication and client connection path as separate layers, then prove a committed application transaction survives a planned role change.
This guide starts with an existing validated two-node Windows Server Failover Cluster (WSFC), supported SQL installations and a dedicated synthetic database. It excludes SQL Failover Cluster Instances, shared guest disks, distributed availability groups and production cutovers. No SQL or cluster operation was executed for the article.
1. Choose a supported reference design
Use two domain-joined Windows Server 2022 VMs with SQL Server 2022 Enterprise, or Developer edition strictly for non-production testing. Standard edition's Basic Availability Group limits require a different design. Record exact cumulative updates, guest patch levels, supported VMware configuration and licensing decisions.
Place the replicas on independent intended host failure domains. Review DRS anti-affinity and restart behaviour with the platform administrator, including what happens when a host is in maintenance. Two VMs sharing a host or datastore can still share a failure cause. Do not assume an affinity rule overrides insufficient surviving capacity.
Check database eligibility, full recovery, endpoint authentication, account rights, network reachability and WSFC support against Microsoft's AG prerequisites: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability?view=sql-server-ver16. Record instance-level dependencies such as logins, SQL Agent jobs, credentials and linked servers separately; do not assume joining a database replicates them.
2. Inspect quorum before creating the AG
Review the existing cluster validation report and current node/witness design. The witness contributes to quorum; it is not a third database copy. Use a supported witness in a separate failure domain and test the required network/access path from both nodes.
For a file-share witness, reserve a dedicated share rather than reusing an application-data share. Microsoft explicitly warns against unsupported DFS/replicated-storage arrangements for that witness. Quorum witness deployment: https://learn.microsoft.com/en-us/windows-server/failover-clustering/deploy-quorum-witness
Document expected behaviour for loss of each node, witness unavailability and a network partition. Do not force quorum or adjust votes to make a failed drill appear healthy. A production quorum change requires its own recovery specialist and rollback plan.
3. Prepare the database and endpoints
Have the DBA create a small test database containing synthetic records and an application operation that commits one numbered record at a time. Keep a client-side log of acknowledged commits and UTC timestamps. This is the evidence used later to check data survival.
Enable Always On availability groups on each SQL instance through SQL Server Configuration Manager using the supported procedure, with any required service restart confined to the disposable lab. Confirm the intended instances belong to the validated cluster.
Choose the supported initial seeding method before opening the wizard. For this example, use the wizard's full database/log backup method with a dedicated temporary share and approved service-account access. Confirm free space, database state, endpoint connectivity and the backup path. Do not reuse a production backup folder or grant broad rights to bypass an access error.
Before full initial synchronisation, confirm the test database uses full recovery and has an initial full backup. The wizard's full backup/restore path requires matching database-file paths on the replicas and no database of the same name already present on the secondary. Give the primary Database Engine service account read/write access to the dedicated share and the secondary service account read access. Preserve the wizard's log backups as part of the backup chain. These are prerequisites for the full-synchronisation choice, not permission to overwrite an existing secondary database.
4. Configure one small availability group
In SQL Server Management Studio, start New Availability Group for the prepared database. Name the lab AG, select the eligible database and add the second replica. Configure synchronous commit and manual failover for this first rehearsal. Automatic failover can be a later, separately validated phase.
Review endpoint details, backup preferences and the intended listener. Allocate a unique listener DNS name and the supported address configuration for the lab subnet. Ensure directory/DNS permissions allow its creation. Use a fixed application port approved in the network design, not a port inferred from a screenshot.
Choose full initial synchronisation, provide the dedicated share and inspect the validation results. Generate and retain the wizard's script for DBA review before execution. Investigate every failure and material warning. Microsoft's creation wizard guidance: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/use-the-availability-group-wizard-sql-server-management-studio?view=sql-server-ver16 explains the configuration stages and synchronisation choices.
After the lab creation completes, check that every database is joined and the replicas reach the intended state. A wizard completion message without a ready secondary is incomplete commissioning.
5. Measure replication state without confusing it with readiness
Run this diagnostic query on each replica using an authorised monitoring login:
SELECT DB_NAME(database_id) AS database_name,
is_local, is_primary_replica,
synchronization_state_desc, synchronization_health_desc,
is_suspended, log_send_queue_size, redo_queue_size,
last_commit_time
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1;
For SQL Server 2022, this DMV requires VIEW SERVER PERFORMANCE STATE. The DMV reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-ver17 documents local relevance and potentially delayed state information; its rolling view also covers newer SQL releases. Use only fields supported by the installed build.
Log-send and redo queues describe different work. A secondary can have hardened log that is not yet redone. Queue size alone is not seconds of data loss, and a healthy asynchronous replica is not equivalent to a synchronised synchronous replica. Capture multiple observations during a bounded workload and review the AG dashboard's Failover Readiness as a separate gate.
6. Test through the listener
Connect the application using the listener's supported connection configuration, then run the numbered transaction. Confirm which instance is serving the request and that the expected database is writable. Verify that authentication and application dependencies work on both potential primary instances.
Do not test only a direct connection to one replica and conclude that listener failover works. Multi-subnet deployments need their own address and client-driver settings; they are not simulated by this single-subnet example.
Record client retry behaviour and its transaction semantics. Retrying after a broken connection can encounter an operation that committed but whose acknowledgement was lost. Use an application-level unique transaction identifier to distinguish a legitimate retry from a duplicate write.
7. Rehearse planned manual failover
Only use an authorised disposable AG. Confirm both replicas use synchronous commit, every target database is joined and synchronised, and the dashboard reports readiness. Connect to the intended secondary and use the reviewed Fail Over wizard. Stop if it proposes a forced operation or data-loss path.
Microsoft distinguishes planned manual failover from forced failover and notes that database recovery continues asynchronously after the command is accepted. Planned manual failover: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server
Keep the synthetic client running through the event. Record the first failed operation, successful reconnection and first correct committed transaction after recovery. Compare all acknowledged transaction identifiers with the recovered database. Repeat in the reverse direction only after the former primary has caught up and is ready again.
Acceptance, rollback and limits
A planned drill passes when acknowledged commits survive, the listener and application recover correctly, no duplicate business operation is introduced, and the replicas return to healthy protection. Measure service interruption from the first failed required operation to successful restoration. Separately report operator execution time; neither metric alone establishes recovery for an unplanned disaster including detection and decisions.
Do not roll back by reverting snapshots of active SQL replicas or switching on an isolated stale primary. Returning authority requires a supported, reviewed failover with synchronisation checks. Forced failover, lost quorum and cross-database transaction guarantees need separate expert procedures.
Related topics: SQL performance, backup recovery and ESXi maintenance capacity.
Learning notes
- Quorum decides cluster authority; it does not contain application data.
- Hardened log, completed redo and application readiness are different milestones.
- Listener and retry testing matter as much as the replica dashboard.
Sources and review status
See the source ledger, author self-check and series index. Independent review and lab validation remain pending.