From 0 to SQL Demo lab in a few easy steps – Part 1

As a Premier Field Engineer I get into a different challenge every week. These challenges can be anything; performance issues, architecture review or migration scenarios. If I’m at a customer site it is not always possible (or recommended) to test my solution on the production systems. And if it’s a possible bug I want to reproduce it so it can be investigated. For this you will need a lab environment. But building a lab environment can be a time consuming task. Most of the times I need different SQL versions, clustering, AlwaysOn or another Microsoft product relying on SQL server. And when I’m ready to present and prove my solution I don’t want any data from other customers to be visible. For these reasons I always build new and fresh environments. If it’s a performance issue I can get away with just a single VM but if it is a more complex scenario I might have to install a domain controller and a series of other machines as well.

I used to run Windows Server 2008 R2 on my laptop as my primary OS. Basically because I needed Hyper-V. But with Windows 8 I can run Hyper-V on a client OS and as a bonus I get a lot more PowerShell cmdlets to automate recurring tasks. Do you see where I’m going? There is some great software available to automate the deployment of VM’s. You can use Windows Deployment Services or System Center Virtual Machine Manager. But I wanted an out-of-the-box solution that didn’t require any additional tooling other than Windows 8 with Hyper-V enabled. And there is.

This post will be part of a series of posts because getting a complete AlwaysOn cluster running is a lot of preparation and understanding all the steps will make it easier to customize this to your purpose. Basically I will break it down in a few steps:

  • Part 1: Installing Windows on a virtual disk and boot it in Hyper-V
  • Part 2: Automating and customizing the installation using sysprep and unattend
  • Part 3: Running post deployment tasks like AD configuration or SQL Server installation
  • Part 4: Putting it together

Installing Windows on a virtual disk and boot it in Hyper-V

Before you can start to automate anything with PowerShell you will need the steps to do it. Imagine you would create a new VM? What steps do you need to take?

  1. Create the VM
  2. Insert the DVD
  3. Install Windows

If we are going to do this in PowerShell you will need to reorder some of the items. In PowerShell we will create the disk first, install Windows on it and create the VM in the final step.

Create a vhd(x) with PowerShell

There are several ways to create a virtual disk. You can use DISKPART but that can be a bit of a drag and is harder to automate, or you can use PowerShell. In Windows 8 you can use some new and cool cmdlets to create a virtual disk. The next script will create a vhd and format it. I will explain them after the script.

$vhdpath = “D:\vm\dc-01.vhdx”
$vhd = New-VHD -Path $vhdpath -SizeBytes 30GB

Mount-DiskImage -ImagePath $vhdpath
$disk = Get-DiskImage -ImagePath $vhdpath | Get-Disk

Initialize-Disk -Number $disk.Number -PartitionStyle MBR
$volume = New-Partition -DiskNumber $disk.Number -UseMaximumSize -AssignDriveLetter -IsActive | Format-Volume -Confirm:$false
$drive = [string]$volume.DriveLetter + “:\”

We start with setting a variable to make things easier later on. The variable $vhdpath hold the path to our virtual disk. The first step is to create the disk using New-VHD with the specified size, in my case 30GB is enough. The disk will be dynamically by default but you can create a fixed or differencing disk as well. Refer to the documentation for more options:

The following step is to mount the image using Mount-DiskImage specifying the path. When the disk is mounted you can format it using PowerShell instead of DISKPART. Formatting is a three-step action; first initialize the disk, partition it and finally format it. This is not different from DISKPART. Once it’s formatted we can get a handle to the drive which we will need later on.

With a freshly formatted drive we are ready to install Windows. For those of you that are not familiar with deployment or images; we can deploy Windows directly to a disk. No need to boot into a DVD. When you install Windows an image file (*.wim) is applied to the destination. The proper boot records are written and you are off to go. Prior to Windows 8 you would need a tool called ImageX to capture and apply images to a destination. Being part of the Windows Automation Deployment Toolkit this was another download and dependency and does not fit in my ‘only use what’s default’ policy. But with Windows 8 this is not a problem. The command line tool DISM is extended to capture and apply images as well. Being part of the operating system it can be used for my purpose. Check out the next piece of PowerShell.

Install the DVD

$image = “F:\sources\install.wim”
$dismArgs = @(“/Apply-Image”, “/ImageFile:$image, “/index:4”, “/ApplyDir:$drive)
Start-Process -FilePath “DISM.exe” -ArgumentList $dismArgs -NoNewWindow -Wait -PassThru

There is no DISM cmdlet so we will resort to invoking the process. The source is set to my DVD drive with the Windows Server 2008 R2 DVD loaded. The index is set to 4 because I’m installing the Enterprise Core edition. You need to supply an index if the image contains multiple installations. I will not elaborate much on this because in the next post I will show another method. If you want to find out the index number of the installation use DISM /Get-WimInfo /wimfile:<pathtowimfile>. Finally we apply this to the drive we obtained after the formatting.

When that’s finished the proper boot records need to be written to the disk. The tool is bcdboot and we pass the drive and boot type as parameter.

$bcdargs = @((Join-Path $drive “Windows”), “/s $drive, “/f BIOS”)
Start-Process -FilePath “bcdboot.exe” -ArgumentList $bcdargs -NoNewWindow -Wait -PassThru


Create the VM

And basically that’s it. All that is left is to create the VM, start it and open it. And as you might have guessed, Windows 8 comes loaded with all you need cmdlets.

$vmname = “DEMO-01”
Dismount-DiskImage -ImagePath $vhdpath

$vm = New-VM -name $vmname -MemoryStartupBytes 512MB -SwitchName “Hyper-V Internal Network” -VHDPath $vhdpath
Set-VMMemory $vm -DynamicMemoryEnabled $true -MinimumBytes 512MB -MaximumBytes 1GB
Start-VM $vm
vmconnect localhost -G $vm.VMId


First another variable is loaded with the name of the VM. Then the disk is dismounted and a new VM is created. It’s connected to my Hyper-V switch named “Hyper-V Internal Network”. You will need to adjust this to your environment. In the second step dynamic memory is configured. I use this a lot. Using dynamic memory enables me to have 6 VM’s running with 8GB internal memory and still not run into any issues. The final two steps are starting the vm and connecting to it using vmconnect. The latter can be discarded of course. Also notice I’m using the identifier to connect to the VM. During my testing I found out it is possible to create multiple VM’s with the same name. Connecting to the identifier makes sure you connect to the right one.

After a few minutes you should have a shiny Windows installation awaiting you to create a new administrator password. In the next post I will go into customizing the installation, sysprepping it and automating the installation process. 

Comments (1)

  1. Norman Gillis says:

    A nice article and real time saver!