February 29, 2008 at 8:05 am
We are having a discussion as to best practices. SQL2005 SP2 x64. Current config is:
RAID1- 2 PHYSICAL DRIVES
C:\ (Windows 2003)
•Microsoft OS
•MSSQL Server installation to \Program Files\Microsoft SQL Server\ directory
•Master, model, msdb data files (mdfs) and transaction logs (ldfs)
L:\ (SQLLogs)
•All transaction logs (ldf) for user-defined databases
•Tempdb log file
RAID5- 4 PHYSICAL DRIVES
S:\ (SQLData)
•All data files
•Tempdb data file
Is there any performance pros and cons to leaving the Master, model, msdb and related hidden databases as we have configured? Should I plan to move to the other drives?
All opinions welcome!
February 29, 2008 at 8:05 am
i would create tempdb files on a separate RAID1 volume and limit the growth of the ones on the c drive
February 29, 2008 at 8:15 am
just a short list:
- Gut feeling is not to install any datafiles in program file folders
- stay away from the boot drive or paging file drive with any datafiles
- What's the purpose of the databases ?
Mainly read : yep Raid 5 may be a good choice
Mainly write: Raid 5 may not be your best option. Prefer raid1, raid01
- I wouldn't worry about having master/msdb/model at the same drive as the userdatabases to begin with.
These databases will be rather small (maybe except for msdb because of
joblogs etc)
- tempdb should be on the fastest possible volume you can have.
If on a raid volume, use raid1 or raid01.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 29, 2008 at 8:46 am
Move the tempdb to RAID 1 and then put your executables in a other drive than the boot drive.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 10:05 am
Oh how I wish I worked in a perfect world! If so, I'd have my logs on a dedicated RAID 1, my tempdb on another and my data on their own RAID 5.
However, real world we're lucky to have 2 RAIDs to work with usually and one of those tend to have the pesky (yet necessary) O/S and program files running on it.
That being said, our prod 64-bit clusters run with local disk a RAID 1 (O/S and Prog files) and then all system DBs, logs, and data (oh, BACKUPS TOO!) are on a massive RAID 5 SAN with a monster cache up front. No performance problems at all and this SAN is for our entire enterprise, not just SQL.
Life gives you limited options, you make limited option-ade.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply