December 14, 2011 at 5:07 am
Hi,
one of the production server TLog backup taken every 30 minutes. the same server going to implement HA for database mirroring.
Mirroring transfer TLog to mirror server from principal server, In this case
what frequency TLog backup to be set in principal server after implemented mirroring.
Pl.let me know once completed database mirroring between principal and mirror server, Is it really required or possibility to take TLOG backup at principal server?
thanks
ananda
December 14, 2011 at 5:17 am
You can take Log Backups on Primary Server.
December 14, 2011 at 5:20 am
Sorry missed the second part of question.
You should keep taking log backup at regular interval for Transaction Logs truncation. 30 minutes interval is fine.
December 14, 2011 at 5:22 am
Dev (12/14/2011)
You can take Log Backups on Primary Server.
ok, If i have taken log backup every 30 min at principal server then what is frequency for Tlog apply at mirror server?
thanks
ananda
December 14, 2011 at 5:29 am
ananda.murugesan (12/14/2011)
Dev (12/14/2011)
You can take Log Backups on Primary Server.ok, If i have taken log backup every 30 min at principal server then what is frequency for Tlog apply at mirror server?
thanks
ananda
What is the operation mode High Safety / High Performance?
December 14, 2011 at 5:31 am
FYI...
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.
December 18, 2011 at 10:34 pm
In mirroring Transaction log backup will not be applied on the secondary server. However the uncommitted/commited transaction will be automatically applied to the secondary database server log. To manage the size of the transaction log in the primary server, we need to have transaction log backup at scheduled interval.
Read the article from techet.
http://technet.microsoft.com/en-us/library/cc917680.aspx
-- Babu
December 19, 2011 at 3:09 am
ananda.murugesan (12/14/2011)
Dev (12/14/2011)
You can take Log Backups on Primary Server.ok, If i have taken log backup every 30 min at principal server then what is frequency for Tlog apply at mirror server?
You're thinking of log shipping.
In database mirroring the log records are copied across by the mirroring functionality either before the transaction commits (synchronous) or shortly after (asynchronous). You have to take log backups to keep the log from growing and to ensure you can restore to an earlier point if necessary. The log backups aren't copied across and applied, that's log shipping.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 3:11 am
Dev (12/14/2011)
FYI...A database mirroring session runs with either synchronous or asynchronous operation....
If you're going to quote someone else's work (in this case Books Online), the correct thing to do is to reference it properly. Otherwise you're implying that it's your own work.
In this case. http://msdn.microsoft.com/en-us/library/ms189852.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 3:45 am
GilaMonster (12/19/2011)
Dev (12/14/2011)
FYI...A database mirroring session runs with either synchronous or asynchronous operation....
If you're going to quote someone else's work (in this case Books Online), the correct thing to do is to reference it properly. Otherwise you're implying that it's your own work.
In this case. http://msdn.microsoft.com/en-us/library/ms189852.aspx
It was not my intension (never thought it). I didn’t want OP to be lost in BOL so I identified what he needed and pasted it here.
p.s. hope I didn’t violet any copyright :hehe:
December 19, 2011 at 4:03 am
Dev (12/19/2011)
GilaMonster (12/19/2011)
Dev (12/14/2011)
FYI...A database mirroring session runs with either synchronous or asynchronous operation....
If you're going to quote someone else's work (in this case Books Online), the correct thing to do is to reference it properly. Otherwise you're implying that it's your own work.
In this case. http://msdn.microsoft.com/en-us/library/ms189852.aspx
It was not my intension (never thought it). I didn’t want OP to be lost in BOL so I identified what he needed and pasted it here.
Fine, but then you need to state where the extract came from, otherwise it appears to be your own work, which it is not.
p.s. hope I didn’t violet any copyright :hehe:
Without a proper reference, it could be called plagiarism.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 4:10 am
GilaMonster (12/19/2011)
Dev (12/19/2011)
GilaMonster (12/19/2011)
Dev (12/14/2011)
FYI...A database mirroring session runs with either synchronous or asynchronous operation....
If you're going to quote someone else's work (in this case Books Online), the correct thing to do is to reference it properly. Otherwise you're implying that it's your own work.
In this case. http://msdn.microsoft.com/en-us/library/ms189852.aspx
It was not my intension (never thought it). I didn’t want OP to be lost in BOL so I identified what he needed and pasted it here.
Fine, but then you need to state where the extract came from, otherwise it appears to be your own work, which it is not.
p.s. hope I didn’t violet any copyright :hehe:
Without a proper reference, it could be called plagiarism.
It’s very much offensive. I usually mention the source in my posts. I missed on one occurrence and you declared me a thief.
December 19, 2011 at 5:05 am
Dev (12/19/2011)
GilaMonster (12/19/2011)
p.s. hope I didn’t violet any copyright :hehe:
Without a proper reference, it could be called plagiarism.
It’s very much offensive. I usually mention the source in my posts. I missed on one occurrence and you declared me a thief.
No. I said that without a proper reference it could be called plagiarism.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2012 at 11:39 am
I have a similar/related question. What happens when the mirror server is down AND t-log is backed up before the mirror comes back?
For example, in the case of high safety mode without automatic fail-over,
At 1:00am, primary and mirror servers are synced.
At 1:05am, mirror server down.
At 1:10am, primary server t-log backed up.
At 1:15am, mirror server backup.
It seems the primary and mirror will be synced again later. It may be that transactions not committed in the mirror can not be flushed out to the file, and still available to send to mirror again.
Is this the case?
Thanks,
-Yuemo
September 12, 2012 at 12:22 pm
yuemo (9/12/2012)
What happens when the mirror server is down AND t-log is backed up before the mirror comes back?
Nothing special. The log is backed up, but can't be truncated because of the mirroring. If the mirror's down for a long time, your log will grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply