SQL Server – Effects of Turning Transaction Safety OFF

high-availabilitymirroringsql servertransaction

I am considering to turn off the transaction safety to maximize the performance.

Before that i am interested in finding the current status of transaction safety. Is there any command that we can get the status ?

I want to know the real use of turning this off. I found that it could increase performance. please let me know if there are any other advantages

http://msdn.microsoft.com/en-IN/library/ms189061.aspx

and also I would like to know if there are any disadvantages with turning off the transaction safety.

Best Answer

The main disadvantage of high-performance mode is going to be your possibilities of failover. Because the communication between the principal and mirror server is now asynchronous with high-performance mode, the only way to failover is with a forced failover and possible data loss.

If you had to do a forced failover to the mirror server, any transactions that weren't sent to the mirror server would virtually be lost.

The real use of using high-performance mode would be that the following two conditions are true for you:

  1. You want to incur the least amount of database mirroring overhead on the principal
  2. You don't care if there is data loss

You should read up on the following BOL reference on Asynchronous Database Mirroring. It will answer all of your questions more in-depth.

Related Question