Database Mirroring in SQL Server 2008:
In database mirroring we can opt to either synchronous or asynchronous DB mirroring.
In asynchronous DB Mirroring the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
In synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes as below;
1. In High-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
2. In high-performance mode transactions are sent to mirror database asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded. In high-performance mode, 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.
A typical DB Mirroring can be shown below with a Witness Server(Only for High Safety Mode):
Role Switching:
In database mirroring the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role.
The following three forms of role switching exist.
1. Automatic failover
This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.
The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server but the witness is still connected to the principal server, the mirror server does not initiate a failover
2. Manual failover
This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
• Forced service (with possible data loss)
Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available. High-performance mode is intended to run without a witness. But if a witness exists, forcing service requires that the witness is connected to the mirror server.
The Step by Step process of DB mirroring will be posted soon.