Database Mirroring has a Quorum
The US Constitution says that a majority of the Senate constitutes a quorum to do business. That’s 51 senators. In the UK, 40 members of parliament (out of 650) need to be present in the House of Commons to validate a vote. In SQL Server Database Mirroring, the quorum is 2 out of 3. For high-safety mode with automatic failover, commonly called “High Availability” mode, quorum makes sure that a database is owned by only one partner at a time. Within this mode, there are three separate SQL Server instances playing the roles of Principal, Mirror and Witness. The Principal owns the updateable “live” copy of the database, the Mirror holds a synchronized copy and the Witness keeps an eye on both to determine when a failover is required. So a “full quorum” means that all three roles are available and all is going swimmingly (Brit) – or peachy (US). The principal database is available to the clients, the database is protected and life is good. All three roles are connected by “endpoints” that communicate via TCP to provide the latest vital signs of each server. Updates on the Principal are committed synchronously with the Mirror in High Safety mode so that no data is lost during a failover. So what happens when one of the roles fails and goes off-line? If just the Principal goes offline, we have what is called a “Witness to Partner” quorum, the Witness detects the failure and automatic failover occurs. The Mirror becomes the Principal and the clients redirect themselves to the new server using the Failover Partner field in their connection string. This all should take a matter of seconds. When the failed server comes back online, the Witness lets it know it is now the Mirror and it synchronizes with the new Principal. We then have a full quorum again. Failover back again or “failback” is not automatic but can be done manually. (Failback within 30 days will guarantee that Microsoft does not come looking for a SQL Server license for the Mirror if that’s its only role.) So what happens if the Mirror fails? Well, in this case we have again a “Witness to Partner” quorum but the Principal is fine so the Witness recognizes that no failover is necessary. The clients continue blissfully unaware of the issue. However, this state is referred to as “running exposed” since another quick failure will bring down the availability of the database. SSMS shows the Principal database with “DISCONNECTED” to indicate that the Mirror is offline. Hopefully, the Mirror comes back online soon and will automatically re-synchronize with the Principal and we have a full quorum again. And the Witness? If this fails, we have a “Partner to Partner” quorum. The Principal and Mirror databases continue as normal in a synchronized state and the clients are fine. However, the ability to perform an automatic failover is lost while the Witness is down. Manual failover can be performed but obviously we want to get the Witness up and running as soon as possible so we have a full quorum again. OK, on to Murphy’s Law: what about a double or triple-failure? Well, that means we have lost quorum. If the Principal and the Mirror are lost then obviously the database becomes unavailable and the clients will complain. If the Mirror comes up before the Principal, interestingly, the Witness will not invoke a failover since offline updates may have occurred and it will wait for the Principal to come back up to re-synchronize and the full quorum is back. It’s kind of what’s happening in the U.K. at the moment except it’s called a coalition. But that would be stretching the political analogy too far. Cheers Brian Detailed scenarios: http://technet.microsoft.com/en-us/library/ms189902.aspx
