LinkedIn | FaceBook | Twitter
In a previous post, I mentioned that you should use Filegroups and I talked a little about those. In this post, the best practice I'll point out is where those files should live.
As a general rule of thumb, more "spindles" in a database are better. What that means is that a larger number of physical drives with the files spread out on them perform better than more files on a single drive. That's within the database itself. For the larger picture, you need to think about the other kinds of files involved in the database as well.
At a minimum, I always recommend that these kinds of files be separated onto different physical drives - not driver letters, mind you - actual hardware:
Even within these, Filegroups help you separate the Data and Indexes onto even more physical drives. Other files, like Filestream or Full Text Indexes, also need their own hardware.
Does it really take this kind of planning? Isn't all this a little much? Yes, it really does take this kind of planning, and it isn't too much to ask to plan for good performance, even down at the file level. This is all part of "it's not the database platform, it's you" discussion. Take the time, learn the facts, layout your plan and bask in the glow of the results. OK, maybe that last part *was* a bit much, but you get the idea.
PingBack from http://asp-net-hosting.simplynetdev.com/sql-server-best-practices-file-layouts/
Wouldn't you want to put the logs on a different physical drive too?
Is the “Best Practice” then to have a single physical drive for each data type? As above, 7 individual drives? If this is correct do you also recommend drive redundancy like raid 1 or 5? Are there recommendations for someone with a smaller server as far as how to group say logs and backups on the one set of drives, data on another, etc. if I don’t have more than possibly 2 or 3 separate spindles?
In a previous "SQL Server Best Practice" post , I made reference to file layouts and where
pklages - great question. I answered it in another blog: http://blogs.msdn.com/buckwoody/archive/2009/06/11/sql-server-best-practices-file-layouts-revisited.aspx