SQL Server local disk best practices. Multiple Arrays or OBR10

I am looking at putting together a SQL server,  and I am getting some contradictory info from dell.  

It has been best practice for a long time to separate OS/SQL Data/SQL Logs/TempDB on separate arrays, but I am now hearing that OBR10 (One Big RAID 10) is the way to go. If it is not the way to go, it will at least be suitable for our environment, per dell.  

I am interested in hearing an opinion confirming, or proposing some reasoning why this would not always be the case.  

If the current IO load fits under the maximum potential IO of the new server is that all I would need to be concerned about,  or are there more complicated reasons why I would not want to use OBR?  I understood the reasoning more for competing IO characteristics, sequential vs random, and keeping them separate.  Is it all IO, and there for I only need to account for current IO, and make sure the number of spindles, and speed of the disks puts my IO potential well above that?

Thank you,
Who is Participating?
smiliefaceConnect With a Mentor Commented:
OBR10 provides impressive throughput but will (inevitably) cost more and reduce flexibility. To a small IT shop there are undeniable benefits of simplicity. As you scale up and start to get into the TB or PB range of data, this may become less attractive.

Databases use two different types of IO.

Synchronous writes is used for the Log files, and requires that all writes are confirmed written before the process can move forward.

Asynchronous writes are used for Data, which allows the database to hand the responsibility of the write over to the OS and the database moves on without confirming the write. In the event of a crash before that write does occur, the database will simply treat it as an in-flight transaction and roll it back.

I agree with JimP on the need to understand the physical drives in a SAN as well, but with capabilities such as storage tiering, this gets increasing complicated!
Scott PletcherSenior DBACommented:
I think that typically now there are so many files that seq vs random access is highly over-rated as a factor.

From a purely performance standpoint, OBR10 probably is better, what with essentially all spindles available for all I/O.

But from a recovery standpoint, it's extremely dangerous.  Specifically, if the data and logs are on the same raid, and that raid fails ... the db is lost.  But, if data and logs/backups are on different raids, you can always recover the db, no matter which fails.  Raids are extremely reliable, but they are not 100% foolproof.
David ToddSenior DBACommented:

There is a point, that when contemplating a raid5, that when adding disks, the likelihood of two devices failing starts to add up, so the recommendation is to go for a raid 6 which has two separate parities, and can mostly cope with two devices failing in the array.

Likely there is something similar for raid10.

Consider this:
If the array goes and has to be rebuilt/recovered from scratch, you have to recover the whole thing from tape. Compared to recovering only one array ...

Brent Ozar makes the point that if the backups are on a separate array/server, then while you might not want to add new transactions, if something really bad happens to the server you can quickly get the databases/application to a read level and answer customer questions.

Jim P.Commented:
It depends on how the SAN is configured and setup.

I got sucked into a system that I don't know if the original DBA knew about.

The prior DBA had set up the D: as the data drive,  the L: as the log (LDF) files, the T: as the tempdb files, the S: as the system files. This was for five different dedicated SQL Servers.

That sounds great, right?

The problem is that all five SQL Servers were presented the same set of 18 disks from the SAN's "aggregate". Or in other words the D: and the L: drive could have been competing for the disk write head movements against the T: for the tempdb. And the read versus write could be competing as well.

So a simple answer is not available without you knowing the SAN unit configuration versus the SQL server config.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.