Sql-server – Design Sql Server physical configuration from existing VM

sharepointsql serverssasssis-2016vmware

We have 4 virtual machines that we used to test our BI platform (PoC), the final dimensions are:

  • SSIS (16 vcpu), SSAS (8 vCPU), SSRS (4 VCPU) et Data Engine (24 vCPU)

What are the recommendations to install these components to a physical server?

For SQL Server performance, VMware recommends (chap. 3.5.2) setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform". That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each.

Can we calculate physical server dimensions using reverse engineering? I mean: 16 vCPU equals 16 physical core?

The dimensions CPU and RAM of these VM are big so we are asking if it's not more interesting to deploy on physical machines. We need 280 GB of RAM.

The same thing that VMware said here the ratio 1:1 or 1:2 is considered for more performance.

Best Answer

That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each.

Not exactly, that means initially a Host with a CPU of 16 cores should only have 1 VM with 16 vCPUs or 2 VMs with 8 vCPUs each instead. But as the chapter 3.5.2 Allocating vCPU says:

if the physical server that the various SQL Server workloads currently run on equates to 16 physical CPU cores, avoid allocating more than 16 virtual vCPUs for the VMs on that vSphere host during the initial virtualization effort.

Notice it says initial virtualization effort. So, after some time, you will probably find out that it's not the best configuration for your environment. And it continues:

After you have determined that there is excess capacity to be used, you can consider increasing density by adding more workloads into the vSphere cluster and allocating virtual vCPUs beyond the available physical cores. Consider using monitoring tools capable to collect, store and analyze mid- and long-terms data ranges.

It's not that there is a faster ratio then 1:1, it's just that apart from being the fastest it's the most expensive ratio. In a VMware environment you can find performance charts that will give you some information about the workload on a VM.

Can we calculate physical server dimensions using revese engeneering? I mean : 16 vCPU equals 16 physical core?

Yes, but

  1. lets say that VM has been using only 4 vCPUs at peak to process its workload. When you create the physical server with 16 physical cores, 12 of them will be sitting doing nothing and you paid for that;

  2. Now let's say that VM has been using all 16 cores at peak and you build a physical server with exactly 16 cores. Next month, when the workload increases requiring 17 cores to run properly, you'll have to by new hardware.

So, if you really decide to go for a physical environment and just replicates physical copies of your VMs without the monitoring information of your servers, chances are good of sizing them ineficiently.

If your only motivation to change from VM to physical is the size of your VMs, check the Configuration Maximums of vSphere and you might find out that you still have plenty of room to grow your VMs (provided you have hardware on the host).

Related Question