Log Shipping

Question

Log Shipping

Nic-1052152

Ten Centuries

Points: 1157
More actions
June 9, 2010 at 2:33 am

#222485

Hi All
Our main SQL server is situated in an office which is about 4 hours away from the main head quarters of the company and the IT department, which i know is not ideal! This is due to a recent company merge.
We are in the process of transferring and altering the whole of the IT, and the plan is to move the server, but not for the time being as we want to minimize disruption to users by changing things only once.
Anyway, i am looking at setting up log shipping between the offices and i am trialling it on two instances on the same machine to start with. The database is only about 500MB in size, but is vital to the company so i need to lower the risk of downtime by as much as possible.
i seem to have the log shipping set up in the test environment on the same machine, but i was wondering about how older backup schedules would affect the log shipping.
in the past, only a complete database backup was done in Simple mode, as it doesnt take very long for a database this size. however, now i have changed the recovery model to full to allow for log shipping, if older backups are still running i assume it will interfere with the log backups and cause it to be out of sink. am i right in assuming this? should i stop the original backup procedure and just allow log shipping to run in the background?
is there anyway of creating a backup procedure on top of log shipping?
thanks in advance for your replies.
Nic

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

If the recovery model is full , taking a full backup over the log shipping setup will not impact the log shipping.

You can schedule your backup in such a way that the schedule time should not cross the log shipping tlog backup / restore activities.

"More Green More Oxygen !! Plant a tree today"

Nic-1052152 Ten Centuries Points: 1157 More actions · Answer 2

Hi Minaz

thanks for the reply, this is good then.

is this because in simple mode it truncates the transaction log and in full it doesnt unless you ask it to by doing the transaction log backup? sorry for so many questions, i have studied doing this but never actually looked at putting it in to a live environment?

am i right in saying that the log file would grow with the amount of transactions between the log backups? then once the log file is backed up, a lot of free space is created but the log file will not actually reduce in size unless i shrink it?

i am not worried about the log file growing too much, as sql statements which would do this would only ever be run by myself, so i can keep an eye on this manually.

thanks for your help.

Nic

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

Nic-1052152 (6/9/2010)
Hi Minaz
is this because in simple mode it truncates the transaction log and in full it doesnt unless you ask it to by doing the transaction log backup? sorry for so many questions, i have studied doing this but never actually looked at putting it in to a live environment?
am i right in saying that the log file would grow with the amount of transactions between the log backups? then once the log file is backed up, a lot of free space is created but the log file will not actually reduce in size unless i shrink it?

Yes you are right, transaction log will truncate only if you take tlog backup, and truncation will not reduce the size unless you shrink it.:-)

"More Green More Oxygen !! Plant a tree today"

WayneS SSC Guru Points: 95460 More actions · Answer 4

And you shouldn't be going around shrinking the tlog either. It grew for a reason, and unless you eliminate that reason, it will just continue to re-grow. By doing regular tlog backups, you are giving it space to reclaim, and it should just come to a relatively steady size.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

You are right Wayne, we had the same experience in our PROD env. There was a uindex rebuild job which was the culprit. Any way shrinking Tlog periodically will fragement the file too. We need to elliminate the cause instead of shrinking tlog.

"More Green More Oxygen !! Plant a tree today"

abbi1680 SSC Enthusiast Points: 134 More actions · Answer 6

Can you not try mirroring ? Is there any reason to choose log shipping ?

By the way, what is the bandwidth you have between the 2 locations ?