I'm planning the disk layout for my soon-to-be-live SQL Server 2005 SP2
OLTP database. I've read all of the "best practices" doc I can find,
and have an idea of what I will do. I am looking for any comments and
generally having the experts shoot holes in it. Any is
appreciated.

I know there are a million more questions that could be asked about the
system -- I'm just trying to define /general/ guidelines for this (or
any other) SQL Server system that I administer.

I have an EMC SAN. I will separate storage into 3 RAID 10 LUNs
(separate spindles):
D: is TEMP
E: is DATA (DATABASES)
F: is LOG
The server has 2 dual-core CPUS, so I will plan on 4 equally-sized DATA
files per user database (I will only use a PRIMARY filegroup). Should I
have 4 equally-sized LOG files per user database as well?

Lets assume I have user databases called A and B, and perhaps 30-40 gigs
of total data. Logs will be ~25% of database size. Here are the drive
contents:

D:\ (TEMP)
tempdb.mdf
tempdb1.ndf
tempdb2.ndf
tempdb3.ndf
templog.ldf

E:\ (DATA)
MSDBData.mdf
model.mdf
master.mdf
a.mdf
a1.ndf
a2.ndf
a3.ndf
b.mdf
b1.ndf
b2.ndf
b3.ndf

F:\ (LOG)
MSDBLog.ldf
modellog.ldf
mastlog.ldf
a.ldf
b.ldf

Is there any good reason to create database sub-folders in the drives?
Like:
E:\msdb\MSDBData.mdf
E:\model\model.mdf
E:\master\master.mdf
E:\a\a.mdf
E:\a\a1.ndf
...and so on....

As I said: any comments appreciated.

A soon-to-be SQL Server DBA..

aj

Since this is not my area of expertise, I asked in our internal MVP
forum, and my MVP mate Andrew Kelly had this comment:

>>>>>
I have an EMC SAN. I will separate storage into 3 RAID 10 LUNs
(separate spindles):
D: is TEMP
E: is DATA (DATABASES)
F: is LOG
The server has 2 dual-core CPUS, so I will plan on 4 equally-sized DATA
files per user database (I will only use a PRIMARY filegroup). Should I
have 4 equally-sized LOG files per user database as well?
<<<<<

It is OK to have 4 files for tempdb due to the potential contention issues
outlined here:
http://www.microsoft.com/technet/pro...ngwithtempdb.m
spx

but I would not create multiple files for the user databases if the dbs are
of the size you mention. But you should definately create a secondary
filegroup with 1 file and leave only the system objects in the primary
filegroup for recovery purposes. And DO NOT create more than 1 log file per
db. Adding additional logs will not performance due to the sequential
access. I would also move the tempdb log file to the same Raid 10 as the
other log files.

>>>>>
Is there any good reason to create database sub-folders in the drives?
Like:
E:\msdb\MSDBData.mdf
E:\model\model.mdf
E:\master\master.mdf
E:\a\a.mdf
E:\a\a1.ndf
...and so on....
<<<<<<<

If the number of dbs are reasonable then I prefer to have the data and log
files in separate sub dirs so there is never an issue if two dbs have the
same physical name for the files.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx