move transactions log to another physical server

Question

move transactions log to another physical server

Wilson Lo

SSCommitted

Points: 1993
More actions
July 6, 2006 at 1:34 am

#60659

Hi Folks,
I have a few questions re the location of transaction log.
Currently, my transaction log file and data file are on the same server but different hard drives (for example, the data file is in F drive and log file is in the E drive)
   The concern is when I lose the entire server, I lose both data file and transaction log. The most current data that I can restore is from last night backup.
    Is it possible to move the location of the log file, for example, E drive of a 2nd server? If so, how I can do it?
    Thanks for your help.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The answer to your question is I don't know whether it's actually possible. But even if it were, I wouldn't want to do it. You'd have performance issues with transmitting the data across your network to the transaction log, and, worse still, you'd lose availability if the network went down or got too busy.

If you're concerned about losing everything with the server, then I recommend that you set up log shipping or replication to your second server so that you have a standby with all the data on it. Or just backup up the log on a regular basis and have the backups copied straight to your second server.

John

Wilson Lo SSCommitted Points: 1993 More actions · Answer 2

John,

Thanks for your suggestions on this issue.

Currently, my data file is 2Gb, index file is 600 Mb and transactions log file is 1.7 Gb (the index is re-built daily). The SQL server is 2000 stanadard edition.

The full database backup runs daily and takes 7 minutes. The transactions log backup runs daily and takes 4 mintutes.

If I configure the "log shipping" or "backup the transactions log in the 2nd server" in regular basis (every 20 minutes), do you think the performance will be affected?

Does it mean the only option is "replication"? Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You'd have to test the impact of taking transaction log backups every 20 minutes. I would be surprised if it had a noticeable effect on performance, though. Don't forget that if you take log backups that often, they're not going to be as large as they will if you take them daily. Therefore I think that you will be measuring the time they take in seconds rather than minutes.

One more thing - if you have Standard Edition, you can't use SQL Server's built-in log shipping facility, which is only available in Enterprise Edition. However, you can set it up yourself instead by scheduling backup and copy jobs.

John

Stephen Pierce SSC Veteran Points: 251 More actions · Answer 4

If I configure the "log shipping" or "backup the transactions log in the 2nd server" in regular basis (every 20 minutes), do you think the performance will be affected?

I backup my transactjion log every hour and it ususally takes around 2 or seconds.

Steve

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 5

You will not be able to move the log files (or data files) to a network drive. SQL Server will not support either on network drives. Only backup files can go there.

Frequent Tlog backups to network drives will not be noticeable, probably only 1-2 seconds.

Good practice to do your full backup to the same place. That way your recovery/rebuild files are in same place.

Mark

Wilson Lo SSCommitted Points: 1993 More actions · Answer 6

Folks,

Thanks for all your help. My problem is solved.

It is true that it takes a few seconds to backup the transactions log.

I am able to a write visual basic script to copy the backup files from my server 1 to server 2 and delete the backup files in the server 2 after certain days.