Alternate Witness

Question

Alternate Witness

NJDave

SSCommitted

Points: 1903
More actions
November 15, 2011 at 6:48 am

#260036

Hello
Can I have more than one witness in database mirroring?
My client wants to have the database mirrored between 2 data centers and a witness at each data center. That way, if the one datacenter goes down, the mirror at the other data center will take over.
I've never used mirroring - all of the examples show only one witness. I found the term "Alternate Witness" for Outlook but not SQL.
The only mention of having more than one witness was here...
"You can run a witness server in Microsoft SQL Server Express Edition, and SQL Server Express Edition does not require a SQL Server license. Therefore, use multiple witness instances when you use SQL Server Express Edition."
http://support.microsoft.com/kb/930283
Is there better documentation that goes into more detail of using multoplee or alternative witnesses?
Thanks
Dave

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No. One witness.

The point of a witness is to form quorum - where 2 out of the 3 machines involved agree as to what's happened. If there were 2 witnesses, then, should the connection between the sites be broken you'd have an instant split-brain scenario where both servers can see a witness and hence think they are the one that should be running.

That comment about multiple witnesses is describing one instance acting as witness to multiple database mirroring setups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NJDave SSCommitted Points: 1903 More actions · Answer 2

Thanks Gail - is there any way to protect against a situation where the datacenter that hosts the witness loses all power?

Or, is there any way SQL Server can stay up even if a data center goes down? Is mirroring the 2 datacenters the best option?

Any suggestions are appreciated.

Thanks in advance

Dave

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

is there any way SQL Server can stay up even if a data center goes down? Is mirroring the 2 datacenters the best option?

One Witness can monitor Many mirrored DBs.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

NJDave SSCommitted Points: 1903 More actions · Answer 5

Thanks Dev - but what if the witness loses power?

What if the datacenter hosting the active node and the witness loses all power?

Thats what the client is interested in.

Thanks

Dave

Steve Jones - SSC Editor SSC Guru Points: 736211 More actions · Answer 6

If that happens, dc loses, power, I'm not sure that the mirror will pick up. The mirror will think it's lost connectivity with the witness and principal.

What you ought to think about putting the witness in the spare data center. That way if they primary data center goes down, then the mirror picks up, as the witness and mirror will agree they're both up.

The issue is if you lose connectivity between data centers. In that case, you can get into a weird situation. You might end up resetting mirroring if that happens. What is most important, is where the clients go. If the clients can hit the primary, it doesn't matter.

The chances of losing the entire DC is low. I wouldn't think about that so much as I would consider protecting one machine going down. If you lose an entire data center, you can always manually bring the mirror online.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

What if the datacenter hosting the active node and the witness loses all power?

Role of the Witness in Automatic Failover

________________________________________

Throughout a database mirroring session, all the server instances monitor their connection status. If the partners become disconnected from each other, they rely on the witness to make sure that only one of them is currently serving the database. If a synchronized mirror server loses its connection to the principal server but remains connected to the witness, the mirror server contacts the witness to determine whether the witness has lost its connection to the principal server:

•If the principal server is still connected to the witness, automatic failover does not occur. Instead, the principal server continues to server the database while accumulating log records to send the mirror server when the partners reconnect.

•If the witness is also disconnected from the principal server, the mirror server knows that principal database has become unavailable. In this case, the mirror server immediately initiates an automatic failover.

•If the mirror server is disconnected from the witness and also from the principal server, automatic failover is not possible, regardless of the state of the principal server.

The requirement that at least two of the server instances be connected is known as quorum. Quorum makes sure that the database can only be served by one partner at a time.

Database Mirroring Witness

http://msdn.microsoft.com/en-us/library/ms175191.aspx

Quorum: How a Witness Affects Database Availability

http://msdn.microsoft.com/en-us/library/ms189902.aspx