June 25, 2008 at 9:27 am
Hi all, first post.
We had some prospective consultants in the other day and one of the things they suggested was placing our ldf files on a separate server, for performance and recovery reasons. I understand that it's best practice to keep them on a separate physical disk from the data files, but a separate server altogether? I can't find any documentation on that. Is that even possible, or did we misunderstand them? Perhaps they meant log backups.
Thanks in advance,
Robert
June 25, 2008 at 9:34 am
Backups on another server i can understand.
But you would want to have your transaction logs as close to your database as possible.
June 25, 2008 at 9:36 am
Was the consultant talking about Transaction Log shipping to another Server? So that you can set up a Warm Stand By?
-Roy
June 25, 2008 at 9:53 am
Roy Ernest (6/25/2008)
Was the consultant talking about Transaction Log shipping to another Server? So that you can set up a Warm Stand By?
No, they were referring to our current setup. We *should* have a Warm Stand By, that's a whole other issue. Our production server is 64-bit. We just freed up our old 32-bit server and are repurposing it as a file server. Would we be able to use it as a stand by SQL server?
June 25, 2008 at 11:08 am
As far as the second server as a standby - yes, you can mirror, log ship, and replicate between 64bit and 32bit servers in most cases.
As far as the log files on another server? This is probably not a good idea. Using a shared drive for data or log files will likely be much slower than using local resources. It also opens you up to the possibility of networking issues causing failures that you could never have on local or SAN drives.
June 25, 2008 at 11:39 am
Thanks Michael (and all who replied), that's what I figured, but as a n00b I'd rather ask than assume. So here's a followup question: Does having a RAID 10 array obviate the need put the transaction logs on on a separate physical drive from the data files? My network admin thinks so.
June 25, 2008 at 11:50 am
No. Also, it is not a need - it is a recommendation.
RAID 10 striping will be pretty fast, but with a single array, you only have a single set of read and write heads. If you are able to move your log files or individual file groups onto their own physical arrays, a log file operation writing to disk will not interfere with a data file write to disk. With a single RAID 10 drive array, they can conflict with each other.
In addition, if the files are stored away from each other on the drive the heads may have to move a significant distance to switch from a data file operation to a log file operation.
However, depending on the workload of your system, the size of your databases, and the types of operations being done, a single RAID 10 array may be fine. The only thing I always recommend is having a RAID configuration that supports automatic handling of failed drives. This is a must in any production database environment - drives go bad way too often.
June 25, 2008 at 11:54 am
No.
The point of having a transaction log alone on a drive is so that the disk's write head is always (*) in position to write, since a log is written linearly. If the transaction log shares with anything else, the disk's write head needs to be repositioned for each log write
(*) Transaction logs are mostly write-only, though replication, recovery and a few other things will require a read of the tran 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
June 25, 2008 at 12:32 pm
Michael & Gila, thanks, that clarifies it immensely! We'll probably throw another array on the server just for the transaction log, for performance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply