I know this question has been asked a million times here and the answer always is RAID 10. I understand that RAID 10 offers better performance but my question is at what point do you realize that performance?

I have a server with 16GB of RAM, 4 x 500GB 7200rpm sata drives and a 6 core processor. The SQL database is 4GB and I will have 15 computers accessing the db but not all at the same time. With this amount of data and access really see a benefit from RAID 10?

RAID 5 allows me to keep more disk space for the growth of the data. There is also a working folder of data that is about 250GB that grows along with the db. I don't want to "waste" the extra space to make a RAID 10 array if it won't really benefit me.

I would like to hear answers based on this specific information and not just general info on how RAID 10 is better!

2. Buy 2 x 250GB drives (7200RPM 6Gbps SATA) from a recertified dealer ($150 out the door). Create 250GB mirror for OS and logs and then use the 4 x 500GB drives in RAID 10 for SQL and file share. This will allow me to get the increased read rate from the RAID 10.

Buying extra drives is fine if you need them. But putting the OS and logs on a different array remains a wasteful, poor way to do this. No matter how many drives you buy, the best way to use them is in a single RAID 10 array. Any splitting of the arrays or any array that isn't RAID 10 slows you down without benefit.

I just bought the server with the config I described so I don't want to put more money into it if I don't have too. I probably should have had this debate prior to purchasing but hindsight is always 20/20. Dell would sell me more 500gb drives for $200 each so probably the cheapest route to raid 10 is $400 for 2 more. But I am hoping for no more costs.

Depending on how the database is used is also a factor on what benefits from each RAID will give you. If your database is going to me a majority of reading data and very little writes, then RAID 5 will be a bit better. RAID 5 has better read performance, but has terrible write performance. RAID 10 is not as fast on reads, but makes up for it on writes.

Generally in the database world there is a ton of writes as well as reads, that is part of the reason why most are on RAID 10, that and the size of the disks.

Your 500 GB drives are okay to RAID 5, but if you have a write heavy database, you may not get the best performance, also factoring in those are SATA drives, and not 10K or 15K SAS.

I dont know your workloads so an an accurate answer is impossible. That being said....I think you'll be fine with RAID 5.

You've got a 4gb db and 15 users. That sounds pretty light to me. What kind of growth are you expecting, both in db size and users? Is this an OLTP system or OLAP?

The only thing to watch out for is to make sure you have data files and tlog files on separate spindles. If you can separate out tempdb from you db drives even better. Adjust your SQL Server settings appropriately and maintain your stats and indexes and you likely wont have an issue.

TS_DBA, I guess it would be considered oltp because this is a practice management software for a dental office. All of their scheduling and patient information comes and goes from this db. The growth up to 4GB has been over the past 10 years (maybe a few more) so the rate of increase is not extreme. But he does grow at an increasing rate each year.

By separate spindles do you mean separate arrays? My layout was going to be all 4 disks in a raid5 with an OS partition and a data partition. If put the db on the data partition and the logs on the OS partition. Does that solve the spindle issue?

I mean separate spinning disks.....the randomness of the read/write of the datafiles is the complete opposite of the sequential write nature of the transaction logs. By sharing the same spinning disks (spindles) you've got the potential of having the heads thrashing around. Slow t-log write speed is a performance killer.

But, need to balance these decisions with the impact to your situation. Will it be less than ideal to do it your way? Yeah. Will it be able to scale up? Not likely. Will it matter with your workload? I *suspect* not. I doubt a dental office needs the transactional throughput that "doing it right" will give you. As long as the system is up, is stable, can be recovered and performance is reasonable - they'll probably be happy.

Personally, I think DBAs spend too much time talking about RAID levels. Sure, as a DBA, I am performance hungry and if given my preferences, I will want to go for RAID 10 every time. But if you want to talk about needs, the system needs certain performance, and that is called I/Os per second (IOPS). If this is for an existing system, what is the maximum read and write activity (IOs) of the server?

If you are working with a knowledgeable storage person, tell them what your IOPS requirement is, and let them figure out the best way to give it to you. Otherwise, you're kind of like those people who buy the exotic sports cars that go 0 to 100 in a couple of seconds and then sit in bumper-to-bumper traffic with it barely rolling along.

Robert, I agree, but my experience is that anything below a large enterprise environment wont have a knowledgeable storage person to work with. You work with a sys admin who's never given it a thought. As a defense mechanism the DBA tries to find the storage system that will give the biggest bang for the buck.......because the problem is *ALWAYS* SQL....even when it isnt.

Agree 100%. But I do think DBAs need to make a concerted effort to understand IOPS and how to measure it. Otherwise when they are faced by a knowledgeable storage admin, they won't come out looking well if they don't know what they're talking about.

You have 4GB of data and 16GB of RAM. The entire database can fit inside of RAM. This is a good thing.

You shouldn't have many disk issues, but we didn't get any details of your workload.RAID10 is likely going to be fine for your needs and you are likely to get performance gains by focusing on concurrency bottlenecks rather than disk bottlenecks for this scenario.

I know this question has been asked a million times here and the answer always is RAID 10. I understand that RAID 10 offers better performance but my question is at what point do you realize that performance?

The first time that you need it :) Not sure what you are looking for here. It's always faster. Because storage is typically the bottleneck for all performance and especially for databases, you pretty much feel it on every query.

Robert, I agree, but my experience is that anything below a large enterprise environment wont have a knowledgeable storage person to work with. You work with a sys admin who's never given it a thought. As a defense mechanism the DBA tries to find the storage system that will give the biggest bang for the buck.......because the problem is *ALWAYS* SQL....even when it isnt.

Personally, I think DBAs spend too much time talking about RAID levels.

I'm a bit of a RAID guy (ha ha) and I agree. Way too much focus on it. Avoid RAID 5, stick with RAID 6 if you need capacity, RAID 10 if you don't. Easy, done. If you need something else you'd have the resources to know it and would never need to ask :)

500GB drives are fine for RAID 5 it is really 1TB where you draw the line.

Drive size is not a factor, array size is. Only array size is. If your array is anywhere near 1TB total, RAID 5 can start to be a staggering risk. If you have 500GB disks, the smallest RAID 5 you can do is 1TB usable.

Personally, I think DBAs spend too much time talking about RAID levels. Sure, as a DBA, I am performance hungry and if given my preferences, I will want to go for RAID 10 every time. But if you want to talk about needs, the system needs certain performance, and that is called I/Os per second (IOPS). If this is for an existing system, what is the maximum read and write activity (IOs) of the server?

If you are working with a knowledgeable storage person, tell them what your IOPS requirement is, and let them figure out the best way to give it to you. Otherwise, you're kind of like those people who buy the exotic sports cars that go 0 to 100 in a couple of seconds and then sit in bumper-to-bumper traffic with it barely rolling along.

But, need to balance these decisions with the impact to your situation. Will it be less than ideal to do it your way? Yeah. Will it be able to scale up? Not likely. Will it matter with your workload? I *suspect* not. I doubt a dental office needs the transactional throughput that "doing it right" will give you. As long as the system is up, is stable, can be recovered and performance is reasonable - they'll probably be happy.

Just want to give this a big thumbs up. I can't imagine a dentist's office would need massive throughput. As long as data is being served up reasonably, I bet it'll be fine and performance will be acceptable.

Like TS said, take good care of that server and maintain your databases properly, and things should be OK.

I'm hopped up on pain meds so i might be talking out my arse here but I don't see the issue. You have four 500GB drives which is all you need to impliment RAID 10 so why the talk of extra costs?

