SQL Server 2008 R2 Storage Config

[MSS17]

I am setting up a new SQL Server for 15 user accounting system Dell PE R310 with 4 250GB SATA (dual raid 1). The C drive (80GB) has the OS and SQL program installed on it, the remaining space is the E drive 149GB, and the D drive 232GB is unused. My question is where should the tempdb and templog files go, as well as the user database and transactions logs? Thanks

 

[intelwizrd]

Best configuration is this:
OS + SQL Program files on drive set 1
PageFile on drive set 2
Tempdb on drive set 3
User DBs on drive set 4
User Trans Logs on drive set 5

Note that these are separate drive groups not separate partitions on the same drives. If the logical drives share the same physical drives they you dont get any performance gain by splitting the data between logical drives. Problem with this is the cost. Assuming at least Raid1 for everything just for redundancy sake you are looking at 10 physical drives.

In your case, I would say to put the tempdb and log on e and the rest of the databases and logs on D. I dont know that you will get any performance boost from it though. Your best bet is to load the system with as much RAM as you can afford or it will take.

 

[MSS17]

Thanks for the response, just to clarify should the user database transaction log file go on the D drive with the user database file or should it be on the E drive.

 

[intelwizrd]

In your case, I think they would be fine on the D drive. Ideally, the database files and log files would be split onto separate arrays with the db files on an array that is better suited for reading and the transaction logs on an array better suited for writing. In your case, you don't have many options. I have an accounting server with all of the sql db files (temp, system dbs and user dbs) on the same drive array (Raid1 I think) and the system and program files on another. It seems to work fine for them (about 10 users). They didn't want to spend a lot of money on the hardware and the transaction volume is fairly low. If the system has downtime in the evening, you can always try it one way and if needed, move the databases files to a different drive (multiple methods to do this, fastest would probably a simple detach, move the files and then reattach the database).

I have another server where I have it set up in the ideal configuration but that involves a SAN and it was quite expensive. It also supports Web applications and has an average of +2000 batch requests per second.