6

I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single hot spare. I'd like to stretch to 8+ drives but the costs are a little out of my budget right now (and probably somewhat overkill).

So my question is, what would be the optimum configuration for SQL Server 2012 when limited to 4 disks? The database is around 29 GB and is growing around 250-500 MB per month. The database will typically deliver 80% reads to 20% inserts/updates/deletes.

I understand from researching this subject that my options are as follows:

  1. RAID 5 with all functions on a single volume.
  2. Dual RAID 1 with OS, Backups and system dbs on one volume and database + log files on the other.
  3. RAID 10 with all function on a single volume.

I'm looking for a solution that will give me reasonable performance but won't obliterate the array if a single drive fails (which I understand is quite common with SSDs).

Current Hardware ------------------

HP ProLiant DL360 G7 1 x Xeon E5640 / 2.66 GHz - RAM 12 GB - 2 x 300GB Pluggable SAS SFF 10,000 rpm disks in RAID 1.

Improve this question
6
  • The missing information here is what kind of hardware the DB/web-server is currently running on? ie how many disks, what configuration and whether they are SSD or not.
    – Jack Douglas
    Commented May 27, 2013 at 19:24
  • Current hardware details updated (see above). I should add that unless under heavy load this configuration holds up pretty well.
    – QFDev
    Commented May 27, 2013 at 19:42
  • 3
    I think my suggestion would be similar to COTW's then, go for some good SSDs like the s3700 (or possibly the s3500 coming out next month). I'd just get two and put everything on a RAID1: the step up from your SAS array will be so enormous you really don't need to worry about making marginal gains with a more complex setup. Personally with that growth rate, I'd provision for 5 years and then double it at least so would want to get 2x200GB drives. Do drop by The Heap if you'd like to chat more.
    – Jack Douglas
    Commented May 28, 2013 at 7:44
  • This strikes me a as a rare and very welcome way to do business: I wish you every success with that!
    – Jack Douglas
    Commented May 28, 2013 at 7:52
  • 1
    Have you considered hosting? I have a hosting environment that would be suitable, but whether it would be cost effective or not may depend on how you are licensing SQL Server.
    – Jack Douglas
    Commented May 28, 2013 at 11:17

1 Answer 1

Reset to default
8

With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck. SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks.

If you're getting SSDs for a DB server, get ones designed for a long life span, such as Intel S3700s. You're probably better off getting two of those than four cheaper ones, and 2x100GB units + a hot spare (which is probably enough to keep you going for a few years at your current rate of growth) should cost around £200 each according to Google Shopping.

Mixing logs and data on the same disks isn't such a big deal any more. However, you may get some resiliency out of having separate log drives. If you want to do this then 5 disks (enough for two pairs + a hot spare) should still see you with change out of £1,000.

Try two good quality drives in a RAID-1, with a hot spare for a starter. Your growth suggests you probably won't have transaction volumes so high that this setup can't keep up with your application workload.

Back up your database onto spinning disks - a couple of enterprise-grade SATA disks and a hot spare should cost you a few hundred dollars.

If (and only if) you have performance problems with that lot then you might look into adding more SSDs, but I suspect just one pair will be fast enough to keep up with your transaction volumes quite comfortably.

Improve this answer
6
  • Depending on volume etc. you may also want to look into adaptec raid controllers and their hybrid raid 10 - one ssd paired with one platter, all reads going to the ssd. That is to keep a server running instead of going the still more expensive full ssd, in case of a failure. Doing that now with a 2tb size database.
    – TomTom
    Commented May 27, 2013 at 18:48
  • @TomTom I think LSI have something similar
    – Jack Douglas
    Commented May 27, 2013 at 19:15
  • Not to my knowledge. Usage of SSD cache is not identical - this is Hybrid Raid. Uses a lot more SSD size... thus more expensive. It is not on board - it is "mirror hard disc with ssd and have all reads go to the SSD". As scuch, I can scale it as large as I scale the raid - obviously for a little price of having 50% in SSD. Caches are good, but when you do data warehouse size stuff, they get - ah - small ;)
    – TomTom
    Commented May 27, 2013 at 19:24
  • 1
    @TomTom I understand now, thanks, but tbh I'm dubious about going that route: cost is never less than half the full-ssd cost and complexity is higher, I'm not sure if that is a worthwhile trade-off. The adaptec docs also leave me feeling a bit uneasy for some reason.
    – Jack Douglas
    Commented May 28, 2013 at 10:47
  • 1
    If your workload consists of a lot of random IO (which is likely for a database server), I strongly recommend ConcernedOfTunbridgeWells' suggestion over a hybrid solution. Random write performance to a single SSD can rival that of entire trays of spinning discs, and you won't suddenly drop to spinning-disc speeds if your SSD fails.
    – Nathan Jolly
    Commented May 28, 2013 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.