Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

Question

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:

1. OS
2. SQL Transaction Log
3. Largest database tables (take up about 30GB) in separate filegroups
4. 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?

Answer 1

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.

Answer 2

The new server has 8 scsi drives

Are you sure?

SCSI is something I have not seen in new servers for like 10 years. SATA, SAS - but SCSI is really not something I would buy today. If I can buy it. Outside of eBay and Craigslist.

I made the suggestion of having the 4 drives become 4 raid 0 partitions each on its own scsi controller,

That is like saying "I want a 8 cylinder engine, so I put in 8 engines with 1 cylinder each".

Also, if that is a server that has to work - then a Raid 0 is like driving a car and before that sabotaging the brakes to make sure that you have an accident. Raid 0 is fast, and is MADE to lose data if one of the discs fails.

but then scripts that normally run 30 seconds would run in less than a second

It may be fast, but that maybe the memory caching kicking in.

Is my idea a bad one?

It is bad. SCSI, multiple controllers and Raid 0 - each of those items are on a level that makes no sense.

I checked my preferred supplier and I could not even find SCSI in the list of available drive technologies anymore. Seems no-one is selling SCSI drives.

For a 75gb database:

• Forget all fancy multi drive setups. Go Raid 1. Mirror.
• Take 2 decent quality SSD.
• Depending on mobo use mobo raid or get a lower end hardware raid controller. If you do that - get a REAL one, not a fakeraid. I personally use Adaptec.... with problems ;(

Finished.

That runs circles in speed even around a 16 disc Raid 10 SAS setup with 15k RPM Discs and will cost a really small amount. Then put that money saved into buying a server that has a decent amount of memory (say: 64gb) and the SSD will not even be touched most of the time with your database size.

Use 2 partitions - operating system, data (and possibly log files) and you get a lot more. If you want more security, add another 2 SSD and mirror those and put the log files there.

But stay away from fancy setups:

• SCSI: Dead and outdated.
• 4 raid controllers: Really expensive, for not being used.
• Raid 0: Ouch. ONE disc fails, and your server is down, data lost (i.e. go back to backups).

Answer 3

There are best practices for this sort of thing and RAID 10 with a single controller is going to be a performance limiting configuration. The more you separate with SQL Server, the better.

Ideally:

• The OS is together with the SQL binaries on their own drive and it is mirrored - that's 2 drives
• For the system databases, I like tempdb and user databases to each get their own drive with RAID 5 or 10 but seeing how you don't have the luxury of the number of disks required to implement this, then lump all of your databases together onto a drive - that's a minimum of 3 drives for RAID 5.
• The user database transaction log gets its own drive with some form of RAID - 2 for RAID 1, 3 for RAID 5.

The most important thing to not compromise on is the fact that the transaction log for your production database is not on the same drive as the main database file (.mdf) of your production database.

Right now you are laying the foundation for the future performance of this SQL Server. If you haven't already ordered this server see if you can you get one with more drives. Note that you will get better performance out of several smaller drives than a few big ones.

Another thing to consider is the anticipated growth of this database over the next two years. If the company has big, big plans and marketing is planning big promotions, it is much easier to scale up when the foundation is prepared for it.

Consider, too, disaster recovery. By using all 8 drives this way, you have no room for a hot spare. If you have a drive go out, you better have a replacement drive sitting on a shelf in your computer room so you can replace it right away. If another drive fails while the replacement drive is in transit, well, you better have a good backup strategy in place.