I am a software developer for a small company. I wish we had a dba but not in the cards at this point for this company. I was working on migrating our 75gb SQL Server 2000 database to SQL Server 2008 r2.
The new (Windows 2008R2 )server has 64GB RAM and 8 72GB 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:
- 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 a 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?