SQL Server Questions Answered

Why Isn’t the Mirroring Partner Timeout Working?

Q: I’ve read that I must increase the mirroring partner timeout when combining database mirroring and failover clustering. I tried a manual failover after increasing the timeout, but database mirroring immediately failed over. What am I doing wrong?

A: The mirror server and witness server verify whether the principal server is available by essentially pinging it once per second. The number of pings that have to go unanswered before the principal server is declared unavailable is the mirroring partner timeout.

The mirroring partner timeout therefore controls how long the mirror server waits for a response from the principal server. If a witness server is also configured, and the witness server agrees that it also can’t get a response from the principal server, the mirror server will initiate an automatic failover.

As an aside, there's a misconception that the witness server initiates the failover. However, that’s not true—the witness server exists solely to agree (or not) with the mirror server about the state of the principal server. When the witness server and mirror server agree, the mirror server is said to have "quorum" and can initiate the failover.

 Getting back to the question, the mirroring partner timeout only comes into play if the mirror server doesn't get a response from the principal server at all—in other words, if the Windows server hosting the principal SQL Server instance is offline for some reason.

If the Windows server is still available, but SQL Server is offline, the principal Windows server will respond to the mirror server, saying that the principal SQL Server instance is offline. This lets the mirror server initiate a failover (as long as it gets quorum with the witness server, of course).

The mirroring partner timeout value needs to be increased when combining failover clustering with database mirroring so that a local cluster failover (which means the principal server is unavailable for a time) doesn’t trigger a database mirroring failover. If, after increasing the mirroring partner timeout value in this configuration you perform a manual failover (as in your case), you're only making SQL Server unavailable. The Windows server responds to the mirror server that the principal SQL Server instance is unavailable, and the mirror server performs the failover. The way to avoid this behavior is to temporarily remove the witness server from the mirroring configuration before performing the manual cluster failover.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish