Help with SQL Server Log, Master and TempDB locations on specific array config

Question

I have a quick question that is specific to the server setup we have available to us.

We are getting ready to migrate our help desk to a new server, as well as convert a small ACT DB to the full version of SQL 2005 Standard (from SQL Expr.)

Right now, we only have the following resources available to us as far as array configurations go.

It is server 2008 64 standard, and we will be using SQL 2005 Standard 64.

2 drives in raid 1+0 for the OS (1) 3 drives in raid 5, (2) and 3 additional drives to allocate out for additional resources. (3)

My initial plans were to install ACT, our Help Desk and the SQL Program files and transaction log files on (2), and use (3) in raid 0 for the tempDB.

The DB sizes are very small, and honestly we could probably run everything on the first 2 arrarys with minimal performance loss (just because the DB are so small)

However we may decide to dedicate this server to SQL somewhere down the line, moving many more DB's over to it, and remove the help desk (web front end) to another server.

1. How intensive are the log file write operations for 2 small (<500MB) db's?
2. How risky is putting the TempDB on a raid 0?
3. Would moving the log files to the system array (1) improve performance?

Answer 1

With 8 disks available I'd recommend the following independent RAID arrays:

OS: RAID 1 (2 disks) (you specified RAID 10 in your question - you can't do RAID 10 with only two drives).

Database data files (including TempDB data file and log file): RAID 5 (4 disks).

Database log files: RAID 1 (2 disks).

If you think the usage of your databases will increase or you're planning to consolidate further databases you may also consider adding further disks to split out your TempDB database.

If disk space capacity isn't an issue you could consider changing the 4 disk RAID 5 array to RAID 10 which would be more performant (particularly for writes).

How are you planning to do backups?

1. How intensive are the log file write operations for 2 small (<500MB) db's?

This depends on what your application is doing and the number of concurrent connections.

2. How risky is putting the TempDB on a raid 0?

Putting TempDB on RAID 0 is risky, if you lose the TempDB database because one of the disks fails your SQL instance will stop.

3. Would moving the log files to the system array (1) improve performance?

Yes, but putting them on their own independent array would be more performant and resilient.

You should really ask this question on serverfault.com (its content is skewed towards administration rather than programming).