Mirroring offers two methods for exchanging data, lets discuss each one in detail.The two are
* Synchronous mirroring
* Asynchronous mirroring
Synchronous mirroring :
In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database’s log buffer in memory, and then flushed to disk (or ‘hardened’) as quickly as possible. In database mirroring, as the principal server writes the principal database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance.The mirror receives the log records in mirror database’s log buffer and then hardens them to disk as quickly as possible.
In Synchronous mirroring, as soon as data is committed in the principal it is sent to the mirror server and it is also committed there.After that the mirror sends an Acknowledgement to principal that data has been committed there.The key concept is that the client i.e principal waits until it receives an Acknowledgement from mirror before it commits the next set of datas.
If the network is slow, it will definitely cause performance issues because the principal is waiting for receiving an Acknowledgement to proceed with subsequent transactions.This is the most secure method for assuring that data is absolutely correct between principal and mirror but it is also the slowest method due to overhead of the principal having to wait for the Acknowledgement to be sent back.
Asynchronous mirroring :
The data that is committed in the principal is sent to the mirror but the principal does not wait for any Acknowledgement from mirror server before continuing with the other transactions.The transactional consistency between the principal and mirror might vary slightly.The mirror database might receive the log records after some time may be some 20 to 30 seconds depending on the transaction size and network speed.
Transaction Safety :
If transaction safety (or just ‘safety’) is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror’s log disk. When safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal).Synchronous transfer guarantees that all transactions in the mirror database’s transaction log will be synchronized with the principal database’s transaction log, and so the transactions are considered safely transferred. You set safety to FULL using,
ALTER DATABASE [dbname] SET SAFETY FULL
A Quorum is the relationship between the Witness,Principal and the Mirror.Depending on the mode of operation it is divided into 3.
Full Quorum —> This is when all 3 Witness,Principal and the Mirror can communicate with each other.Since witness is present automatic failover occurs.
Quorum —> This state exist if the Witness and either partner can communicate with it.
Partner-to-Partner —> When only the Principal and Mirror can communicate with each other.
Operating Modes :
SQL Server provides 3 operating modes for database mirroring and the exact mode is based on the setting of transaction safety and whether a witness server is part of the mirroring session.
High Availability Mode :
It provides the most robust coverage.It consist of the Principal,Witness and Mirror in synchronous communication.In this mode SQL server ensures that each transaction that is committed on the Principal is also committed in the Mirror prior to continuing with next transactional operation in the principal.The cost of this configuration is high as Witness is required.If the network does not have the bandwidth, a bottleneck could form causing performance issue in the Principal. If Principal is lost Mirror can automatically take over.
High Protection Mode :
It is pretty similar to High Availability mode except that Witness is not available, as a result failover is manual.It also has transactional safety FULL i.e synchronous communication between principal and mirror.Even in this mode if the network is poor it might cause performance bottleneck.
High Performance Mode :
It consist of only the Principal and the Mirror in asynchronous communication.Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation.
ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances