I am a software developer for a small company. I wish we had a dba but that is not on the cards at this point. I was working on migrating our 75GB SQL Server 2000 database to SQL Server 2008 R2.
The new (Windows 2008 R2) server has 64GB RAM and 8 x 72GB 10k SAS drives. I made the suggestion of configuring the 8 drives into 4 RAID 1 drives, each with a single partition and separating out the data as follows:
- OS
- SQL Transaction Log
- Largest database tables (take up about 30GB) in separate filegroups
- The remainder of the database in a separate filegroup.
Our network guy said this would not work and that using RAID 10 would work best.
I had set up a test computer to test my suggestion. I tested several scripts to test the performance of significant read and write operations. The test system would be slow the first few executions, but then scripts that normally run 30 seconds would run in less than a second.
The network guy had also had me talk with a dba friend of his who also suggested 1 large RAID 10 who said there would be I/O issues using RAID 0 and having separate filegroups on separate partition.
We have now been using the new server on RAID 10 for over a year now and run fine, but I always feel like we might be missing out on some performance using what seems like a very basic configuration.
Was my idea a bad one? Are there best practices for this sort of thing?
Best Answer
I'm going to assume that, as TomTom went through in detail, some of your hardware acronyms are incorrect regarding your actual server (which you apparently already have).
I'm also assuming that your reference to a "75GB database" indicates you have one database of 75GB that's your primary DB. This should have only one log file.
Assuming you have 8 disks on modern hardware, one generalized maximum performance setup would be: 2 drives in RAID1 (mirroring) for OS 4 drives in RAID5 for all data files and most log files other than the primary DB's log file. 2 drives in RAID1 (mirroring) for your primary DB's log file.
RAID5 is surprisingly fast on modern hardware, and 4 disks in RAID5 gives 3 disks worth of storage.
The primary DB's log file is by itself so that it really is sequential access - it should be the only file on that entire raidset. If you had many log files, the drive heads would have to jump from one to the other, which is not sequential.
On reading the additional information in the updated question: a 72GB SAS drive clearly cannot hold a 75GB database even without accounting for growth. Either a 3 or 4 drive RAID5 or a 4 drive RAID 10 is the bare minimum, given the new hardware information. For any one specific case, try a few configurations and benchmark them - but with 64GB of RAM on a 75GB database, I'd expect almost the entire database to be in buffer, rendering disk performance much less important; write performance for the transaction log will be one of the first things to watch.