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,
LVL 5
shareditAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
1
David ToddSenior DBACommented:
Hi,

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.

HTH
  David
0
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.
0
smiliefaceCommented:
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!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.