asked
Can I tell if is safe to promote a mirror database to principal without a witness?
Hi, I'm planing on having 2 machines, one being the principal and the second one as a mirror in case of failure of the 1st one. Is any table in the mirroring DB that can tell me if it was synchronised when the Principal has failed? (I'm asking this, to know if safe to an application to promote the mirror to principal without a 3rd machine working as a witness) **edit:** I plan in implement high-safety mode, but I'm still concerned if: 1-the mirror goes down 2-the Principal does a lot of work (while mirror is down) 3-the mirror come up, and starts synchronising, while synchronizing the principal goes down in this situation, can I detect the mirror is not up to date without a witness?? (I gave this example, but there are more situations like this)
I would answer it depends how you are going to configure it. If you use high-safety mode i.e. synchronous operation in which a transaction is committed on both partners, but at the cost of increased transaction latency. Hence almost 0% percent data loss. But if you configure high-performance mode i.e. synchronous operation, in which as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss. As far as witness is concerned, you need it only when you want an automatic failover. For more information visit
http://msdn.microsoft.com/en-us/library/ms189852.aspx
The role of the witness is only to verify whether a given partner server is up and functioning and hence decide that a fail-over is needed or not. If you need more HIGH AVAILABILITY options try Clustering etc. Or wait for SQL Server 2012 if you can, where four replicas (mirrors) could be created. Since this is not only the case, following is the link where a detailed information with different scenarios, complimented with very useful figures to make you understand what is going to happen
http://technet.microsoft.com/en-us/library/cc917681.aspx For monitoring purpose, either through GUI OR system procedures please visit
http://msdn.microsoft.com/en-us/library/ms365781.aspx
I plan in implement high-safety mode, but I'm still concerned if the mirror goes down...the Principal does a lot of work...then the mirror come up, and starts synchronising, while synchronizing the principal goes down. in this situation, can I detect the mirror is not up to date without a witness??
