February 6, 2013 at 9:23 am
I am after a clear picture of log shipping and all the references I can find do not explain 1 point.
The source database is approx. 200GB and a full backup is made nightly, with transactional backups every 30 minutes.
My understanding is that the first step in log shipping is a full backup is done, copied to the remote server, then restored, then the transactional backups are automatically copied/restored onto the remote server.
This makes sense during the day, but the question is, how does the nightly full backup affect this, when it is too big to copy every night.
I hope this makes sense.
TIA.
February 6, 2013 at 9:35 am
Doesn't affect the log shipping at all. The only time a full backup needs to be copied is when the log shipping's broken and needs to be reset, even then a diff backup's usually easier.
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
February 7, 2013 at 8:14 am
Thanks for the info, so is it that when a full backup is done that transactions will appear in the destination server, or for example, if tlog backups are done 07:00 to 21:00 and a full backup is performed at 23:00 that any changes that happened after 21:00 will not appear in the destination version until 07:00 or will appear at 23:00?
February 7, 2013 at 8:24 am
How would those transactions appear at the log shipping secondary if the log backups aren't running?
Log shipping means backup log, copy log, apply log, there's no other factor involved magically copying transactions around
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
February 7, 2013 at 8:40 am
That is where I am confused, as I thought that a FULL backup which does not perform a Tlog backup as such, does affect the transactions so there is no tlog backup file to copy and the next tlog backup at 07:00 will only have transactions since the full backup and not since the last tlog backup at 21:00.
I guess within log shipping it is intelligent enough to identify transactions in the full backup to copy without a normal tlog bak file.
February 7, 2013 at 9:50 am
Hi Sotn,
One question I would like to ask is why the log backups are not taken during the period of 21:00 to 7:00 ?
I believe that it would be more beneficial and simpler if you take trans log backup every 30mins throughout the 24 hours period. Taking Full backups every night should not affect this log shipping trans backup operation. Full backup is taken by the LS Setup and restored only ONCE at the beginning of this process. Full Backup is not taken automatically daily by the LS setup.
It is up to you to decide if a separate Full backup job is needed to be scheduled.
Dear Mediators , Please correct me if i'm wrong...
February 7, 2013 at 9:56 am
GilaMonster (2/6/2013)
even then a diff backup's usually easier.
Providing a full backup hasn't occurred since the LS plan broke otherwise the Differential_Base_LSN is incremented and the Diff will no longer be restorable on the secondary.
sotn (2/7/2013)
That is where I am confused, as I thought that a FULL backup which does not perform a Tlog backup as such, does affect the transactions so there is no tlog backup file to copy and the next tlog backup at 07:00 will only have transactions since the full backup and not since the last tlog backup at 21:00.I guess within log shipping it is intelligent enough to identify transactions in the full backup to copy without a normal tlog bak file.
Do you understand the principals of log shipping?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 7, 2013 at 12:00 pm
sotn (2/7/2013)
That is where I am confused, as I thought that a FULL backup which does not perform a Tlog backup as such, does affect the transactions so there is no tlog backup file to copy and the next tlog backup at 07:00 will only have transactions since the full backup and not since the last tlog backup at 21:00.
No, it'll have the transactions since the last log backup at 21:00. Full backups do not and never have truncated the log.
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
February 8, 2013 at 4:29 am
thanks for the all the replies,
if no activity is performed for 12 hours (overnight) then does not seem make sense to perform 24 tlog backups with no real activity. so that is why we do not have tlog backups 24/7 but of course I can easily change the plan to do this.
I had a misunderstanding about how a full db backup affects the transaction log (hence the original question), so I now know that it has 0 effect on tlogs, thanks.
Therefore then, if we do tlog backups every 30 minutes 24/7 and a full backup nightly, and restore a full backup then leaving the destination DB in standby, we can 'report' against it with only 30 minutes latency, or do we need to kill connections to the destination in order to apply the tlog/see the new data.
One more question, if it takes more than 1 day to copy a backup from source to destination this is not a problem as Log Shipping will bring the destination up to date (i.e. apply over 50 tlog backups) providing all tlog backup files are still available on the source.
February 8, 2013 at 4:51 am
sotn (2/8/2013)
thanks for the all the replies,if no activity is performed for 12 hours (overnight) then does not seem make sense to perform 24 tlog backups with no real activity. so that is why we do not have tlog backups 24/7 but of course I can easily change the plan to do this.
If that fits your recovery plan then so be it, can you be absolutely sure that no one has made a critical update that may be lost?
sotn (2/8/2013)
Therefore then, if we do tlog backups every 30 minutes 24/7 and a full backup nightly, and restore a full backup then leaving the destination DB in standby, we can 'report' against it with only 30 minutes latency, or do we need to kill connections to the destination in order to apply the tlog/see the new data.
Depends on how often the copy and\or restore jobs run. For the copied log backups to be restored the users will need to be disconnected. If the restores happen every 15 or 30 minutes then users will get pretty upset 😉
It's not unreasonable for the LS backup job to run every 15 or 30 minutes and have the copy and restore jobs run only twice a day for example, although in reality the copy job frequency should ideally match the backup to avoid copying lots of files in one go. once they're on the secondary the restore job could run twice a day maybe
sotn (2/8/2013)
One more question, if it takes more than 1 day to copy a backup from source to destination this is not a problem as Log Shipping will bring the destination up to date (i.e. apply over 50 tlog backups) providing all tlog backup files are still available on the source.
In an ideal world the full backup is used once to initialise the LS plan, after that the restored t-log backups keep the primary and secondary in sync.
That is until someone or something comes along to wreck your LS scenario. Even then, depending on the timeframe and backup regime in place, it is usually possible to fix a broken LS scenario without having to completely re initialise.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2013 at 10:03 am
Sotn,
It would be nice to inform the users of the Secondary Log shipping Database to expect a latency of around 1 hours (even though you have setup 30 mins of backup and restore interval) .
I had same case like yours and while testing how well the data are sync between the two databases, I found that there was a latency of 1 hour.
This might be because the Backup, Copy and Restore were all scheduled to run at same time (interval of 30mins). When the Restore needs the trn file to restore it to the Secondary database , It won't find it in the Restore Folder because it is in the process of being Backed-up and Copied across the network. So The Restore has to wait for another 30 mins to Restore that trn file.
Hence there is a latency of around 1 hour.
I may be able to resolve this by delaying the restore by few minutes..
thanks guys..
February 8, 2013 at 10:10 am
Hi,
Yes makes perfect sense, thanks.
February 8, 2013 at 3:04 pm
February 11, 2013 at 5:13 am
I think my confusion came from this scenario.
Tlog backup at 9:30 and 10:30 and a full backup at 10:00
The full backup at 10:00 contains transactions since the last full backup
I thought that the 10:30 tlog backup just contained transactions since 10:00 (the full backup) but as I have now been told it will included transactions going back prior to the full backup, back to the most recent tlog backup at 09:30, so technically, transactions made between 09:30 and 10:00 will be in the 10:00 full backup and the 10:30 tlog backup.
February 11, 2013 at 6:09 am
sotn (2/11/2013)
The full backup at 10:00 contains transactions since the last full backup
The full backup contains the complete state of the database at the time of backup, it's not a list of transactions since the last full backup
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply