adenton
Contributor
Contributor

SQL 2005 VM

I have been fighting with wether or not to virtualize my dedicated SQL 2005 server box. I have been doing tones of research and just cant seem to pull the trigger on this one. I have 47 Databases mostly used by Microsoft Dynamics and Websense with a normal load of about 30-35 users. The physical hardware is a Dell 6800 with 2 Dual core 3gz processors, 20gb of memory and Windows Server 2003 Enterprise. SQL is currently utilizing 13gb of the 20. Our virtual environment is 3 ESX 3.5 Dell 2950 quad core and 32gb of memory each. We are currently running 10 production VM's on these 3. The data storage is an ISCSI san. After looking at several white papers and searching the forums it seems like it would be ok but was wondering if anyone had any recommendation of things to be sure of before I bite the bullet. I believe the overall CPU usage is fairly low but the memory concerns me a bit.

Thanks for any advice,

0 Kudos
11 Replies
Dave_Mishchenko
Immortal
Immortal

Welcome to the VMware Community forums. Your post has been moved to the Performance forum. I've includes some threads to look at below. A few of the threads relate to problems with SQL in a VM, but overall there are many more cases of people running SQL in a VM without an problems. ESX can handle the I/O load and your memory shouldn't be an issue. SQL Server will use all it can get, so it your case the 13 GB isn't necessarily reason for concern.

How large are these databases that you have and what sort of I/O load to them impose on the current host. That would be a bigger concern that CPU and memory. For example, if the SQL box has 20 - 15 K drives and is pushing a lot of I/O, then you could end up with performance issues if your iSCSI SAN has 20 drives as well and the other 10 VMs are imposing a heavy I/O load already.

Dave Mishchenko

VMware Communities User Moderator

http://communities.vmware.com/click.jspa?searchID=2640763&objectType=2&objectID=959011

http://communities.vmware.com/thread/151013?tstart=0

http://communities.vmware.com/docs/DOC-5082

0 Kudos
adenton
Contributor
Contributor

The total space taken by all databases is 68gb. The Largest DB is 28gb but the 4 most used DB's are less than 1gb a piece. The current drives are 8 10k 320 drives. The san has 15k SAS drives. Are there any paticular perfmon stats other than avg disk reads/writes that I should look at?

Thanks,

0 Kudos
Funtoosh
Enthusiast
Enthusiast

First thing I would do to virtualized any database would be , align the drives with 64K size according the VMWare document(I don't remember but I read somewhere). Yes how do I do that?

  1. Use diskpart

  2. Select the disk you want to create partition with 64K size and run the command

Create partition primary align=64

PS: Make sure your drive is unformatted or else this command will not work.

0 Kudos
adenton
Contributor
Contributor

I did some calcs using a script I had. Based on these numbers can anyone provide any suggestions.

-


TOTAL I/O ANALYSIS FOR TIME RANGE "DAY": 00:00 - 24:00 hours for 1.01 days

-


Read IOs: 1340715.43 Write IOs: 833763.86

KB read: 116193338.16 KB written: 41854965.54

Peak read IOs/sec: 1842.14 Peak write IOs/sec: 935.08

Peak KB/sec read: 100305.80 Peak KB/sec write: 59477.35

Average read IOs/sec: 15.41 Average write IOs/sec: 9.58

Average KB/sec read: 1335.31 Average KB/sec written: 481.00

Average Resp Time/msec: 2.64 Peak Resp Time/Msec: 873.94

********************************************************************************

-


Thu Jun 12 16:51:29 2008 EVALUATION BASED ON I/O DISTRIBUTION RANGES

-


Percentage of Read IO/sec range for 87017 lines

85740 Hits for range 0.00 - 460.53: 98.53 %

1201 Hits for range 460.53 - 921.07: 1.38 %

72 Hits for range 921.07 - 1381.60: 0.08 %

4 Hits for range 1381.60 - 1842.14: 0.00 %

Percentage of Write IO/sec range for 87017 lines

86948 Hits for range 0.00 - 233.77: 99.92 %

46 Hits for range 233.77 - 467.54: 0.05 %

21 Hits for range 467.54 - 701.31: 0.02 %

2 Hits for range 701.31 - 935.08: 0.00 %

Percentage of Read KB/sec I/O range for 87017 lines

84631 Hits for range 0.00 - 25076.45: 97.26 %

1863 Hits for range 25076.45 - 50152.90: 2.14 %

516 Hits for range 50152.90 - 75229.35: 0.59 %

7 Hits for range 75229.35 - 100305.80: 0.01 %

Percentage of Write KB/sec I/O range for 87017 lines

85611 Hits for range 0.00 - 14869.34: 98.38 %

1119 Hits for range 14869.34 - 29738.67: 1.29 %

286 Hits for range 29738.67 - 44608.01: 0.33 %

1 Hits for range 44608.01 - 59477.35: 0.00 %

0 Kudos
Funtoosh
Enthusiast
Enthusiast

I am not 100% sure with numbers but looks like SQL is doing heavy write and less read compared to write. I would like to see your offset number. Go to command prompt and run msinfo32 and let me know what is the offset value as shown in the screenshot.

0 Kudos
adenton
Contributor
Contributor

The Offset is 32KB.

0 Kudos
Funtoosh
Enthusiast
Enthusiast

Yes NTFS file system is not aligned with your VMFS and that makes a lot of difference with the performance.

Here is what I will do:

  1. I will backup every thing of the drives

  2. Then perform the disk alignment as I suggested in my previous post.

  3. Perform the restore of the system.

  4. Rerun the I/O test which you have shared.

You will find the difference yourself.

0 Kudos
adenton
Contributor
Contributor

I guess I should state that this server hasn't been virtualized yet. The numbers and the offset are from the physical dedicated server it is still on. I'm just trying to make sure that I won't have a huge performance problem when I do virtualize it. I will for sure look at the offset though when I move it over.

0 Kudos
Funtoosh
Enthusiast
Enthusiast

I am sorry I misunderstood the original question. Yes please do so and check the offset value before you run the test. But your I/O value which you shared does not seems to be a big challenge for virtualization. I have never run I/O meter before P2V but I used HP OVO chart to look at the trends and based on that I performed P2V. More over I got TAM sponsored Capacity planner to choose suitable candidate which makes my life easier. I have experience in vitalizing Siebel TNC which is one of the notorious I/O applications I have ever virtualized. I would say give it a try since you are not going to loose anything if it fails. You always have option to turn on your physical box.

0 Kudos
adenton
Contributor
Contributor

Thank you for your help, I will give it a go and see what happens. Can you provide a info to the TAM sponsored Capacity planner you spoke of?

Thanks again!

0 Kudos
Funtoosh
Enthusiast
Enthusiast

Sure TAM stands for Technical Account Manager who manages big enterprise customer and provide these kind of service to their customer to keep them happy for free. This is called VMWare Professional Service which will cost you a lot if your choose at your own. Check this link for more information

0 Kudos