Log file partition

I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
>
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
>
> EdComments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> > Diane,
> >
> > The standard 3 drive setup is to put the operating system on the C
drive,
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
> >
> > Ed
>
>|||Stev
Most sites (and most people) have different views on the perfect disk set up
The way we do it
'C' Operating system (NT4 or Windows 2000/2003
'D' Install SQL Server, System databases, User databases primary filegroups containing system tables onl
'E' User databases user table
'F' User databases transaction log
'G' Backup
'H' Application Dat
D, E and F are usually RAID 1 + 0 all other drives are mirrored
We find this gives us good performance and good resilience
Regard
John