sql-serversynchronousalwayson

SQL Always On Secondary replica limitations


Can you please clarify how many secondary replica nodes can be in synchronous commit mode in 2012/2014/2016. I know that we can have 4 and 8 secondary replica in 2014 & 2016 respectively. Please let me know the limitation on synchronous committ mode.


Solution

  • The version of the SQL Server is important but you need to pay attention to the edition, too. There are some major differences between SQL Server editions and I will advice always to refer the official docs for answering such questions by yourself.

    In case of Enterprise Edition:

    So, there are different Availability Modes:

    where

    Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.

    In the Asynchronous the primary replica does not wait for any of the secondary replicas to harden the log, while in Synchronous, the primary sens confirmation message to the client requesting changes after it receive confirmation from the secondary.

    And in Standard edition you have Basic Availability Groups which comes with a lot of limitations and 2 synchronous replicas only.