I'm designing a SQL Server architecture with two SQL servers and I'm planning to implement database mirroring between the two. I need to minimize the data loss in case of a server breakdown (whatever the reason). I'm looking at SQL Server 2008 high-safety mode without automatic failover. There is only one operational database which needs to be mirrored and the two servers will be on the same site.
Online documentation states that in that mode when the principal server goes down I can force the mirror to be the new principal, but with the risk of losing data. My question is then, what is the point of mirroring if a data loss can occur (minimizing it?), and what can I do to nullify (not minimize) that loss? Is there a mode in which the principal server does not commit it's data until it's been successfully written to the mirror first?
Also, the SQL server would typically be accessed by client applications, which will be configured to connect to a certain server by name. If there is a database mirroring, what server name should be used to access the mirror, should clients be even aware of an existence of a mirroring server?
EDIT: The following scenarios should be supported:
- Principal is down -> Client receives FAIL, we get notified, manually break the mirroring and use the mirror as the new principal, then establish a new mirror while online.
- Mirror is down -> Client receives FAIL, we get notified, manually break the mirroring and establish a new mirror while online.