Consultants mostly get stuck at the decision as to which SQL Server Always On replication model to choose from.
SQL Server Always On offers SYNCHRONOUS vs ASYNCHRONOUS mode of replication.
One should try to have synchronous replication because then we will have zero loss recovery, but at the same time, enabling synchronous replication might not be realistically possible in 99% cases based on its cost. In Synchronous replication, data is replicated and only then write is considered completed on the primary node which ensures we have updated 2nd node. In the case of asynchronous replication, we have the 2nd node, always catching up the latest and would be having some backlog in case of loads.
Note that Microsoft does not refer to 10gbps or 50Gig link requirement or Storage Raid levels or IOPs required to have Synchronous replication. Since it is very loaded specific thing so Microsoft refers to performance counter of SQL to check if the hardware and setup is feasible for this replication type or NOT. Plus since DB is ever growing thing, so it needs to be the check on schedule instead of once only.
source: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2017
As per Microsoft TechNet Article,
"In Always On availability groups, the availability mode is a replica property that determines whether a given availability replica can run in synchronous-commit mode. For each availability replica, the availability mode must be configured for either synchronous-commit mode, asynchronous-commit, or configuration only mode. If the primary replica is configured for asynchronous-commit mode, it does not wait for any secondary replica to write incoming transaction log records to disk (to harden the log). If a given secondary replica is configured for asynchronous-commit mode, the primary replica does not wait for that secondary replica to harden the log. If both the primary replica and a given secondary replica are both configured for synchronous-commit mode, the primary replica waits for the secondary replica to confirm that it has hardened the log (unless the secondary replica fails to ping the primary replica within the primary's session-timeout period)."
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into the asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.
Note that synchronous replication is subject to the fact that the partner is not down. Because every transaction is committed to 2nd node also, so it has to continue to function if the 2nd node is permanently down so it waits for the timeout period and shifts to async mode temporarily if the communication is disrupted. (Ping is the criteria!!)
This also means, Synchronous Always On Availability Groups Is Not Zero Data Loss in all cases. These are scenarios that need planning if the use case is such.
Same i've posted on Technet Gallery great feedback: https://social.technet.microsoft.com/wiki/contents/articles/52671.sql-alwayson-choosing-between-the-right-replication-model.aspx
Further Reading: Microsoft Article
SQL editions can give you tough times, especially when you have used enterprise edition and later you find you cannot downgrade it to standard edition. Often times this is core requirement from the customer once they renew their Microsoft licenses.
Only edition flexible enough to be downgraded as well as upgraded is the developer edition which works for six months without any issues.
Once this period elapses you have chance to either upgrade to enterprise edition or downgrade the solution.
It will save you lot of wasted deployment hours.
source: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2017?view=sql-server-2017#-edition-upgrade