DiogoGoulao
Contributor
Contributor

Raid configuration SQL 2008 R2

Jump to solution

Ho everyone.

I´m planning installing a Esxi 5 in a HP DL380 G7 with 6 x SAS 15rpm 146 GB Disks . The raid controller is P410i with 512 cache with Battery Pack.

This server will have 2 VM.

1 VM as DC, DNS, DHCP and file share. The second VM will have SQL 2008 R2 Standard. The actual size of the DB is 29 GB and is expected to grow 4 GB per year.

My scenarios for Raid configuration is:

1 - Raid 1 for both OS VM´s and RAID 10 for SQL Database.

2 - 3 x Raid 1. One Raid 1 for each OS Vm and one Raid 1 for SQL Database.

3 - Raid 1 for OS of DC VM and Raid 10 for SQL VM (SO+Database Volume).

What is this scenario for achieving the best performance of SQL Server ?

The VM SQL will have 10 MB of memory.

Thanks in advanced.

Tags (4)
0 Kudos
1 Solution

Accepted Solutions
DavidKlee
Enthusiast
Enthusiast

For the PVSCSI driver, take a look at splitting the workloads up first. You'll have multiple VMDK hard drive files. Each one of them is assigned a virtual SCSI controller ID. The first drive will have the ID of 0:0. The second should have 0:1.

Take the second drive, and change it to ID 1:0. This creates a new virtual SCSI controller. You'll see it in the list after you hit OK to make this ID change. Select it, and on the upper right hit change type. Select Paravirtual. You've just converted your SCSI controller to one that is better optimized for performance!

Do this after you install the VMware Tools (prereq) but before you install SQL Server, if possible. It makes a possible drive letter change from the reassignment easier to do.

Convert all non C: drives to IDs 1:0, 1:1, etc. until you've mapped them all. You're done!

For the controller cache, I recommend all of the cache you can possibly max it out at being installed before you put that server into production. It's a cheap way to gain a decent bit of performance.

Sent from my iPad

David Klee | Founder and Chief Architect | Heraflux Technologies | dklee@heraflux.com

View solution in original post

0 Kudos
7 Replies
DiogoGoulao
Contributor
Contributor

By the way....

SQL will have 35 Users where 25 will always connect ....

Thanks in advanced

Diogo  

0 Kudos
sergeadam
Enthusiast
Enthusiast

Since IOPS are cummulative per spindle and unless each pair is on a separate controller, your bottleneck would be the controller anyways so I'd go single RAID10 with all spindles. Maximizes your spindles, does not waste space.

DavidKlee
Enthusiast
Enthusiast

I definitely agree. Maximize the space available AND the performance with one big RAID-10 array. Do you have options to boost the cache amount on the SAS controller?

Split the OS, SQL Server installation, data files, log files, and tempdb files into separate VMDK files and logical drives for performance and flexibility. Use the Paravirtual (PVSCSI) driver for all drives but the C: (OS) drive because it gets you performance improvements.

David Klee | Founder and Chief Architect | Heraflux Technologies | dklee@heraflux.com
DiogoGoulao
Contributor
Contributor

Thanks for your replies.

Sergeadam, the controller has 512 MB with battery cache and is already a upgrade. The maximum is 1 GB.

DavidKlee, should i use PVSCSI for all drives exept C:(OS). Is this waht you mean ?

0 Kudos
DavidKlee
Enthusiast
Enthusiast

For the PVSCSI driver, take a look at splitting the workloads up first. You'll have multiple VMDK hard drive files. Each one of them is assigned a virtual SCSI controller ID. The first drive will have the ID of 0:0. The second should have 0:1.

Take the second drive, and change it to ID 1:0. This creates a new virtual SCSI controller. You'll see it in the list after you hit OK to make this ID change. Select it, and on the upper right hit change type. Select Paravirtual. You've just converted your SCSI controller to one that is better optimized for performance!

Do this after you install the VMware Tools (prereq) but before you install SQL Server, if possible. It makes a possible drive letter change from the reassignment easier to do.

Convert all non C: drives to IDs 1:0, 1:1, etc. until you've mapped them all. You're done!

For the controller cache, I recommend all of the cache you can possibly max it out at being installed before you put that server into production. It's a cheap way to gain a decent bit of performance.

Sent from my iPad

David Klee | Founder and Chief Architect | Heraflux Technologies | dklee@heraflux.com
0 Kudos
Josh26
Virtuoso
Virtuoso

DiogoGoulao wrote:

By the way....

SQL will have 35 Users where 25 will always connect ....

Thanks in advanced

Diogo

That's an incredible small number of users for the configuration you're talking about.

We've got one RAID5 LUN running over 500 users.

People obsess around major RAID configuration changes for small performance increases but it usually comes down to the quality of the software.

Edit: Your database sizing estimate is tiny. I really wouldn't stress about it.

0 Kudos
DiogoGoulao
Contributor
Contributor

Thank´s for the replies..

This is somethings interesting to investigate.

Thank´s again.

0 Kudos