Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  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 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?

share|improve this question
    
Try to determine the number of I/O operations per second (IOps) you need. The average enterprise class 15k SAS drive is very capable of 100+ IOps, when combining 6 drives into a single RAID 10 array, you can expect around 300-400 sustainable IOps. Without knowing the IOps required for your db, it is difficult to make specific suggestions. –  Max Vernon Feb 10 at 5:00
    
What capacity are the drives in this system? –  Max Vernon Feb 10 at 5:02
    
Out if curiosity how much RAM does the system have, and is the OS and SQL Server 64 bit? –  Max Vernon Feb 10 at 5:03
    
@Max 72GB 10k scsi –  RandyMorris Feb 10 at 7:38
    
@Max 64GB Windows 2008R2 64 bit. –  RandyMorris Feb 10 at 7:39
add comment

3 Answers

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.

share|improve this answer
    
Upon 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. –  Anti-weakpasswords Feb 11 at 2:03
add comment

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).
share|improve this answer
5  
Do you happen to know what SAS stands for? –  jcolebrand Feb 10 at 1:17
    
I apologize, I had made some mistakes describing the situation. It is SAS, not scsi and Raid 1. –  RandyMorris Feb 10 at 7:42
1  
Yes. But if someone says "SCSI drives" then I assume he means that. YOu know. SCSI like SCSI bus with terminator. Do you know what SCSI stands for? –  TomTom Feb 10 at 7:47
    
@RandyMorris please update the question with this information –  Jack Douglas Feb 10 at 15:43
    
@TomTom I'm confused if you mean like Ultra320 SCSI or iSCSI or something else. There's actually lots of SCSI implementations, as SCSI is a type of storage subsystem communication protocol. –  jcolebrand Feb 10 at 21:06
add comment

There is 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 SQLos on their own drive and it is mirrored. - that's 2 drives I like for the system databases, 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.