[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1031
  • Last Modified:

RAID config options for SQL Server 2008

I'm setting up a new database server.

It's running Win 2K8 Standard (SP2) with MS SQL 2008 (64-bit) Standard (SP1)

The E: drive (where the Database will reside), is a RAID5 array with 10 - 146 GB 15k rpm SAS drives.

My question is regarding the specific RAID settings. The RAID controller allows me to configure these options:

- Read Policy: No Read Ahead / Adaptive Read Ahead / Always Read Ahead

- Write Policy: Write Through / Always Write Back

- IO Policy: Direct IO / Cached IO

- Disk Cache Policy: Enabled / Disabled

Can someone comment on those options? When would it be beneficial to choose one setting as opposed to another?

Thanks!
0
IT_Service
Asked:
IT_Service
  • 4
  • 2
1 Solution
 
dicconbCommented:
The ideal settings depend on the type of IO workload your database will generate, its size, and your attitude towards risk.

- Read Policy: No Read Ahead / Adaptive Read Ahead / Always Read Ahead
If your database contains large blocks of data (eg images stored as BLOBs like in a Sharepoint content database) select Adaptive Read Ahead, otherwise choose No Read Ahead.  Read ahead gives better performance if you're reading large chunks of sequential data, because the array predicts the data you're going to read next and gets it ready in cache before SQL even requests it.  WIth smaller chunks of data the requests are more random and harder to predict, so the array wastes cache pre-staging data you don't need.

- Write Policy: Write Through / Always Write Back
Write-back allows faster write performance, but involves a small risk of data corruption in a power failure.  Write through reduces write performance but is more stable.  Choose write-back if you have a battery-backed RAID controller and a stable data center, and want to get the maximum possible performance.  Choose Write Through if you aren't absolutely confident about your power supply, or if the integrity of the database is mission-critical.

- IO Policy: Direct IO / Cached IO
For small databases (<4GB) choose Cached IO, ortherwise Direct IO.

- Disk Cache Policy: Enabled / Disabled
The disks in your array have their own cache, as well as the RAID controller.  If you have a battery-backed RAID controller make sure Disk cache is disabled, so writes are only cached on the RAID controller where it is protected by the battery.  If your RAID controller isn't battery-backed or if data integrity isn't an issue, you may find a performance increase if you enable disk caching.  Use with caution!

Let me know if you have any questions!

D
0
 
IT_ServiceAuthor Commented:
That's great - thanks. Exactly what I was looking for.
0
 
dicconbCommented:
You're welcome! Thanks for the points. Cheers,

D
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
dicconbCommented:
You're welcome! Thanks for the points. Cheers,

D
0
 
MrVaultCommented:
Follow up question if you're game dicconb:

1. on the read ahead option, is adaptive read ahead safe since it will only read ahead if recent data is sequential? or is it possible a small transaction db (OLTP) will still suffer?

2. we have battery backed raid controllers, but no UPS in our racks. the datacenter has generator backed power and tons of customers, so I'm guessing the IT guy before me figured no need to invest in UPS. right now there are no funds to do so. my question is this:

a. even if the raid controller has a battery backup, what keeps all the drives spinning in a power failure situation? we have a dell r510 with 12 sas 15k disks and 2 internal sas 10k drives.

b. what exactly happens that tells the server "power is gone or about to be gone. write all cache to disk and stop accepting more data to the cache."

3. what does enabling disk cache actually do? does it send some command to enable disk cache in windows because there's a disk cache setting for each drive in windows. or is that different?
0
 
dicconbCommented:
Hi Mr Vault,

Since it's just a quick one!

1.  The effectiveness of adaptive read ahead algorithms will vary between manufacturers, but it's safe to say that unless the objects in your database are considerably larger than the database page size (6Kb by default in SQL server) you won't get any benefit from read-ahead.  Personally I would keep it switched off for this sort of workload.

2.a The answer is that the disks will all stop working if the server loses power, however the write cache on the RAID controller will be kept alive by the battery.  When your generator kicks in and power returns to the server, the RAID controller processes the remaining writes in the cache.  So although it won't keep your server running, the battery will prevent write operations being lost.

2.b The server has no way of knowing that it's about to lose power. The process of writing all the cache to disk doesn't happen until the server comes back online, so you're relying on the battery to keep the write cache alive until power is restored.  If the server is without power for so long that the RAID controller battery runs out, the write cache dies and your data is potentially corrupted.  I suggest checking with Dell to see how long your RAID controller battery is designed to last if you're worried there might be extended outages.

3.  Disk cache is a small buffer of RAM built into each physical disk drive unit, but it never has battery backup.  It performs the same task as the write cache on the RAID controller, so if you already have write cache enabled on the RAID controller you will see very little performance increase by enabling disk cache.  As I said in 2a if the server loses power, all of your disks lose power too.  If disk cache is enabled there may be writes cached on the individual disks - as the disks don't have their own backup batteries these writes will be lost, potentially causing data loss or corruption.  "Disk" caching in Windows is where write operations are stored in your server's RAM before Windows submits them to the RAID controller.  This happens in a very different place, but it's just as vulnerable to data corruption in a power failure as your server's RAM is also not protected by a battery.

Hope that explains everything - if you need any more help I suggest opening a fresh question so the whole community can get involved.  Feel free to post a link to the new question here so I'll be notified, and I'll take a look at it for you.

Cheers,

D
0
 
MrVaultCommented:
Thanks dicconb. That was a tremendous help. the only followup question I have is how to determine the database page size. I wouldn't put it past our last admin to have changed this.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now