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:
- RAID 5 with all functions on a single volume.
- Dual RAID 1 with OS, Backups and system dbs on one volume and database + log files on the other.
- 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.
Best Answer
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.