0

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.
Improve this question

2 Answers 2

Reset to default
1

SQL server 2008 has two modes of operation: one is high-safety mode in which the mirroring is synchronous: a transaction isn't commited until it has been written to the mirror. In that mode, the only data that you can lose if from the uncommited transaction.

The second mode is high-performance. In that mode, the transaction is marked as comitted on the principal as soon as possible without waiting for the mirror. It will allow the system to continue to work even if the connection between principal and mirror is temporarily broken but, of course, you will lose all transaction that haven't been copied yet if you perform a manual failover.

See this link for details: http://technet.microsoft.com/en-us/library/ms189852.aspx

Improve this answer
1

As to the point of a mirroring setup that allows for data loss, the tradeoff is performance. That is, if you allow for data loss, the client application doesn't have to wait for the transaction to commit at the mirroring partner before it's considered "done". And, for many mirroring topologies, the partner is in a different geographical area than the principal. So the latency can be non-trivial. The transactions queue up at the principal and are delivered when they can be. But that delivery is asynchronous.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .