Hi Friends,

Database Mirroring in SQL Server, the poor man’s clustering solution, has different operating modes based on business needs. Here is a small explanation:

Synchronous with witness:

In this mode, you will have a witness server with full safety, which means your data is protected on failover as there will be no data loss. And this also provides Automatic Failover. Transaction log records are going to be committed both on mirror and principal before a commit acknowledgement goes to the client, so expect a bit of latency in this mode.

Synchronous without Witness:

In this mode, you will have no witness server, so no automatic failover. You have full safety, which means your data is protected on failover as there will be no data loss. Transaction log records are going to be committed both on mirror and principal before a commit acknowledgement goes to the client, so expect a bit of latency in this mode.

Asynchronous:

In this mode, you will have no witness server, so no automatic failover. The mode is asynchronous so you do not have full safety, which means there could be data loss on failover.  Transaction log records are going to be committed only on principal before a commit acknowledgement goes to the client, and after that the log records are going to be shipped to the mirror partner. So this offers better performance than synchronous mode but there could be chances of data loss.