SQL Server Lab in Hyper-V

Using Hyper-V Client and PowerShell to build an SQL Server lab.

Author:Shawn Melton
Date:November 14, 2016

Table of Contents

Virtual Network Switch Slide
Create a VM Slide
Building a Template Slide
Create a VM Using the Template Slide

Virtual Network Switch (p1)

Build two types of connections for the VMs to communicate

  1. Internal (so they can talk to each other)
  2. External (allow Internet access to each VM)

External: Your preference, but some will create an external connection for each physical connection on the host machine. This simply gives you the option to switch your VM(s) to the connection you are using at that time for Internet access.

Virtual Network Switch (p2)

Pull the adapter to use into a variable:


								$wifi = Get-NetAdapter | where name -eq "Wi-Fi"
						

Virtual Network Switch (p3)

Use the $wifi variable to build the external switch


# Issuing this command will cause your Internet connection to reset
New-VMSwitch -Name "External-WiFi" -AllowManagemnetOS $true -NetAdapterName $wifi.Name
					

Virtual Network Switch (p4)

Create an internal connection.


#include the CIDR block for easier reference, if desired
New-VMSwitch -Name "Internal-10.0.2.0/24" -SwitchType Internal
						

Once you create these switches you will notice your own host machine now have "vEthernet (Virtual Switch Name)". clsModify the "Internal" NIC to be an IP address for your lab subnet.


Get-NetAdapter 'vEthernet*'
Get-NetAdapter 'vEthernet (Internal-10.0.2.0 24)' | New-NetIPAddress -AddressFamily 'IPv4' -IPAddress '10.0.2.5' -PrefixLength 24 -DefaultGateway '10.0.2.1'
Get-NetAdapter 'vEthernet (Internal-10.0.2.0 24)' | Set-DnsClientServerAddress -ServerAddresses '10.0.2.10'
						

Create a VM

  • Name > W12R2DC
  • RAM Startup > 1GB
  • System drive > 20GB
  • Mount the ISO file

New-VM -Name 'W12R2DC' -MemoryStartupBytes 1GB -SwitchName "Internal-10.0.2.0/24" -NewVHDPath 'C:\VirtualMachines\W12R2DC\W12R2DC-SystemDrive.vhdx' -NewVHDSize 20GB
Set-VMDvdDrive -VMName 'W12R2DC' -ControllerNumber 1 -Path 'E:\Software\OS\Windows_2012_R2_English.iso'
Start-VM W12R2DC
					

After starting the VM you can see the ISO has booted successfully.

Hyper-V Checkpoints

Checkpoints provide a way to save the current state of the VM, used to be known as snapshots.


Get-VM -Name W12R2DC | Checkpoint-VM -SnapshotName 'PS-Default-RenameComputer'
					

Save/Stop VM

  • Saving - Like a bookmark to where you left off.
  • Stopping - Graceful powerdown, or just turn it off

# Save VM state
Save-VM -Name W12R2DC

# Stop it (graceful shutdown)
Stop-VM -Name W12R2DC

# Both commands support piping, so save all running VMs
Get-VM | Save-VM
						

Save-VM and Stop-VM overlap in functionality so it is your preference to which you use. Both commands below save the state of the VM.


Save-VM -Name W12R2DC

Stop-VM -Name W12R2DC -Save
						

Connecting to the VM

Hyper-V's VM Connection...no native "Connect-VM" to date.


# connect to localhost Hyper-V server, and W12R2DC VM
vmconnect localhost w12r2dc
					

Building a Template

Configure OS to use as a template.

Windows Server Core

Window Server 2012 R2 Core Edition configured using just PowerShell.

Basic task to do for server to setup as a template:

  1. Set PowerShell to default shell (instead of cmd)
  2. Install roles/features
  3. Disable Firewall Profiles

Configuring the NIC and renaming the computer will be done when you build the server to use it.

sconfig

Using sconfig gives you a menu driven option:

Set PowerShell to default shell

  1. Open regedit via cmd shell, browse to:
    HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\AlternateShells\AvailableShells
  2. Change the permission on the key "AvailableShells" to enable "Full Control" for local Administrators account.
  3. Then create a string key based on the following:
    • Name: 40000
    • Value Data: powershell.exe -noexit
  4. Log off and back on to have PowerShell prompt as your shell.

Installing Roles/Features

Only a few roles I add to the template, for both Core and Full Editions:

  • .NET 3.5 Core
  • Active Directory Tools

# See all that are available
Get-WindowsFeature

# need the OS ISO still mounted for this one
Install-WindowsFeature -Name Net-Framework-Core -Source 'D:\source\sxs'

# Install AD tools (they come in handy on any server)
Install-WindowsFeature -Name RSAT-AD-PowerShell,RSAT-ADDS-Tools
					

Disable Firewall Profiles

Will make it easier to just turn off all the firewall profiles on your lab servers.


Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False
					

sysprep


Set-Location C:\Windows\system32\sysprep
.\sysprep.exe /oobe /generalize /shutdown /mode:vm
						

Once the VM shuts down copy the VHDX file to another location to save as your template.

Create a VM using the template

Prep the Template File

First thing is just create a directory for your new VM and copy the template VHDX file into it, naming appropriately.

Create a new VM

Create a VM and add the VHDX file to it.


New-VM -Name 'SQL12S01' -MemoryStartupBytes 1GB -SwitchName "Internal-10.0.2.0/24" -VHDPath 'C:\VirtualMachines\SQL12S01\SQL12S01-SystemDrive.vhdx'
Start-VM SQL12S01
					

Configure Internal NIC


# Only one adapter to deal with
Get-NetAdapter | New-NetIPAddress -AddressFamily 'IPv4' -IPAddress '10.0.2.20' -PrefixLength 24 -DefaultGateway '10.0.2.1'
Get-NetAdapter | Set-DnsClientServerAddress -ServerAddresses '10.0.2.10'
				

Rename the computer

Leaving off "-Restart" you will get a warning about restarting before it takes


# To restart
Rename-Computer -NewName SQL12S01 -Restart
					

Next steps

You can utilize sysprep to build a template for the full version of Window Server 2012 R2 if you would like.

You can follow all the steps up to this point to build multiple Core Edition servers. Which can be used to run SQL Server 2014+ and all components, except SSRS. SSRS requires the full GUI edition of Windows Server.