[AlwaysOn] create SQL Server high availability group T-SQL syntax: AVAILABILITY_MODE parameter
AVAILABILITY_MODE = {SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY}
Synchronous or asynchronous commit specifies whether the primary copy must wait for the secondary copy to confirm that the log records will be hardened (written) to disk before the primary copy can commit to a transaction on the primary database. SYNCHRONOUS_COMMIT or ASYNCHRONOUS_COMMIT specifies whether the primary replica has to wait for the secondary replica to acknowledge the hardening (writing) of the log records to disk before the primary replica can commit the transaction on a given primary database. Transactions on different databases on the same master copy can be committed independently. The transactions on different databases on the same primary replica can commit independently. SQL Server 2017 CU 1 introduces the configuration only option. SQL Server 2017 CU 1 introduces CONFIGURATION_ONLY. Configuration copies only apply to availability groups whose cluster type is external or none. CONFIGURATION_ONLY replica only applies to availability groups with CLUSTER_TYPE = EXTERNAL or CLUSTER_TYPE = NONE.
Synchronously submit SYNCHRONOUS_COMMIT
Specify that the master copy waits for the transaction to be committed until they are solidified on this copy (synchronous commit mode). Specifies that the primary replica waits to commit transactions until they have been hardened on this secondary replica (synchronous-commit mode). You can specify synchronous commits for up to three copies, including the master copy. You can specify SYNCHRONOUS_COMMIT for up to three replicas, including the primary replica.
Asynchronously submit ASYNCHRONOUS_COMMIT
Specifies that the primary replica commits the transaction without waiting for the secondary copy to solidify the log (synchronous commit availability mode). Specifies that the primary replica commits transactions without waiting for this secondary replica to harden the log (synchronous-commit availability mode). You can specify asynchronous commit for up to five available replicas, including the master replica. You can specify ASYNCHRONOUS_COMMIT for up to five availability replicas, including the primary replica.
CONFIGURATION_ONLY
Specifies that the master replica synchronizes the availability group configuration metadata to the primary database on this replica. Specifies that the primary replica synchronously commit availability group configuration metadata to the master database on this replica. The copy will not contain user data. The replica will not contain user data. This option: This option:
L can be hosted on any version of SQL Server, including the Express version. Can be hosted on any edition of SQL Server, including Express Edition.
L requires that the data mirroring endpoint of a unique copy is of witness type. Requires the data mirroring endpoint of the CONFIGURATION_ONLY replica to be type WITNESS.
L cannot be changed. Can not be altered.
Invalid when the cluster type is WSFC. Is not valid when CLUSTER_TYPE = WSFC.
"for more information, see configuring copies only." For more information, see Configuration only replica.
The AVAILABILITY_MODE clause is required. The AVAILABILITY_MODE clause is required. For more information, see availability modes (always on availability groups). For more information, see Availability Modes (Always On Availability Groups).
