SQL Server misunderstanding: 30 days talk about 11th days image will be able to be transferred immediately after the fault is detected

Misunderstanding #11: The image can be transferred immediately after the fault is detected

Error

Database image failover can be initiated automatically or manually.

In the case of automatic initiation, the mirror server performs the failover operation (You are not mistaken, but it is not determined by the witness server for failover ), when both the witness server and the backup server find that they cannot exchange information with the master server (this process is called "arbitration"), the Translator's note: the cluster is monitored through programs, the cluster availability depends on the algorithm from the regulatory program, such as the configuration of each node, file sharing, disk access, and availability of each node to determine whether the cluster is available) in addition, when the mirroring method is synchronous, failover can be performed. (Note: synchronization means that the master server can submit transactions only after the logs of the backup storage are written to the backup storage. Compared with Asynchronization, the performance is worse, but it is safer, and SQL Server is not required to be an Enterprise Edition ).

Manual failover is initiated by you. Manual initiation may be due to the absence of a witness server (so that it cannot "form arbitration "), or, when the subject server is faulty, the image running mode is not "synchronous ".

When the main server fails, the backup storage will not go online until the Redo log queue is complete (the so-called log queue is the log sent from the main server to the backup storage, but not on the backup server ). Even if the operating mode of your image is synchronous, it only means that the log is written to the image disk, but the log cannot be replayed on the image server. For failover, the backup storage must go through the Roll Forward phase before it can be launched. However, the backup storage is only available after the backup storage is launched.

In SQL Server Standard Edition and Enterprise Edition, the CPU is smaller than five kernels, and Roll Forward has only one thread. For the enterprise edition and the CPU has more than 5 cores, a Roll Forward thread is allocated for each 4 cores. Therefore, we can see that the time required for failover depends on the size of the queue for Redo log processing, the number of CPU cores, and the load of the backup storage.

Since we all think that the mirror can be quickly transferred during synchronization, few people detect the log Redo queue. However, because the size of the Redo queue determines the Downtime during failover, it is very important to detect the Redo queue of the backup storage.

For more details, see Estimating the Interruption of Service During Role Switching.