One area you seem to have missed in this is expected maximum transfer rate. Also, a sense of how 'noisy' your IOPS curve is. If it is very noisy you can have sustained periods of significantly over average IOPS, and that's a case you'll need to engineer for. From experience, some of the biggest bursty IOPS occur with large transfers, and if those large transfers saturate your I/O subsystem somehow, other actions during those transfers will suffer.
Peak loads do need to be considered, as you want to perform adequately when they occur. This may mean your system is underutilized a lot of the time, but that sort of comes with the territory. We create a minimum service guarantee over the expected load-range and manageable growth, which leads to a certain amount of unavoidable over-engineering.
Another area is expected read/write I/O percentages. You said web-server, so I'm guessing it'll be more reads than writes but you'd know best. If the percentages are heavily skewed towards reads (say, 80% reads) that'll have an effect on what you select for the storage sub-system as you'll be able to afford expensive writes in order to get fast reads (RAID5 or RAID6 for example). But not too expensive, as you don't want to saturate something with a huge write that'll bog down the whole system.
Once you do get hardware, do test failure modes. Figure out how bad things get when a drive has failed, and when one is added back in. If you only have five disks this may not be a big deal as failure rate should be low enough that bad disks should be a very infrequent occurrence. But if you have a lot of spindles (say... over 10) your failure rate may be high enough that you have to consider the 'failed' state into your estimations. We got badly bit by this one a couple years ago, as a certain drive array seriously bottle-necked on writes when it was rebuilding a parity set (it disabled the write cache, evil, evil thing), which caused havoc when someone attempted to write a CD image (625MB!) to it during this period.
And finally, consider the load during backups during your estimation. If you're going to have to provide service when the backup is busily reading everything on the server, that'll also have an effect on how beefy a storage system you get. So, consider utility I/O operations, not just user generated ones.
This should give you a few more data-points to work with!
**edit:* Peak headroom... that depends on load. I have a system that during the day averages between 3-5 MB/s with peaks in the 10-15MB/s range, and backup can push it to 20-25MB/s. Average, therefore, is around 12MB/s, with true peak a bit over twice that. This particular system doesn't suffer significantly during RAID rebuilds so it doesn't enter into planning. Also, end-user driven I/O is minimal during the backup period so I don't have to worry about contention, which means that I can run it flat out during backups without fear of getting calls.
Lets start from the top.
I've got a large database for a
telephony application, about 60GiB or
so
Rephrase that: I have a pretty small database. Seriously, the time where 60 giga where large is about 10 years ago. Compare that to: I have a finaincial data database that has 800gb and growing, with 95% of the data in one table ;)
It'll have four internal HDs,
probably for OS and backup, but the
biggest change is the attached storage
- 12 x 15k drives on an external SAS interface.
Here is what I would do:
- Mirror two discs for boot. Put off a 64gb partition down, the rest up you use for TEMP. You do not want to see a lot of IO there.
Mirror the next 2 discs for logfiles. If those run hifh on IO - relace them with SSD. Given the small amount of changes you have.... small 80gb SSD should be enough.
The rest (12 discs), put up a hugh RAID 10.
More important is that you reconfigure our server to use:
- Minimum 12 data and log files for tempdb. Do NOT autogrow those. Fix them.
- Minimum 12 log files. No joke. Do not autogrow here, either.
- Minimum 12 database files. Did I say - no autogrow?
Then, of course, there's always RAID
10 vs RAID 5/6, vs RAID 50/60 to
consider.
What please is to consider there, given the HUGH performance differences between Raid 10 vs the others - which all blow the water out of Raid 5/6/50/60 for anyhing requiring high IO. RAID 5 / 6 make onl ysense if you put in SSD drives - then the significnat IO loss will be totally eaten up. Actually given your trivial database size, it may be financially idiotic to even go with 2x15 SAS discs. Get 2 x200gb REALSSD drives and you will have about 100 times the IO performance if a RAID 10 over your 30 drives. Given the significant cost of the infrastructure you may save a LOT of money on the way.
Actually the smartest thing would be to not ordet the whole SAS thingy - you have 4 drive slots, put the OS on two drives, use 200gb SSD in a Mirror on the other one. Finished. And a LOT faster than your SAS stuff, too ;) THe joy of having a trivial datbase size. Check http://www.fastestssd.com for the current state. A modern SSD will reah 200mb sustained random rates in that setup, even if not top of the line. THis will seriously wipe the floow with the mediocre IO you get from your SAS setup.
Or: 30 SAS discs are maybe 4800 IOPS. RealSSD gets up to 50.000 - on one disc, with "weak times" of around 36.000 IOPS. That means that ONE SDD is about 7,5 times as fast - in slow moments - as your 12 disc setup. Around 10 times as fast in good times. Ouch.
Be carefull to properly align the parittions and properly format the file systme (hint: do not use the standard 4kb node size - stupid for SQL Server).
I could do a massive RAID 10 of 6
disks and throw the entire DB onto it,
but I've been considering breaking up
TRAFFIC and it's index files onto
separate partitions - and possible
BILLING as well. In addition, the
Everything Else might do well in it's
own File.
That would be stupid abuse of SQL Server. GIven that it does load balancing between files and wants/ asks for multiple files per group (one per logical processor) it would not gain anything - au contraire. Separating files and indices achieves NOTHING if they end up on the same discs anyway. In your case you are better off with one filegroup, 12 files. If you want later scalability, you may want to go for 48 data files to start with - gives you room up to processor 48 cores.
You may want to use two filegroups to splt off the billing data from the less volatile -/ requested one, but not for direct speed, but for the priviledge of posibly moving them totally later off without reorganization - that is what I did with my financial database.
Last words: whoever purchased the server made a bad decision hardware wise. There is no reason to have an external SAS tray for something that small. My database server is from SuperMicro and has 24 disc slots in 2 rack units height. That is without external cage. I dont really want to compare the numbers here- but I bet it was a lot of wasted money.
Best Answer
What exactly does it do? The excerpt from this Compaq document explains it well:
Power interruptions, even for brief moments, result in the loss of data which was being written to or read from storage... Power interruptions can have terminal effects on data which is in the process of being written and is temporarily residing in cache. This data does not yet exist in the storage environment and has been offloaded from the server.
Therefore, loss of power to the cache means any data stored in the cache is lost forever.
If, however, the cache has a power source independent of the external IT environment, the data in the cache can be maintained for a short period of time, allowing the recovery of that data and the protection of the integrity of business critical information. Battery backing of cache serves this function of an independent, though temporary, power source. Battery-backed cache will normally retain the integrity of the data in the cache for a period of several days, depending on the capacity of the batteries at the time of the failure.
Is it just for RAID configurations? Anything doing write caching can have BBWC, RAID controllers and SAN's cover most of these.
If there is a power malfunction, isn't the data loss inevitable? It's more likely to occur if you have write caching enabled & no battery backup. It's a strong recommendation that if there's no battery backup that write caching is disabled.
Are there any performance improvements from it (assuming the server will mostly do sequential reads and sequential writes)? Write caching is there for performance, what it basically means is when writes are flushed to disk, the disk controller returns to the OS stating that the writes have been committed, when they're still actually in cache & can be written later on.
What affected your decision when you faced the option to buy one? It's a standard option for any professional RAID or SAN on the market, Write caching & battery backup go hand-in-hand & is a must, especially if your system is for a database. Write caching without battery backup is dangerous & an outage can leave a database in an un-recoverable state.