Getting Fancier With VM Creation

Raul Gonzalez shows how to spin up a Hyper-V VM using Powershell:

There are a lots of command to create or manipulate VM’s and I’m still only scratching the surface, but although I’m not a PS person, I have to admit that every time I want to do something, I find relatively easy to find a powershell command or a script for it, so I like it.

For instance, creating new virtual machines it’s a simple as one command


And that’s only the beginning, we can add the different virtual hardware like in the UI, Drives, Network Adapters and so on. And then configure memory, CPU and NUMA, etc…

This is the script which I’m more or less running to create my VM’s, this in particular will be a Hyper-V Host itself, so there are a couple of interesting settings I’ll tell you about later.

Click through for Raul’s script.

Spinning Up Hyper-V VMs With Powershell

Andrew Pruski shows us how to enable Hyper-V and create a Windows VM using Powershell:

I’m constantly spinning up VMs and then blowing them away. Ok, using the Hyper-V GUI isn’t too bad but when I’m creating multiple machines it can be a bit of a pain.

So here’s the details on the script I’ve written, hopefully it could be of some use to you too.

Click through for the script; it’s ultimately just a few lines of code.

VMware Configuration For SQL Server

Jeff Mlakar talks about things you want to look at if you’re running SQL Server on VMware:

In a virtual data center CPU is spread across many guest VMs. This is one of the key drivers behind the effort to virtualize – CPU cores mostly sit unused. For example, we can take a host with maybe 48 cores and virtualize many machines that present logically with > 48 cores. The hypervisor can swap in and our cores as it needs based on what the guest VMs are doing. If the baseline for a guest VM is only 10% CPU usage then this is easy. However, when an intense application like SQL Server is virtualized it must have CPU available otherwise performance will suffer noticeably.

Generally for CPU on a guest VM:

  • Reservations on CPU are not often possible but consider them if you data center allows for it.

  • You want more cores than sockets. So if you are aiming for 8 cores you want something like 2 sockets with 4 cores each instead of 8 sockets with 1 core each.

  • If priority can be given to the SQL VM for CPU then change the Shares Resource Allocation from normal to high.

Click through for more helpful hints.

Figuring Out Virtual Sockets And Cores

Denny Cherry looks at a few considerations regarding virtual sockets and cores for a VM running SQL Server:

Standard Edition

You wants 1×6 (one socket, 6 cores) because standard edition will only use the first 4 sockets in a server (up to 16 cores combined). There’s no getting around that.

From a NUMA perspective as long a vNUMA at the Hypervisor is disabled then it doesn’t matter as SQL Server standard edition isn’t NUMA aware (NUMA awareness is an Enterprise Edition feature).

Read on for a more nuanced answer when it comes to Enterprise Edition.

CPU Hot-Add And NUMA

Frank Denneman discusses VMware NUMA behavior when you hot-add more CPUs:

But what happens when the VM is configured with less vCPUs than the core count of the physical CPU package and CPU Hot-Add is enabled? Will there be performance impact? And the answer is no. The VPD configured for the VM fits inside a NUMA node, and thus the CPU scheduler and the NUMA scheduler optimizes memory operations. It’s all about memory locality. Let’s make use of some application workload test to determine the behavior of the VMkernel CPU scheduling.

For this test, I’ve installed DVD Store 3.0 and ran some test loads on the MS-SQL server. To determine the baseline, I’ve logged in the ESXi host via an SSH session and executed the command: sched-stats -t numa-pnode. This command shows the CPU and memory configuration of each NUMA node in the system. This screenshot shows that the system is only running the ESXi operating system. Hardly any memory is consumed. TotalMem indicates the total amount of physical memory in the NUMA node in kb. FreeMem indicates the amount of free physical memory in the NUMA node in kb.

Interesting reading.

SQL Server On VMware Guide

David Klee announces an update to VMware’s SQL Server best practices guide:

I am proud to announce that we contributed to the latest revision of the Microsoft SQL Server on VMware best practices guide, freely available at this address. This document outlines some of the common VM-level tweaks and adjustments that are made when running enterprise SQL Server VMs on VMware platforms. This guide is considered a must-read if you manage these sorts of SQL Servers, which cannot be treated as general purpose virtual machines.

This guide was recently updated for vSphere 6.5, and we consider it an absolute must for your enterprise management library!

If you manage SQL Server instances on VMware, it’s definitely worth the read.

Disabling VMware In-Guest Clock Updates

David Klee explains when VMware will update the internal clocks for VMs and shows how to disable that:

These time sync actions can move a guest’s time backwards as well as forwards. More details about this conflict of settings are found in VMware KB1189. If the host time is out of sync, such as when a BIOS battery fails, bad things can happen. This action is extremely detrimental to the state of SQL Server high availability features, such as Availability Groups and Failover Cluster Instances, which depend on the in-guest time closely aligning with the Active Directory synchronized time. This action must be explicitly disabled to ensure that these maintenance items do not trigger an unexpected failover of the SQL Server HA solution. To disable this action, perform the following tasks.

I’d imagine that the ideal would be everything being synched to a single NTP source.

Azure VM Encryption

Melissa Coates looks at different encryption methods available for Azure Virtual Machines:

Initially I opted for Storage Service Encryption due to its sheer simplicity. This is done by enabling encryption when you initially provision the storage account. After having set it up, I had proceeded onto other configuration items, one of which is setting up backups via the Azure Recovery Vault. Turns out that encrypted backups in the Recovery Vault are not (yet?) supported for VMs encrypted with Storage Service Encryption (as of Feb 2017).

Next I decided to investigate Disk Encryption because it supports encrypted backups in the Recovery Vault. It’s more complex to set up because you need a Service Principal in AAD, as well as Azure Key Vault integration. (More details on that in my next post.)

Click through for a point-by-point comparison between the two methods.

VSphere 6.5 And VNUMA

David Klee notes that vSphere 6.5 might modify vNUMA settings on you:

I appreciate their attempt to improve performance, but this presents a challenge for performance-oriented DBAs in many ways. It now changes expected behavior from the basic configuration without prompting or notifying the administrators in any way that this is happening. It also means that if I have a host cluster with a mixed server CPU topology, I could now have NUMA misalignments if a VM vMotions to another physical server that contains a different CPU configuration, which is sure to cause a performance problem.

Worse yet is that if I were to restart this VM on this new host, the hypervisor could automatically change the vNUMA configuration at boot time based on the new host hardware.

I now have a change in vNUMA inside SQL Server. My MaxDOP settings could now be wrong. I now have a change in expected query behavior. 

Definitely read the whole thing if you’re using vmWare.

Dockerizing ASP.Net Applications

Elton Stoneman shows how to package an ASP.Net web application into a Docker image:

It’s how you start to package “legacy” ASP.NET apps in Docker images, so you can run them in containers on Windows 10 and Windows Server 2016. Once you’ve packaged your app into a container image you have:

  • a central artifact which dev and ops teams can work with, which helps you transition to DevOps;

  • an app that runs the same on your laptop, on the server, on Azure, on AWS, which helps you move to the cloud;

  • an app platform which supports distributed systems, which helps you break down the monolith into microservices.

This is part one of a series, but if you read through this post, you’ll end up with a fully-functional app.


September 2017
« Aug