May 22, 2012 at 1:02 am
Hi,
I have two sql servers, one Live and other DR. Both servers have mirroring configured between Adventureworks database with safety mode full.
Now suppose my mirror server goes down, what impact will it have on below
1) T log size of Adventureworks db on live server? will the T log increase in size
2) Performance of Adventureworks db on live server as in Safety mode full all transactions should commit at mirror server as well?
3) Will more transactions happen as normal although after the mirror server failed previous transactions are already pending to commit?
4) Will transactions be written to data file?
Can someone please share the answers to above queries?
Regards
Gaurav
Thanks
Gaurav
May 22, 2012 at 8:44 am
please advice sql gurus? the post has been viewed 12 times but yet to receive any reply on this 🙁
Thanks
Gaurav
May 22, 2012 at 8:47 am
post deleted because it was wrong (oops) 🙂
MVDBA
May 22, 2012 at 9:01 am
The following answers assume that the witness and principal server (database) are still working while the mirror server/database is down.
1) T log size of Adventureworks db on live server? will the T log increase in size
The transaction log will not be truncated (VLF marked as unused) by transaction log backups while the mirror database is down. Once the mirror database is active again, transaction will be sent across to the mirror for processing. As these transactions are processed VLF will be truncated by transaction log backups.
2) Performance of Adventureworks db on live server as in Safety mode full all transactions should commit at mirror server as well?
While the mirror is down, transactions will continue to be processed on the principal server.
3) Will more transactions happen as normal although after the mirror server failed previous transactions are already pending to commit?
Not quite sure what you are asking with this question. Can you explain what it is you are asking.
4) Will transactions be written to data file?
Yes, the transactions will be written to the data file.
May 22, 2012 at 9:14 am
my bad there (oops) - i was just trying to find the link that describes the difference between high performance and high safety and accidentally hit post!
see the following link
http://msdn.microsoft.com/en-us/library/ms189852.aspx
MVDBA
May 23, 2012 at 12:55 am
Thanks Lynn and Michael
@Lynn: My confusion is: In Full Safety Mode the transaction commits at the Live only when an acknowledgement is received from Mirror server, but in our case since Mirror went down so the transactions remain in the T log of Live database so that once the mirror is up the transactions are passed to mirror db and then t log is truncated by backups. Is my understanding correct?
Now when a checkpoint happens at the Live db, the committed transactions in the log has to be written onto data file but since Mirror db server is still down what happens in this case?
Thanks
Gaurav
May 23, 2012 at 1:21 am
from the link i posted - hence my original posting
A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.
In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.
All database mirroring sessions support only one principal server and one mirror server. This configuration is shown in the following illustration
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply