July 8, 2011 at 12:20 am
[font="Times New Roman"]
Hello,
I have configured database mirroring in high availability mode. It means client is notified about transaction commit when principal gets the acknowledgement from mirror.
My confusion is "Is two phase commit comes into picture to ensure that transaction is being committed at both sides?".
Thnx in advance.
Regards,
Anil
MCTS 70-432
[/font]
July 8, 2011 at 3:00 am
Hello,
This might be helpful for you .
In high-availability mode, you need all the three server roles: principal, mirror, and witness. In this mode, the transaction safety level is set to FULL. As a result, the data transfer mechanism between the principal and mirror servers is synchronous. That is, the principal server waits for an acknowledgement from the mirror server that the transaction log record has been recorded on the mirror server. Then, the client application gets confirmation that the transaction is committed. If the principal server becomes unavailable, the witness server and the mirror server will form a quorum and perform automatic failover. In addition to automatic failover, you can manually perform the failover. Because the transaction safety level is set to FULL, you do not lose any committed transactions in the event of a failover.
Thanks
Imran
July 8, 2011 at 3:10 am
[font="Times New Roman"]
Hello Imran,
I agree with you that principal will wait for acknowledgement from mirror. But if mirror is responding lately in that case: principal would not process any more transaction or will continue to process and notify the client about transaction commit once it gets acknowledgement from mirror.
Regards,
Anil
MCTS 70-432
[/font]
July 8, 2011 at 3:15 am
This is the way of operation for High safety mode. That guarantee from the mirror that the transaction is committed is what gives the safety , if this bothers you try high performance mode
July 8, 2011 at 3:21 am
[font="Times New Roman"]
Thnx for sharing the link:-)
[/font]
July 8, 2011 at 3:35 am
Hi Anil,
In this mode the servers work synchronously; that means the principal server waits for a response from the mirror server. The mirror server then responds when it has hardened these records to its log disk. This ensures transactional consistency.
If Principal continues with no response /late response from mirror that means its not fully synchronized and you might loose any transactions (This could be done by setting the transaction safety set to OFF).
If transactional safety is set to OFF, the principal server does not wait for an acknowledgment from the mirror server and no additional latency is generated. This process is also normally known as operating in a high-performance mode.
Cheers
Imran
July 8, 2011 at 3:42 am
[font="Times New Roman"]
Thanks a bunch for your precious time:-)
I want to know whether database mirroring uses two phase commit or not. If not then how a transaction can be committed at the same time on more then one server?
Regards,
Anil K
[/font]
July 8, 2011 at 6:33 am
Hi Anil,
Please check out this link ; as its much detailed then my reply.
http://sqlserverpedia.com/wiki/Setting_Up_Log_Shipping_(SQL_Server_2005)
Thanks
Imran
July 8, 2011 at 6:43 am
[font="Times New Roman"]
Thanks for the link:-D.
By the way that link is related to Log shipping not database mirroring at all.
Regards,
Anil K
[/font]
July 8, 2011 at 6:52 am
Sorry -forget to mention you can only mirror to one server.
Thanks
Imran
July 13, 2011 at 8:29 am
Note: Asynchronous database mirroring (a.k.a. High-Performance Mode) is supported only by SQL Server 2005 Enterprise Edition Service Pack 1 (SP1) and later versions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply