Re: SQL Server 2005 disk layout - opinions?

On Mar 27, 11:54 am, aj <ron...@xxxxxxxxxxxxx> wrote:
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 help 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

You have a good start here. The size of your logs is going to be very
important here, and they will depend on many factors. The first is
what recovery model's will you be using? Where will you do your daily
backups to? Will you have enough drive space with that format to do
weekly maintenance such as index rebuilding? Breaking your drives
apart will make things faster, but it comes at a price, mainly wasted
disk space. Think about the above questions and let everyone know
your thoughts :)

Good day,
Joshua Austill
Senior DBA
.

Relevant Pages

  • Re: Advice Requested : Disaster Recovery with 2 Drives (No Raid) with SQL Server 2008
    ... so we need frequent copies of the database for testing, ... Which drive for logfile backups? ... Why are you using Enterprise edition of SQL Server if you only have Std ... drives with no Raid. ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding another server instance to another hard drive?
    ... A disaster recovery plan will help you minimize data loss. ... If your computer crashes and/or you lose your SQL Server drive, ... get lucky with a successful attach of the database files if your database ... > drives to actively store data. ...
    (microsoft.public.sqlserver.server)
  • Re: Cool boat & travel computer
    ... For the "new" PCXT, the biggest FULL HEIGHT hard drive was the Tulin ... drives and 256K of RAM. ... these nice 9-track drives with bus adapter cards and drivers in Computer ... run the custom database, print perfect Meter Cards so the technicians on ...
    (rec.boats.cruising)
  • Re: Restore Master DB to New Server
    ... the user database will be suspect. ... Just drop them and then do the restore. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
    ... You'll not be using most of the system databases intensively so you don't need to seperate them, I mean locating them on different physical disks. ... If it's being used intensively in your environment then you should locate it's log and data files on different physical disks. ... For this question you must understand the reason why we should seperate data and log files. ... Of course these drives must be physically seperated so that you'll gain performance benefits. ...
    (microsoft.public.sqlserver.setup)