Database File Configurations

[scottcabral1979]

Hi,

We have been given a new Database server with SAN drives attached for storage. We will see the SAN as 4 separate drives on the server (D,E,F,G). Drives D and E will be a 7 disk array using RAID 5. F and G will be s separate 7 disk array using RAID 5 also.

My question is how best to configure our databases on these drives. I was thinking of splitting the systems db's, user db's, indexes and logs like this:

D: System DBs
E: User DBs
F: Indexes
G: Logs

but i'm not sure if that is the best solution. I wanted to keep data and indexes separate as well as data and logs separate. I'm not sure if it makes sense to keep the system dbs on there own filegroup or not.

Any ideas or best practices?

thanks
Scott

 

[biglebowski]

I always try and put the temp db on it's own array/logical disk that is set up as RAID 10 for the best performance.

Also try and keep the data files and logs on seperate dedicated disks. Don't see an issue with putting system db's and indexes on the same drive as we have static indexes that don't change, although others may have better advice.

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.

 

[gmmastros]

biglebowski,

I'm curious, do you put TempDB data and log on the same disk, or do you use separate disks for the data and log files, even for TempDB?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[biglebowski]

different drives but to help the db is in simple mode also I preallocate the db and create the same number of data files as cpu's

The db's we use are oltp and can be around 6TB on average, they are doing some serious read/write transactions so through painfull experience have found that trying seperate everything out is the best way to go.

Even the indexes are on seperate drives as the main db index is around 2TB and the application runs some long queries for MI reporting.

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.