Disk configuration for small SQL server database

Posted on 2008-02-01
Medium Priority
Last Modified: 2010-04-21
My company is in the process of installing a new SQL server which will support 30-40 users during the workday.  The server will run two databases, which are estimated to grow to a total of 50GB after 5 years.  I have six 146GB SAS drives to work with.

Everything I've read indicates that, with the disks available,  I should go with a Raid 1 for the OS and SQL install and a RAID 10 for the data and logs.  

If there will be far more disk activity on the RAID 10 array, would I be better off going with a single 6 disk RAID 10.  A 6 disk RAID 10 will outperform the 4 disk RAID 10, correct?
Question by:Eric_PSU
LVL 60

Expert Comment

ID: 20798250
You really don't need RAID 10 for the logs...RAID 0 (striping) should be OK for you since the log files are written synchornously.  You won't have the fault tolerance as you would with a 10, but in almost all instances I"ve seen it is OK.
LVL 29

Accepted Solution

Nightman earned 1600 total points
ID: 20798403
Before you consider anything without fault tolerance, consider the cost to the compnay if this server is down. How long can you afford downtime while rebuilding? Can you afford to wait for a new drive?

I suspect not. RAID1 should be fine (although I would prefer RAID10 myself). A 6 disk RAID10 *should* outperform 4 disks (more spindles = better performance), but it depends on your usage patterns. If your logical drives for logs and data are on the same physical disk array you may not even notice the difference.

Where possible (although it looks like you might battle) isolate the log files to seperate *physical* disks (i.e. their own raid containter). For optimal performance each database would have it's own phsyical RAID container for logs AND data, (and if you have large tables you could even consider creating filegroups on seperate physical disks for these tables alone). TempDB should have it's OWN dedicated physical drive.

Of course, all of this is ideal if you have the space (SAN, iSCSI, etc), but you need to look at your load and performance requirements before you can determine what will be acceptable - not just number of users, but the frequency, size and complexity of the reports that they run, mixed OLTP load vs OLAP, etc

In essence, the answer is "how long is a piece of string". Hope this helps ...

Assisted Solution

fbcbloodcenter earned 400 total points
ID: 20798437
Yes, generally speaking, the more spindles you have spinning the faster your IO performance. When dealing with a SQL DB, IO performance is usually the most critical factor. I would recommend using all 6 disks.
LVL 19

Expert Comment

ID: 20798734
Everything I have read suggests you would see a larger boost in performance moving the indexes to a separate physical unit, as opposed to separating the databases.  To do that use the Data Space Specification to locate the index in a different file group.  Since the logs are synchronous, they don't benefit from the more expensive raid configurations, so you can save a bit by moving them to simpler units.

For your type of install, so much depends on the type of data and database design.  If 90% of the data accesses are hitting the most recently written data, and you have lots of available cache, the drive configuration is going to be insignificant.

Author Closing Comment

ID: 31427196
Thanks for your response.

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question