September 28, 2010 at 10:21 am
Afternoon,
I am in the process of designing DB mirroring with High safety and automatic failover for one of our production databases and have been asked by one of out network guys: Why shouldn't we have the witness server located on the same server as the mirror SQL Server Instance?
I have attempted to explain that the witness server should be independent to both the principal & mirror servers in the case of a failure on the mirror server and that you will not be able to fail over if there is a hard failure on the mirror server when it is the then principal server. However I cannot think of a better way to explain this and to give a concrete answer for the 'should'. Why should you have the witness server separate to both the principal & mirror servers?
We will be looking to use SQL Server 2005 standard edition on both the principal & mirror servers and a SQL Express 2008 instance on the witness server (which is actually currently stand alone)
Any info would be greatly appreciated!
Cheers
Dan
September 28, 2010 at 8:47 pm
You can also point out the follwing:
Server A -- SQL Server 2005 Standard Edition Principal Server
Server B -- SQL Server 2005 Standard Edition Mirror Server
-- SQL Server Express Witness Server
If Server A is the currenly active (principal) server and Server B crashes tking down both The mirrored instance and the witness, your principal server will stop responding to requests for data until Server B is up and operational again and either the mirror instance or the witness is running. Wha happens if Server B is down for several hours?
September 29, 2010 at 2:13 am
Lynn Pettis (9/28/2010)
You can also point out the follwing:Server A -- SQL Server 2005 Standard Edition Principal Server
Server B -- SQL Server 2005 Standard Edition Mirror Server
-- SQL Server Express Witness Server
If Server A is the currenly active (principal) server and Server B crashes tking down both The mirrored instance and the witness, your principal server will stop responding to requests for data until Server B is up and operational again and either the mirror instance or the witness is running. Wha happens if Server B is down for several hours?
Thanks for the response Lynn. If Server B is down for several hours does it mean that when Server B is operational again that Server A has to 'REDO' all of the transactions that have happened since? I'm not 100% sure what happens here.
The reason why the network guy is asking if the Mirror server can also host the SQL Witness instance is because currently the only spare machine we have to use as a witness server is a stand alone, and it not part of the same Domain that the Principal & Mirror servers reside on (yes, I know *shudder*)
Ideally I would like to use the Witness server on the stand alone machine and have so far failed (connection handshakes errors and not being able to add logins to the Principal & Mirror partners for the stand alone witness server) Is the resolution to this the use of certificates between the Principal/Mirror Servers --> Witness server?
Thanks in advance,
Dan
September 29, 2010 at 9:11 am
If your mirror instance and witness are both on Server B and the server itself goes down, you will lose quorum and the principal database(s) on Server A will be inaccessable. This means your application(s) will be down, no work will be done until Server B is up and either the mirror instance or the witness come back online.
Your witness needs to run on hardware separate from both the principal and mirror servers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply