Configuring raid read and write policy for sql server 08

phoeneous

Just got a new box that I need to setup as a database server. It has 2 xeon 5500's, 6 600GB SAS2 drives, and 64GB ddr3 registered. Plenty of power for one database. Since it is a dell server, I'm using the old school openmanage server builder disc to configure the raid and install the os.

Here is how I plan to setup the discs:

2x600 in Raid 1 for OS and logs
4x600 in Raid 10 for Data

My question is, how should I setup the read and write policies for the raid? It is a perc6 controller so I have the read options of default, read ahead, adaptive read ahead, and no read ahead. For the write options I have default, write back, and write through. Based on documentation that I have read, I think I should use adaptive read ahead and write back. Thoughts?

Documentation

Tackle

I just set up one like that with perc6, not quite as high powered though.

Your raid is good (sometimes I debate between 5 and 10 for data, but learn towards 10).

I normally use:
Adaptive, 64kb stripe (for SQL 2k8 ), write back.

I use adaptive because it seems logical to use it, 64kb stripe because I had read it was recommended by Microsoft for 2k8, and Write back because of speed (Writing to ram vs disk).

There are also power options in the BIOS depending on what OS your planning on running (Windows Server or ESX).

I would guess you use SQL quite a bit? You have no idea how many customers I deal with that do not know to put the log/data on different arrays.

Krunchi

Read ahead and Write through is the recommended way.

phoeneous

Tackle wrote: »

I would guess you use SQL quite a bit? You have no idea how many customers I deal with that do not know to put the log/data on different arrays.

I know enough to get myself into trouble

I typically do more t-sql and report writing than i do administration but i do like learning the inner workings of sql server.

phoeneous

Krunchi wrote: »

Read ahead and Write through is the recommended way.

Thanks! OS is installed, will finish up tomorrow.

phoeneous

So I finally have time to install sql today. I'm at the point of choosing the data directories.

Here is how I have local storage setup:

C: - 2x600 in Raid 1 for OS and logs
E: - 4x600 in Raid 10 for Data

This is actually my first sql server install so I'm not sure which drives to choose for the data directories. See attached. Thoughts?

phoeneous

So no need to specify them during install? Makes sense since more storage can be added at a later time.

Tackle

If you're still sitting there, go back a step, then forward and accept the default paths. You can change them once you have it installed.

This is picture from SQL Management studio. (Connect to the SQL Instance, then right click and go to properties). You'll need to restart the SQL service after you make a change in here. Also check the Memory, SQL doesn't put a limit on it, you have to manually do it.



Edit: Removed personal info.

Tackle

phoeneous wrote: »

So no need to specify them during install? Makes sense since more storage can be added at a later time.

That's correct. I've never used the tab you were in before. I accept the default, then make changes once it's installed. It will then seperate all .mdf's and .ldf's. Do this before you add any data.

Also, make sure you're truncating the transaction logs on a regular basis. (Or using a SQL backup agent that does it automatically). Over time the log files grow and can become huge if not dealt with.

phoeneous

Thanks for the help!

Tackle wrote: »

That's correct. I've never used the tab you were in before. I accept the default, then make changes once it's installed. It will then seperate all .mdf's and .ldf's. Do this before you add any data.

Also, make sure you're truncating the transaction logs on a regular basis. (Or using a SQL backup agent that does it automatically). Over time the log files grow and can become huge if not dealt with.

N2IT

This thread has been informative. I've never set up a box that powerful, thanks for sharing. Only SQL configurations I set up was on machines set up on workgroups for biologist out in the open, on 980 GX with raid 1.

I learned a lot from this thread thanks!

This is probably going to be a stupid question, but are you setting the Raid at the hardward level or at the OS level?

Since my set ups were small compared to yours I would just use the raid controller on the box itself. But running two different raids I am curious.

RobertKaucher

Tackle wrote: »

That's correct. I've never used the tab you were in before. I accept the default, then make changes once it's installed. It will then seperate all .mdf's and .ldf's. Do this before you add any data.

This is a very informative thread.

I just wanted to point out that the sentence I but in bold here is a very good best practice for anything you are doing. The reason being if everything is working properly with the default configuration you will find it much easier to troubleshoot problems once you customize things if stuff goes wrong.

The purpose of any wizard, IMO, is just to get the bits put down. If you can customize things once the system is running, it's best to do it then.

phoeneous

N2IT wrote: »

This thread has been informative. I've never set up a box that powerful, thanks for sharing. Only SQL configurations I set up was on machines set up on workgroups for biologist out in the open, on 980 GX with raid 1. I learned a lot from this thread thanks!This is probably going to be a stupid question, but are you setting the Raid at the hardward level or at the OS level? Since my set ups were small compared to yours I would just use the raid controller on the box itself. But running two different raids I am curious.

It's done at the hardware level. Dell has several utilities to configure the perc6 controller prior to even installing the os.

It looks like this:

N2IT

Very cool

Thanks for sharing the configuration.

phoeneous

N2IT wrote: »

Very cool

Thanks for sharing the configuration.

Sure, but that one isn't mine

N2IT

Well I figured with geekstuff at the top, but still......