SQL Server Myth: Database mirroring can be found immediately after a failure occurs

Myth 10. Database mirroring can be found immediately after a failure occurs

Error

Hype on the market database mirroring technology can detect errors and failover immediately after a failure occurs.

This is not the case, but the speed at which the failure is detected depends on the type of failure.

The fastest way to detect a failure is if the principal instance in the mirror crashes so that the mirror server does not return a value once per second, knowing that the process is no longer listening on the corresponding TCP port on the principal server, in which case the mirror server can detect the failure almost instantaneously.

The second fastest occurrence of a failure detected is the operating system crash of the principal server. The principal server no longer responds to the ping of the mirror server, causing an error to be found after the mirror server ping timed out. This timeout threshold defaults to 10 seconds. But you can also extend this time, at which point the time of failure depends entirely on the time the ping has timed out.

Fault detected the third fastest situation is the main log disk is not available, SQL Server will still initiate IO request, but the 20-second IO wait failed to write to the log after found that the log disk is not available, and finally 40 seconds later declared that the disk log is not available, so that the mirror server online. SQL Server is very patient, for example, in the case of locks, SQL Server waits indefinitely for the lock, unless it encounters a deadlock to intervene.

Also, a corrupted page might not cause a failure at all, and if the query reported 823 or 824 errors, the mirroring technology was completely out of focus (the problem was fixed after SQL Server 2008: SQL Server 2008:automatic page Repair with Database mirroring), if you encounter a 823 error or 824 error in the process of data rollback, it immediately becomes a query state, that is, the log and data are not uniform. This can also cause mirroring to fail.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/