SQL on 4way server. Max cpus to the VM?

[Shylok]

Our ERP put's a huge demand on SQL and we're upgrading our SQL box to SQL 2008. I was thinking to buy a 4way DL 585 G5/G6 server. Possibly with 6cores per CPU. Though really the main goal of this ESX 4 server would be to run SQL 08 in a VM. I'd like to run a couple light VMs also. My question is, I thought I could dedicate the entire 4processors to the SQL VM. ie 16/24 cores. Is this not possible? To utilize that many cpus would I be forced not to virtualize? Thanks!

[mcowger]

On ESX 4 there is a max of 8 cores assigned to a VM, so you couldn't use all of those CPUs for a single VM.

--Matt

VCP, vExpert, Unix Geek

[Shylok]

So what do people do for large SQL servers?

[y_wisdom]

It all depends on the size of SQL Database you will be running on the VM;

I have several small SQL servers running fine, ( 500 GB) on VMs with 2 vCPU and 4 GB of RAM.

Follow some best practise like,

• seperate LUNs or RDM for each of the partition;

• use seperate Paravirtual scsi controller for each disk;

• Use partition alignment with large blocks

[mcowger]

They use clustered servers, or if thats not possible, they dont virtualize it.

--Matt

VCP, vExpert, Unix Geek

[ChrisJ615]

What's a large SQL Server to you? I find that all of this is really relative at the end of the day. I'm also looking at doing a SQL Server consolidation into one VM with FT. I think this thread could really help me with my implementation decision.

[Odurasler2]

We're in the same boat due to a huge DR initiative with srm/recoverpoint. our physical sql server today is on a 4-quad core cpu with 128GB of RAM. This sql box has multiple databases, but the main one is over 500GB, and we're thinking of virtualizing it. What's the biggest SQL server has anyone virtualized?

[ChrisJ615]

Wow! That is a serious deployment. I'm only looking at doing 2*(2-quad core/16G RAM) active/active SQL Cluster. Our largest application that is on SQL is SPS2007/MOSS. I have a feeling that it will grow to be very large as our users are depending more on that for file shareing, colaberation, etc. Also, items like our BES, Backup Exec 12.5, Freight Tracting software, CrystalXI/SSRS, and all other mission critical DB's would all goto on this one server.

[Shylok]

Hrm. But isn't clustering SQL just getting you redudancy not splitting the workload?

Seems a little silly to me that hardware like six core cpus are available yet the software limitation for the hypervisor is 8cpus... what are other options for virtualization? Our SQL requirements are only starting to see growth now.

[ChrisJ615]

My cluster is an Active/Active cluster, meaning: Half of the DB's sit on one SQL server while the other half sit on another. When one server goes offline the other server runs both sets of DB's. I personally have never run an Active/Passive SQL cluster.

[Shylok]

Yes, I follow you. Our ERP is nuts, that's the DB that I'm concerned about. Basically the new server and SQL server 2008 is being brought in to run this one DB. I'd like to virtualize it though since i'm Virtualizing all our physical servers. A few dbs like BES, and other misc items will be also on it but none of them have any real load. The ERP though is so demanding we had to add a whole new Netapp shelf and seperate controller.

[ChrisJ615]

I got ya. If you don't me asking: What is your ERP solution?

My company is a SMB and we are looking at moving from MAS200 to MAS500 in Q2 or Q3 of next year. For sure we will be virtualizing it.

[Rumple]

If your SQL depands that much dedicated consistent processing power along with dedicated netapp disk and controllers, all you are gonig to do with virtualization is add overhead for the VM and reduce the amount of other low utilization VM's you can virtualize.

you would be much better off with a couple pizza boxes running a SQL cluster and then take 20 or 30 other servers and virtualize them using the resources you save by NOT virtualizing that SQL environment.

[Shylok]

The purpose of this virtualization is to have HA and a DR site. We're

looking at 4 ESX boxes here, with another at the DR site. Of the 4,

one would be dedicated really to our ERP db and perhaps a couple

smaller low load dbs like BES, WSUS, etc. If needed at some point I'd like the

option to add a couple small VMs to this server but it's not necessary.

We're really just dedicating it to SQL 2008.

[thehyperadvisor]

If I was in your position I'd use dedicated hardware for the ERP system. Add another physical server at the main site and cluster for HA. Then another physical server at the DR with all the memory and cpu required. Do some kind of array based DR solution or maybe logshipping, your data change rate and latency will have to determine this. Then virtualized the other pieces.

We use Symantec Cluster with VVR currently for DR/HA (no virtualization) which works well but I can't say it will work for you. The DR solution is actually setup and configured to be Active/Active so that all hardware in each data center is actually being used to some degree. Our SQL cluster in each data center is configured with 5x nodes/16GB RAM/2x Quadcore with EMC FC array. In our case we configured smaller separate sql instances which do the same thing for different sites spreading out the load.

hope this helps - thehyperadvisor.com

If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

[LucasAlbers]

...and don't forget to put your ntfs block size to 64k for the data/log volume(s).

[MattG]

Chris,

Remember FT currently only works with 1vCPU VMs. If you can fit your SQL Server perf into a single vCPU VM then you should be fine.

Shy,

ESX 3.5 supported a max of 4 vCPUs per VM. So the 8 vCPUs of vSphere are only a few months old. While with the vSphere launch VMware is finally stating that you can virualize anything, that anything still needs to within the 8 vCPU limit.

If it were me I would keep it physical and use a replication product like Double Take or replicate at the SAN level.

-MattG

If you find this information useful, please award points for "correct" or "helpful".

[ChrisJ615]

Your right. FT isn't going to work for any server I have in my VM Cloud, much less, one that needs FT like SQL, Exchange, etc.

[Gabrie1]

Plus FT doesn't allow snapshots, which could give your backup solutions problems.

http://www.GabesVirtualWorld.com