[dba-SQLServer] [AccessD] A real puzzler

[John W. Colby]

 >>I don't understand why so many cores in so relatively few machines is 
advantageous.

SQL Server can break complex queries down and assign pieces to threads.  
The more cores, the more threads can be used simultaneously.  It is 
fascinating to watch task manager when it does this behavior, I have 
seen all of my cores (16) maxed.

 >>I don't get the point of stacking multiple VMs in one or more boxes, 
as opposed to creating a cluster with a large number of boxes all 
visualized as a single computer.

It turns out that the VMs run faster off of SSDs.  I bought an Areca 
controller, hooked up about five 500gb SSDs, created a raid out of that 
and then placed the VM files (the files which are the VMs) on that SSD.  
Having an actual dedicated raid controller allows me to do this 
"economically" and easily.

After that, I got one VM running, then cloned it.  It did involve some 
registry search / replace to get each one thinking it was a different 
machine, but you get the picture.  So I can now "turn on" one or two or 
six of these machines if I want.  I also have a single VM acting as the 
custom C# program "driver" of the system, and another VM which runs a 
small SQL Server instance for my own use.

I went out and purchased 8 licenses of Windows Home Server when that was 
going out of style.  $30 each IIRC.  So I can have 8 different VMs, each 
with its own legal windows key, no MS complaints etc.

I then installed legal copies of third party software on each VM.  I 
could only run a single instance of this software on any given box. Each 
license gives me (on the VMs) about 1.5 million records / hour data 
cleaning speed.  I have to clean 500 MILLION records per month, plus do 
other things as well (fill orders).  So a single copy of the third party 
software just wasn't cutting it.  I normally run four VMs, thus 4 
licenses of this software, and about 6 MILLION records / hour.  It still 
takes a lot of time but 1/4 the time over all (more or less).

All of this running on a single reasonably simple "VM server" machine, a 
hex core AMD with 32 gigs ram, the Areca raid controller and a handful 
of SSDs.  Administering the VMs is easy from the server machine.  One 
physical box, six (normally) VMs running.

VMs are incredibly easy to set up and administer.

The third party software would in fact run somewhat faster on individual 
Intel based machines, but the hardware costs would be MUCH higher, 
including hosting costs at the facility that hosts my servers.  And the 
administration would be much larger.

John W. Colby

On 8/12/2015 1:46 PM, Arthur Fuller wrote:
> JWC,
>
> a) Given that you get the job done, if not optimally, then All Is Well,
> sort of. Granted, you have more hardware than I ever imagined owning, and a
> larger database than I've ever worked on;
>
> b) Also given that compared to your knowledge of hardware I am a duffer;
>
> I don't understand why so many cores in so relatively few machines is
> advantageous. Perhaps that's because my electricity costs are included in
> the rent, and thus essentially frree; but that said, I don't get the point
> of stacking multiple VMs in one or more boxes, as opposed to creating a
> cluster with a large number of boxes all visualized as a single computer.
>
> Maybe I should Google this and learn why this approach is better.
>
>