3

We use SQL Server 2005 in a high-availability configuration: 1 principle server, 1 mirror server, 1 witness server. This morning we found that several databases were listed as the principle database on both the principal server and the mirror server and the transaction logs had grown out of control.

My understanding is that the mirror database would only flip to principal if the mirror server and witness server both agreed that they couldn't connect to the principal. After this happens, when they can once again connect to the principal, the database there is marked as the mirror. We've had databases flip properly in the past, so this has us a bit confused.

What could cause a principal and a mirror server to both think they had the principal database?

Omlette
  • 46
  • 3

2 Answers2

4

This is sort of a non-answer, but to maybe get on the right path I would be sure to check the following on all the servers:

  • The SQL server logs. From SQL Management Studio: Database Server :: Management :: SQL Servers Logs
  • The Windows Event Logs
Kyle Brandt
  • 83,619
  • 74
  • 305
  • 448
  • 1
    +1 - something broke in your fail-over & re-marking process. The key is finding out what. Since you've had successful flips in the past this is probably a corner case that you never anticipated :) – voretaq7 Mar 24 '11 at 14:24
3

The answer is something like the following series of events:

  1. Principal server is unavailable so mirror and witness form quorum and mirror initiates failover to become principal
  2. Someone removes the definition of the witness on the principal server
  3. Principal server comes back on line and cannot communicate with the mirror, so comes online as the principal. This is only possible when there is no witness defined, as otherwise the principal knows it cannot talk to both mirror and witness and shuts down to avoid the 'split-brain' situation you're seeing.

Is this possible to have happened on your system?

Paul Randal
  • 7,194
  • 1
  • 36
  • 45