Usefulness of separating database files and trans logs among partitions

Question

Some guys where I work are setting up a new database server for SQL Server. The server has a system drive that also stores backups and a single RAID 5 array for the database files. The RAID 5 array is logically partitioned into 2 drives. One is for the actual database files (MDF) and the other is for the transaction logs (LDF). The quoted reason is best practices.

My question is whether this extra partitioning is pointless. Is there any purpose to having the database and trans logs on different logical partitions if they are still sharing the same physical resources? I understand there are some great reasons for separating the database and trans log files on to two physically separate disks. For instance, speed and disaster recovery should be improved. However, I can't think of any benefits that aren't negated by the lack of physical separation. I don't know enough about RAID to know for sure.

Answer 1

You're pretty much on the mark. The only other situation that could go wrong is a partition corruption, and they're incredibly rare (and probably catered for by your RAID hardware).

I find it's easier to talk about separating data files from log files (or, indeed, separating filegroups for table-read parallelisation) by referring to different spindles rather than different drives. It makes it more obvious to those who know a little and know it dangerously :)

Answer 2

Separating data and log files is usually overkill. If you actually have a situation where you need it, you'll know.

But even then, it only helps if you separate them onto different spindles. Having them on the same physical spindle(s) doesn't gain you anything.

On the other hand, you don't lose anything - except the comfort of knowing that everything related to your database is in this folder.