SQL Server database mirroring run mode

operating mode:

At a large level, there are only two modes of SQL Server mirroring: High security mode and high performance mode. The main difference between the two modes is the operation after the transaction is committed. You can view the run mode from Figure 1-1.

In high-performance mode, the principal server does not need to wait for the mirror server to respond to commit the transaction.

In high-safety mode, transactions that require a transaction to be synchronized to the mirror and received in response are not ultimately committed to the principal server

This mode is synchronous and can be subdivided into high-security mode with automatic failover (that is, with a witness) and high-security mode without automatic failover (that is, without a witness server). If the witness server is not configured, the High security (synchronous) with automatic failover option will be dimmed, which is not selectable.

The biggest difference between the two is whether to introduce a witness server, mentioned earlier, the witness can be used as a quorum to detect the status of the principal server, once the witness server can not connect to the principal server, the session will automatically switch to the mirror server, if there is no witness, then you need to manually switch.

In high-security mode, the transaction must be committed on the mirror library before it can be submitted in the principal library, which means that the entire program must wait for the mirror to commit the transaction before it can be submitted, and if the network situation is not ideal, it will affect the whole running process. High-safety mode supports both standard and Enterprise editions, and the principal and mirror servers must be the same version, such as not one Standard Edition and one Enterprise Edition.

If you need the highest level of mirroring security, you can use the witness server as a quorum, but the witness server is not required, but it is required for the automatic failover (failover) feature. The witness server can use Workgroup (Workgroup Edition) or Express Edition.

The witness server is used to check whether the connection between the principal and mirror libraries is normal in the mirrored environment. The witness does not actually execute the failover, simply tells the mirror server: "The principal server is down." Even if the witness server is down, it simply cannot be failover automatically and does not affect the mirroring environment. The witness server can be understood as the only answer: is the principal server down?

Mode	Version requirements	Data loss	Safety options	Performance impact	Recovery speed	Fail over
Synchronous	Standard/Enterprise	0 lost	Full	Network may affect performance	Fast	can automatically
Asynchronous	Enterprise Edition	Data loss is possible	OFF	Less impact	Depending on the amount of transactions that need to be committed	Not

SQL Server database mirroring run mode