Alternate Witness in Database mirroring

[R7Dave]

Hello

Can I have more than one witness in database mirroring?

I found the term "Alternate Witness" for Outlook but not SQL.

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.

Thanks
Dave

 

[RRinTetons]

Not as I understand it. I'm setting this same thing up right now, and here's how I think it works.

Start with the Principal Server(P), the Mirror Server(M) and the Witness Server(W). Two out of the three form a quorum.

If all three are up, then the quorum is P and W.
If M goes away then P and W still form a quorum and nothing changes.
If P goes away then M and W form a quorum and the mirror server takes over.
If W goes away then P and M form the quorum and nothing changes.

So. I'm guessing you're concerned about a case where Data Center 1 (D1) has P and W and Data Center 2(D2) has M. D1 gets hit by an asteroid, M in D2 has no one to form a quorum with and nothing works. Is that it? Something like a second witness server(W2) in D2, so that if D1 goes away M and W2 still form a quorum and M takes over?

Seems like it *should* exist, but I haven't seen it talked about in the reading I've been doing. I think in that case you want to puot W in a third location with a connection to D1 and D2 so it can still form a quorum with either one.

Or, I'm totally wrong and someone will come along and correct me!

-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

No, only a single witness is supported. You'll want to put the witness at the backup data center.

Mirroring doesn't require quorum like Clustering does. The witness is the only machine that matters. If it looses connection to the primary it tells the secondary to take over. There's no voting, no deciding the witness just tells it to be the primary.

When the old primary becomes available again you have to then manually switch back after everything is back in sync.

If the witness is in the primary site and that site fails, then there's no witness to tell the secondary to take over, which is why you want the witness in the secondary site. If the secondary site fails that's fine because the primary is still online.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site