Database Mirroring in SQL Server – Different modes based on business needs

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.

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

3 Comments on “Database Mirroring in SQL Server – Different modes based on business needs”

  1. 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.

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *