SQL server : disk in raid or split DB and OS on different disk ?

Question

Hello,

We are going to change our server with a new one with 2 scsi hard disk.

My colleague proposed to install the OS windows 2003 on one disk and put the Databases with sql server 2008 (3 of 100 MB and 1 of 2 GB) on the other one.

I was thinking that it would be much more performant to use Raid 0 but i don't have enough knowledge to confirm that, and how much would it be more performant.

Could you please tell me what would be the advantages and inconvenients of both of the methods and which one would be more performant ?

Thank you for your help Vivien

Answer 1

Well what sort of workload is your database performing?

If you are storing data and searching it a lot, you want a mirrored setup (Raid 1) so that you get both redundancy and improved read performance.

If on the other hand you are performing a lot of writing such as OLTP then you would want speed. But with two drives, I would recommend Raid 1 (mirroring) by far. The cost of you losing your database is far outweighed by any small performance gain from striping.

EDIT Many props for getting my raid number wrong. I clearly stated mirroring, not striping but got my number wrong. Thank you @Thorarin for pointing this out (red face...)

Answer 2

I couldn't recommend raid 0 (plain striped, no mirroring) for anything mildly important. One disk failure and it's all gone. Use raid 1 (mirroring), 5 (striped with parity), or 10 (mirrored and striped).

If you have a hardware raid with a battery backed cache, Windows seems to recognize that and will treat transactions as committed as soon as they're in the raid card's write buffer, rather than waiting for them to be physically written. And for read performance you'd just make sure you have a lot of ram for cache.

As for 1 big raid volume vs 2 small raid volumes on separate disks, the latter can be faster if you put your tables on one and your database journal (transaction log) on the other.

Answer 3

Assuming you're stuck with this 2 hard disk setup (not ideal):

First of all: do not use RAID0. Sure, your performance would go up, but if the data you're storing is worth storing, RAID0 is an awful choice as it actually reduces your data security.

Place the database on the second disk. Place the database transaction log on the OS disk, but on a separate partition.

• If the first disk fails: no biggy. Reinstall the OS and you're back in business. No high availability, but no data loss.
• If the second disk fails, you'll be able to do a 100% restore using the latest backup and the transaction logs. Again, no data loss, assuming you do regular backups.

If high availability is important, or if your database actions are mostly read operations, RAID1 is the better option. Most RAID1 setups give you increased read performance, but not write performance. Of course, you'll have less disk space, but that isn't usually a problem.

Either way, separate partition for OS and data.

Answer 4

OS and Log File on primary Disk. RAID 1 or RAID 10 Database on a separate Hard Disk. RAID 10.

If you can't afford all that then drop the RAID on the primary HD which has the OS on it.

If you can afford more, then get ANOTHER RAID 10 disk and put the log files onto that instead.

The idea here is to have the Database (.mdf) on a separate DISK to the Log files .. and even the OS (and swapd disk).

Install the SQL Server (application/software) to the SAME disk where the OS is installed (eg. C:\ drive).

Answer 5

Ideally you'd want:

• to avoid disk contention, put data (incl. index) files on their own disk (preferrably RAID 10 or 5)
• put transaction log files on a different disk drive from both the OS and the data (also RAID 10 or 5)
• another RAID 10 or 5 array to house tempdb databases

At a minimum I mirror (RAID 1) the OS.

There's nothing wrong with installing SQL Server on the OS drive - it's the data & transaction logs that matter.

With only two drives, you can only do RAID 0 (striping) or RAID 1 (mirroring). In either case, all your data (OS, database data, transaction logs) will be on a single RAID array because at minimum it takes two (2) drives to make a RAID array. Mind that striping increases read speeds only - write speeds increase because you are splitting the data between two drives. Mirroring is just a copy in case of hard disk failure.

That said, I don't think it's worth it to setup RAID for the given task unless you can get more hard drives. You'd do just as well to install the OS on the first disk, and the data & transaction logs to the other drive. RAID should never be trusted; setup a schedule for backups (full and transaction log) and periodically test the backups.