September 19, 2008 at 7:13 am
I am considering setting up mirroring in High Protection Operating Mode (Principal and Mirror only; no witness). My question is what happens in the event the principal server crashes/disk failure etc. At this point I can't click "failover" manually on the principal. So is there a published procedure for failing over the mirror without the principal server?
September 19, 2008 at 7:24 am
Full Safety ON (from the mirror with the database being master)
ALTER DATABASE SET PARTNER OFF
RESTORE DATABASE WITH RECOVERY
Full Safety OFF (I think)
ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
September 19, 2008 at 7:46 am
alad (9/19/2008)
I am considering setting up mirroring in High Protection Operating Mode (Principal and Mirror only; no witness). My question is what happens in the event the principal server crashes/disk failure etc. At this point I can't click "failover" manually on the principal. So is there a published procedure for failing over the mirror without the principal server?
No you will not be able to have automatic failover with out a witness. We use SQL express as a witness as it is free and works perfectly.
September 19, 2008 at 8:06 am
Correct. I am running High Protection Operating Mode. Therefore; no witness. I am looking for a way to manually flip the mirror to a production database in the event the the principal crashes. Everything I read indicates the only failover method for this particular setup is clicking failover on the principal and in this scenario the principal is not accessible.
September 19, 2008 at 8:14 am
Why do you not add a witness?
September 19, 2008 at 8:20 am
I understood there is a little overhead with adding a witness.
September 19, 2008 at 8:26 am
There is not a load of overhead from a witness, it really is just pinging the servers to see if they are still alive.
If high availibilty is your priority then it is a small price to pay, especially when your only other option is to manually fail the system over, which may not work as noted and someone will have to be avialiable 24hrs a day to perform this task.
Without a witness you may end up in a situation where you have to break the mirroring and manully restore the database on the mirrored server in order to access it.
September 19, 2008 at 8:55 am
I am a little paranoid of letting the app control when it failsover. We are pretty much a 9 to 5 m-f shop so there is always staff on site to handle the failover. Theoretically if the witness and the principal failed you would be in the situation I am asking about. Not that it's realistic unless the witness and the principal were on a circuit that tripped from a lightening strike and the mirror was still up on another circuit. How would you promote the mirror?
September 19, 2008 at 9:29 am
If your witness and primary failed then i guess you are in trouble , that is why it is recommended to put your witness on a different server in another location if possible.
If you get into the sitution where you need to failover manually and cannot contact the primary, then you will need to break the mirroring relationship between the servers and restore your mirror databse manually on the mirror server.
Still i would use a wtiness, it is far quicker and reliable.
Other options that you can look at could include, Log Shipping , Clustering or replication, but these both have there pros and cons, I perfer mirroring.
Also you will have to have a way for your client apps to failover to the other server, this fairly easy in .Net but can take a bit of work to implement on older applications.
September 19, 2008 at 9:33 am
Thanks. I appreciate the feedback.
September 19, 2008 at 7:11 pm
You can build your own auto-failover mechanism with a job on the mirror server that connects to the primary database (via linked server) and drops mirroring / recovers the database (someone gave the commands earlier in the post). The problem is if the connection between the servers becomes unavailable ... then you would have both servers up and running and no mirroring.
That is where the witness is beneficial ... because a quorum is required to initiate failover there is a smaller chance of an unwarranted failover (although it can still happen if the primary couldn't talk to the mirror and witness servers but was still available to any applications).
If you haven't already done so, you should set up alerts on the mirroring WMI events to have emails sent to anyone responsible for handling the manual failover.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply