SQL Server › Always On Availability Groups
How do you decide between synchronous commit mode and asynchronous commit mode for a specific replica in an Always On Availability Group?
AdvancedAnswer
Synchronous commit mode waits for a secondary replica to confirm it has received and written the transaction before the primary considers the transaction complete, guaranteeing zero data loss but adding some latency, while asynchronous commit mode does not wait, offering better performance but with a small risk of losing recent transactions if the primary fails unexpectedly.
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'ServerB' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'ServerC' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);Real-world example A financial trading platform uses synchronous commit mode for its local disaster recovery replica to guarantee no lost transactions, while using asynchronous commit mode for a distant replica in another region where network latency would otherwise slow down every transaction.