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.
You can have a witness server with asynchronous mirroring, but even if you do, you still cannot have automatic failover. A witness server is not recommended in asynchronous mirroring because it adds potential for downtime without any added benefit.
Yes Robert, thanks for adding that !!
Hi Amit,
This is Raja from Chennai. i have 2yesrs of exp in Software testing now i planing ot learn MS Sql Server Dba, can you please let me know details .
Regards,
Raja