question

Ben Adderson avatar image
Ben Adderson asked

SQL Server hard disk/file setup

What are the best practices when it comes to arranging your mdf's and ldf's on disk? Are there particular HDD configurations (RAID, SAN etc.) that are particularly advantageous?

Edit: I'm interested in both the simpler solutions (e.g. for configuring the local instance on my dev box) and the more complex solutions (for production servers under heavy load)

sql-serverbest-practicefilegrouphard-disksfile location
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is particularly pertinant for SSC, given that we're going to be moving to new hardware in the not too distant future, and that will probably involve shared storage for the database.
1 Like 1 ·
Steve Jones - Editor avatar image
Steve Jones - Editor answered

I think Matt Whitfield has some good advice, and I'll add in what I consider to be the order in which I set things up.

I've used R5 in many places, but usually because of cost considerations. There are good reasons at http://www.baarf.com/ not to use it, and I won't repeat them here. If you can afford it, go with R1 or R10. DO NOT use R0.

The first thing you need to do is physically make sure backups are separate from data. If you have two physical drives (or arrays/LUNs), then put the OS, pagefile, and backups on one, data on a second.

If you can move to 3 separate drives (physical), separate logs from data.

If you can go to 4, I would either move tempdb (data), the pagefile, or separate out heavily used databases/filegroups from each other. You have to measure the writes and load for each of these items and decide what to move.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve, I'm building a dev box with 3 drives (so far). I was going to put tempdb on that 3rd drive (40gb IDE); would it be better to instead move all the log files (or as many as possible), onto that drive? If possible, should I put both tempdb and the log files there?
0 Likes 0 ·
thatismatt avatar image
thatismatt answered

One of the simplest things you can do is to use a different physical disk for the SQL server data files (.MDF) and for the SQL Server log files (.LDF). The reason behind this is that .MDF files are accessed randomly, while .LDF files are accessed sequentially.*

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

First and foremost - understand your data profile before you make a decision. RAID 5 disks are more suited to situations which involve a lot of random writes, whereas RAID 10 can be a lot faster for infrequently written but heavily read data, or for sequential writes.

Secondly - give yourself time to test and make an appropriate judgement. It can be a pain setting up SANs multiple times, especially seeing as in a cluster environment the SQL Server cluster resource must depend on the disk resources it uses - but if you have the time to test it and make some appropriate measurements, it can save a lot of time further down the line.

Thirdly - a large number of smaller disks can often out-perform a smaller number of large disks for random access, whereas larger disks can be beneficial for sequential access.

Fourth - always include a hot-swap spare in your array. Lightning does strike twice!

This is just based on my experience, hope it helps.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

Microsoft has some recommendations here: technet.microsoft.com/en-gb/library/cc966414.aspx technet.microsoft.com/en-gb/library/cc966534.aspx

Make sure you consult your SAN vendor. They may have specific and proven recommendations for using their hardware with SQL Server.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.