Yes you get 1TB of storage vs 1.5TB if you used RAID 5 with the same disks but your talking about a database thats taken 10 years to hit 4GB, you say the working data folder is at 250GB and grows with the database, so assuming that growth is proportionate to the database and continues at a linear progression, unless this this box has to do more than you have said then Ignoring performance you could run it on RAID 1 with the other disks as hot stand bys and based on that growth you would still be decomissioning the disks for life cycle reasons before they were full. So on that basis RAID 10 offers you the space required.

Given your also running a 250GB file share which I can only assume are things like high res xray scans or photographs thats yet more reason to go for a high performance RAID 10 option as your SQL read and writes will be contending with your file share and I suspect the same box is your AD controller?

I imagine its the same in the US as the UK but I can source genuine Dell hot swapable disks for often near half what Dell are asking for them by going to reputable online retailers. I have said as much to Dell in the past when ordering kit and seen the cost of the disks plumet on my quotes shortly after I tell them I'll take a diskless config.

Finally If you're going to spend more money on anything make sure there is a good backup solution in play as backups are like condoms, its always better to have them and not need them than need them and not have them. Sure RAID 5 & 10 can both take a single disk failure (2 in the case of RAID 10 should you get lucky) but it doesn't do you much good if no one spots the failure before you get a chance to rebuild the array.

With your hardware choice there is no way to improve performance. Use RAID5 (one spare drive) as it will at least provide you some redundancy and space.

For all other readers not stuck with the wrong and not performing hardware:

If you have a very small database and little amount of users: virtualize it.

If you need performance: disk speed depends on type (SAS faster as SATA, but needs a good controller!), rpm's and amount of disks (lots!; tests are done on > 80 disks with small sizes to get better results). As more disks and faster spinning as better (but also more expensive). SSD can help for certain things like small but fast access (i.e. writing logs, housing the temp log or DB ).

RAID level helps if it comes to lots of hard drives as more you have to write to as better. Also it can contribute to read speed if you are aware of the location of your database and log files and if you separate them to different drives (logical drives or arrays and raid volumes; not partitions)

﻿﻿Lots of memory and cores (at least two cpu's) would add more performance to your OS and SQL application to keep the records to provide them quicker. (again unfortunately that might cost you also on licenses; another reason to virtualize)

And of cause you need to know how the information is generated and comes to or goes out of the database to understand the external dependencies before looking into hardware.

Google for best practice on your specific hardware and sql type to find more dependencies.

With your hardware choice there is no way to improve performance. Use RAID5 (one spare drive) as it will at least provide you some redundancy and space.

This is the worst possible use of these drives. This is both the slowest and by far the most dangerous way to use four drives. There is a reason that RAID 5 with a hot spare should never have been done and another reason why RAID 5 was deprecated in 2009 and another reason why only RAID 10 ever makes sense with only four drives. Doing RAID 5 with a hot spare is wasteful in every way and gives zero extra capacity over safer, faster methods. It's teh worst of all possible factors.

1. Create 2 mirrors from the 4 500GB drives. Mirror 1 is for OS and logs and mirror 2 is for SQL and 250GB fileshare (which does contain xray images etc...). This seems like it would have good performance for what I need.

2. Buy 2 x 250GB drives (7200RPM 6Gbps SATA) from a recertified dealer ($150 out the door). Create 250GB mirror for OS and logs and then use the 4 x 500GB drives in RAID 10 for SQL and file share. This will allow me to get the increased read rate from the RAID 10.

1. Create 2 mirrors from the 4 500GB drives. Mirror 1 is for OS and logs and mirror 2 is for SQL and 250GB fileshare (which does contain xray images etc...). This seems like it would have good performance for what I need.

No, this is terrible, only the RAID 5 with hot spare gives worse performance. This kills your performance and wastes capacity and makes the system unnecessarily complicated to manage. One array, RAID 10. There is truly nothing to question here. RAID 10 is the best option in every way, speed, reliability, capacity. No downsides. ANY option different than that will cost you for no reason.