Hot-add CPU and Memory in a Win 2008 Datacenter with SQL in vSphere
I have today tested how it works to hot-add both memory and vCPU to a virtual machine running Windows 2008 R2 Datacenter Edition, this machine also has SQL 2008 R2 Enterprise edition installed.
First i had to enable hot-plug in the virtual machine, there is of course two ways to do this, either via the GUI or
the powerCLI way:
$vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec $mem = New-Object VMware.Vim.optionvalue $mem.Key="mem.hotadd" $mem.Value="true" $vmConfigSpec.extraconfig += $mem $cpu = New-Object VMware.Vim.optionvalue $cpu.Key="vcpu.hotadd" $cpu.Value="true" $vmConfigSpec.extraconfig += $cpu $vm = Get-VM TempNHot | Get-View $vm.ReconfigVM_Task($vmConfigSpec)
when this has been executed on the particular VM i can then when it is running use powerCLI again and add resources to it.
Get-VM TempNHot | Set-VM -MemoryMB 3072 -NumCpu 4 -Confirm:$false
According to Microsoft documentation it is only supported to do hot-add of memory and CPU in Datacenter edition of Windows, Also regarding SQL you have to use either Enterprise or Datacenter edition to also get it working into the application.
To check that the new resources where used i tested with the SQLstress application to get some load on the SQL server and check the taskmgr, but it did not show that the load where spreading to the new added vCPU, after some research i found out that the SQL server do not start to use new hardware right away, it need to be reconfigured to schedule load on additional CPU´s, so there is a little manual intervention but no downtime on the server!
So i started a query in SQL Management Studio and wrote
In the taskmanager i could now see that all four vCPU where equally loaded by the SQL server.
Maybe it is not so common to have to do this but if you set up a large Tier-1 SQL server in a virtual world you surely want to be able to hot-add resources when it is loaded. Think of the advantages that this brings when you actually both can add memory and cpu resources without any downtime!
In a virtualization world we always recommend our customers to buy Windows Datacenter licenses on their hosts so using it on a VM will not add any extra cost. The SQL server is of course quite a price jump from standard to enterprise but if your big SQL server uses more than 64 GB ram you will still need to use Enterprise licenses 🙂