Solved

Recommended setup for 14 drive SQL server

Posted on 2010-11-10
9
301 Views
Last Modified: 2012-05-10
There are a ton of best practices out there for setting up database servers. We have 14 drive bays on a new server we ordered. We're trying to determine the best setup for the drives. All are 15K SAS drives. The server has 2 quad core CPUs with 2 threads per core (16 total threads). The server is far heavier on the write than on the read for the database. Basically customers are writing data to it 24/7 with heavier loads at night. They do pull data all the time but not nearly as much. However our database does lots of lookups to determine if it should write more data (it does dedup) or if it can use data it already has.

We planned on putting the OS (including page file) on a RAID 1 set (2 drives). That leaves 12 left. Here are some options I thought of:

A)
RAID 1 set for OS/pagefile
RAID 1 set for tempdb
RAID 10 set (4 drives) for transaction logs
RAID 10 set (6 drives) for database

B)
RAID 1 set for OS/pagefile
RAID 1 set for 1st tempd file
RAID 1 set for 2nd tempdb file
RAID 1 set for transaction logs
RAID 10 set for database (6 disks)

C)
RAID 1 set for OS/pagefile
RAID 1 set for 1st tempd file
RAID 1 set for 2nd tempdb file
RAID 1 set for 3rd tempdb file
RAID 1 set for 4th tempdb file
RAID 10 set for database and transaction logs (4 disks)
0
Comment
Question by:MrVault
  • 6
  • 3
9 Comments
 
LVL 42

Assisted Solution

by:kevinhsieh
kevinhsieh earned 200 total points
ID: 34103878
That's a lot of spindles. There is probably no way to determine the best way without just trying it with your workload. Have you considered SSD? It may be have similar cost to 14 15K SAS drives, and the performance will be better.
0
 

Author Comment

by:MrVault
ID: 34103970
can you expand regarding SSD? are they really the same cost? an overview or link would be great regarding SQL.

also do you know if a modern RAID controller card's performance is affected by RAID stripes that span multiple channels?

So if Channel 1 has 2 drives (RAID 1 set) and then another 2 drives (half of a raid 10 set) and then the next channel on the card has 2 drives (2nd half of RAID 10 set), is that a significant degredation compared to the first channel only having 2 drives and putting all 4 drives in the RAID 10 set on the second channel?


0
 
LVL 42

Assisted Solution

by:kevinhsieh
kevinhsieh earned 200 total points
ID: 34105692
You didn't say how big your SAS drives are, but my guess if that you are looking at $2400-8000 for the hard drives. You get more capacity from hard drives, but if you need performance more than space, SSD really is the way to go.
NewEgg has OCZ Z-Drive R2 M84 OCZSSDPX-ZD2M84512G PCI-E 512GB PCI Express MLC Internal Solid State Drive for $1849.
Look at this review for FusionIO http://www.tomshardware.com/reviews/fusioinio-iodrive-flash,2140.html
0
 

Author Comment

by:MrVault
ID: 34106582
Our drives are 15K 300 GB drives. We have 14 of them in a chassis with 2x 10K 146GB drives for the OS/Pagefile RAID 1 set. Our database volume in a RAID 10 6 drive set gets roughly 850 GB usable. Our database easily grows to 500GB per setup. Sometimes it's gotten over 2 TB in which case we had to temporarily offload it to external storage, clean it up so it's back down to 400 GB and then put it back on.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:MrVault
ID: 34112584
We're pretty set on using 6 drives for a RAID 10 array for the databases. The question is how to break up the indexes, tempdb, and transaction logs. There are 6 drives left for this:

RAID 1 - tempdb
RAID 1 - trans log
RAID 1 - indexes

or

RAID 1 tempdb1
RAID 1 tempdb2
RAID 1 trans log + indexes

when SQL best practices say to have 1 tempdb file per core and we have 2 quad core processors, that means 8 tempdb files. Do they expect/hope people would dedicate 2 whole drives per tempdb file, or just have all 8 on a tempdb RAID 1 set? there's no way we can use 16 drives for the tempdb files.

Our indexes are pretty heavily used so we're debating where to put those too.
0
 
LVL 42

Expert Comment

by:kevinhsieh
ID: 34117024
My though would be to make it 1 large RAID10 array to get as many spindles involved as possible, but I think that you're going to have to experiment. I am sure that the the relative activity between the indices, logs, and tempdb will determine the optimum placement.  If the hard drives aren't fast enough, puting indices on SSD will certainly make a difference.
0
 

Author Comment

by:MrVault
ID: 34219183
while the tempdb is over 12 GB in size (it's a 500GB db) 98% is free space and the ave disk queue length on the drive it's on is under .1 (raid 1 set). Thus I'm guessing now I can put the tempdb on the boot volume. I think we're thinking:

(2) RAID1 C - OS/page/tempdb
(2) RAID1 E - trans logs
(4) RAID10  - table in db with 45% of IO
(4) RAID10  - other table in db with 45% of IO
(2) RAID 1 F - rest of DB tables
0
 

Accepted Solution

by:
MrVault earned 0 total points
ID: 34360460
we decided on this solution:

2 - RAID1 - C/pagefile
2 - RAID1 - tempdb
2 - RAID1 - tlogs
2 - RAID1 - less active part of DB
2 - RAID1 - high IO, small size part of DB
4 - RAID10 - high IO, larger size part of DB
0
 

Author Closing Comment

by:MrVault
ID: 34391615
suggestion was not per best practices and went another direction than requested
0

Featured Post

Too many email signature updates to deal with?

Do you feel like you are taking up all of your time constantly visiting users’ desks to make changes to email signatures? Wish you could manage all signatures from one central location, easily design them and deploy them quickly to users? Well, there is an easy way!

Join & Write a Comment

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now