December 12, 2011 at 2:03 pm
Scenario: Using all virtual servers and same version of SS 2008 SE for principal, mirror and witness. Only two physical hosts. Principal on one host, mirror on other host.
Question: Where should the witness reside?
Rationale: Clearly, fail over cannot occur if the principal and witness are on the same physical host and the host stops (no quorum). Moving the witness to the mirror will allow fail over if principal's host fails (quorum intact).
Second question: If both witness and mirror become unavailable (host stops), will principal continue to run, without mirroring?
The question arises because of the monthly ritual of patching all servers with latest Microsoft patches, including host servers. When a patch requires a reboot, all guests stop while the host reboots.
(Yes, of course, given unlimited resources, locate witness on third host.)
December 12, 2011 at 2:13 pm
steve smith-401573 (12/12/2011)
Scenario: Using all virtual servers and same version of SS 2008 SE for principal, mirror and witness. Only two physical hosts. Principal on one host, mirror on other host.Question: Where should the witness reside?
Rationale: Clearly, fail over cannot occur if the principal and witness are on the same physical host and the host stops (no quorum). Moving the witness to the mirror will allow fail over if principal's host fails (quorum intact).
Second question: If both witness and mirror become unavailable (host stops), will principal continue to run, without mirroring?
The question arises because of the monthly ritual of patching all servers with latest Microsoft patches, including host servers. When a patch requires a reboot, all guests stop while the host reboots.
(Yes, of course, given unlimited resources, locate witness on third host.)
Best option, plan for scheduled down time when patching.
Since you are using a witness server I am assuming that you are using automatic failover. If the principal and witness are both unavailable, you will not be able to failover to the mirror server. Also, if the witness and mirror are unavailable, the principal will also stop responding to client requests until either the mirror or witness are available.
December 13, 2011 at 12:21 am
In my humble opinion, the only option here is to have the Witness Server on a third host. No matter what server you have the witness running on, if that host goes down you have unplanned downtime, that is not why you implement a high availability solution is it? This is all about the good old principle of eliminating single point of failures. I guess that there are no failover of virtual machines between your two physical hosts since you don't mention that.
December 13, 2011 at 12:31 am
okbangas (12/13/2011)
In my humble opinion, the only option here is to have the Witness Server on a third host. No matter what server you have the witness running on, if that host goes down you have unplanned downtime, that is not why you implement a high availability solution is it? This is all about the good old principle of eliminating single point of failures. I guess that there are no failover of virtual machines between your two physical hosts since you don't mention that.
+1
December 13, 2011 at 1:14 pm
Lynn,
Are you saying that, unless all three instances are taken down together, any two instances unavailable breaks the mirror and stops processing of client transactions?
I already identified in online resources (BOL or KB) that, after mirroring is interrupted (either principal unavailable and mirroring fails over, or mirror unavailable), mirroring must be 'manually' restarted (i.e., issuing a GUI or T-SQL command) and, depending on how long the mirror has been down, it may be advisable to take a new backup / restore (without recovery) and restart mirroring instead of waiting for the mirror to 'catch up' on transactions.
Thanks (again and again) for the help!
December 13, 2011 at 1:25 pm
okbangas and dev,
The mirroring is established between virtual servers. The question could be analogous to three physical servers, but two of the three servers share a single UPS. If the UPS runs out of battery power during a power outage, but the principal's UPS stays up because its power circuit is unaffected, then both the witness and mirror would stop and the principal, unable to achieve quorum, would take itself offline (i.e., unavailable to process transactions).
December 13, 2011 at 2:02 pm
steve smith-401573 (12/13/2011)
okbangas and dev,The mirroring is established between virtual servers. The question could be analogous to three physical servers, but two of the three servers share a single UPS. If the UPS runs out of battery power during a power outage, but the principal's UPS stays up because its power circuit is unaffected.
Sorry to disappoint you. In a real-world high availability solution, each server has (at least) two power supplies. These are connected to different UPSes, so if one of the UPSes fails, all servers are fully powered by the remaining UPSs. Furthermore, in critical environments like aviation control, each of these electrical circuits will be fed with electricity from separate diesel aggregates which starts automatically. This is EXACTLY what I was writing about, eliminating single points of failure. That is the single most critical task when designing highly available solutions.
Similarily, you will often find network teams, spanned across different physical network adapters (not only a two-port). Each of these to different switches which is interconnected. Each of these switches are powered exactly as the servers.
I rest my case, the witness, principal and mirror shall reside on different servers, to eliminate downtime due to single point of failures.
December 13, 2011 at 3:23 pm
I agree with you. What you describe is the correct way to do things.
I was trying to paint a non-SQL equivalent scenario, as a hypothetical, not real-world, test case. We all agree here, I think, and everything you said is on the money.
Thank you again!
December 13, 2011 at 3:46 pm
By the way, if we stick to your scenario, the location of witness doesn't matter, you're screwed 50 percent of the time anyway. But, you are aware that SQL server express can act as witness? It is so lightweight that you can install it on pretty much any other physical server that you may have.
December 13, 2011 at 4:16 pm
steve smith-401573 (12/13/2011)
Lynn,Are you saying that, unless all three instances are taken down together, any two instances unavailable breaks the mirror and stops processing of client transactions?
I already identified in online resources (BOL or KB) that, after mirroring is interrupted (either principal unavailable and mirroring fails over, or mirror unavailable), mirroring must be 'manually' restarted (i.e., issuing a GUI or T-SQL command) and, depending on how long the mirror has been down, it may be advisable to take a new backup / restore (without recovery) and restart mirroring instead of waiting for the mirror to 'catch up' on transactions.
Thanks (again and again) for the help!
First, I agree with everyone that your witness instance should be on a third, separate server.
In your scenerio where the principal database and witness reside on the same physical server (2 separate virtual servers) from the mirror database is the following:
When the mirror goes down, the principal database and the witness still hold quorum. The principal database will queue the transactions until the mirror becomes available again.
When the principal and witness go down, there will be no failover to the mirror database. Depending on which comes up first, principal or witness, you may lose some data. Not sure, but it is possible that the mirror could take over as principal at this point. The mirror itself is not broken.
If, on the other hand, the witness server and the mirror on the same physical server, when they both go down together, the principal database will stop responding to data requests until either the witness or mirror become available again. And again, the mirror is not broken.
Does this make sense?
December 14, 2011 at 8:15 am
Agreed that virtually any compatible version of SQL Server can work as witness. Not sure about 'older' versions as witness, but as you observed, since you can use Express the question becomes one of what versions the operating system can support. (Would anyone want to use an XP workstation over a phone line with 2005 Express as a witness for a SS 2008 high availability setup? I have visions of Seth Myers on SNL saying 'Really? Really?')
Your description of behavior does make sense. However, the configuration initially described does not make sense because it compromises the high availability objective unnecessarily. When mirroring was first made available, in the vast majority of cases, configurations were set up on separate physical, not virtual, servers. In today's day and age, this discussion is a reminder that one must look at the physical level to confirm the planned setup will work as intended.
Again, thank you all for participating. Those who read and didn't respond, thank you for reading and I hope this thread has been helpful, or at least educational.
December 14, 2011 at 9:00 am
steve smith-401573 (12/14/2011)
@okbangas:Agreed that virtually any compatible version of SQL Server can work as witness. Not sure about 'older' versions as witness, but as you observed, since you can use Express the question becomes one of what versions the operating system can support. (Would anyone want to use an XP workstation over a phone line with 2005 Express as a witness for a SS 2008 high availability setup? I have visions of Seth Myers on SNL saying 'Really? Really?')
Your description of behavior does make sense. However, the configuration initially described does not make sense because it compromises the high availability objective unnecessarily. When mirroring was first made available, in the vast majority of cases, configurations were set up on separate physical, not virtual, servers. In today's day and age, this discussion is a reminder that one must look at the physical level to confirm the planned setup will work as intended.
Again, thank you all for participating. Those who read and didn't respond, thank you for reading and I hope this thread has been helpful, or at least educational.
I understand about configuring database mirroring, I set it up at a previous employer and I made sure that the witness was a third server. I was simply trying to address the situation presented by the OP using three virtual servers deployed on 2 physical servers.
Regarding the versions of SQL being used, yes the witness can be express edition, however if I remember correctly all three (principal, mirror, and witness) must be the same version.
December 14, 2011 at 8:31 pm
Lynn Pettis (12/12/2011)
steve smith-401573 (12/12/2011)
Scenario: Using all virtual servers and same version of SS 2008 SE for principal, mirror and witness. Only two physical hosts. Principal on one host, mirror on other host.Question: Where should the witness reside?
Rationale: Clearly, fail over cannot occur if the principal and witness are on the same physical host and the host stops (no quorum). Moving the witness to the mirror will allow fail over if principal's host thiet ke website fails (quorum intact).
Second question: If both witness and mirror become unavailable (host stops), will principal continue to run, without mirroring?
The question arises because of the monthly ritual of patching all servers with latest Microsoft patches, including host servers. When a patch requires a reboot, all guests stop while the host reboots.
(Yes, of course, given unlimited resources, locate witness on third host.)
Best option, plan for scheduled down time when patching.
Since you are using a witness server I am assuming that you are using automatic failover. If the principal and witness are both unavailable, you will not be able to failover to the mirror server. Also, if the witness and mirror are unavailable, the principal will also stop responding to client requests until either the mirror or witness are available.
I was trying to paint a non-SQL equivalent scenario, as a hypothetical, not real-world, test case. We all agree here, I think, and everything you said is on the money.
December 15, 2011 at 5:20 am
Inspired by this thread, I've tried to wrap up the discussion on my blog, including a similar but interresting scenario: two sites. Any feedback is more than welcome.
The discussion in this thread is about a high availability solution, that is a solution for those who cannot afford that the server will be down for as long as it takes to recover it when it fails. In that case I wonder, how can you in such a scenario not afford to have SQL Server express on a server. If you cannot afford downtime for a few hours of downtime, then you can afford to have at least a cheap 1U server to have your mirroring work if any one server fails.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply