Share via

How to Build SQL Server 2012 AlwaysOn Hyper-V Virtual Machines for Demos (EMU build)

  • Article

The set of VMs described in this article can be used to demonstrate the new high-availability features of SQL Server 2012, namely AlwaysOn Availability Groups and Failover Clustering. The purpose of the article is to document the requirments and high-level steps that are necessary in order to create the necessary virtual machines.

If you are interested in building an EMU + KIWI build see this wiki: http://social.technet.microsoft.com/wiki/contents/articles/10304.how-to-build-a-combined-set-of-kiwi-and-emu-vms-sql-server-2012-demos.aspx

Prerequisites for build machine (for creating VMs on)

  • Hyper-V role enabled in Windows Server 2008 R2 64-bit
  • 4GB RAM available to allocate to running VMs (allow headroom for host OS)
  • 200 GB free space suggested for VM building, installation media, snapshots, extra room to work in

Prerequisites for host machine (for running VMs on)

  • Same as build machine, but allow for about 100GB of free drive space, preferably running on separate spindle from the host OS drive for performance reasons.

Software requirements for guest VMs (to install on VMs)

  • Windows Server 2008 R2 Enterprise with SP1 (64-bit)
  • .NET Framework 4.0
  • iSCSI Software Target 3.3
  • SQL Server 2012 Developer Edition

IMPORTANT CONSIDERATIONS (READ BEFORE BUILDING)

  • Minimize dependencies on internet connectivity if feasible for your scenario (feedback/customer experience programs, automatic updates)
  • Evaluate whether or not you want to run Windows Update BEFORE you build or use a base VM. For some scenarios, software installed with Windows Update can lead to unforeseen consequences for testing or the evaluation of pre-release software. If you are building this VM for someone else, it would be wise to explicitly ask them this question!
  • Some aspects of setup and configuration can be error prone, so take SNAPSHOTS as you build

Base VMs

There are two sets of VMs that need to be built, only one of which will be running at a time. One domain controller VM will be shared between the two sets. To get started, we will setup a base VM that we will eventually sysprep and then duplicate in order to create the needed machines.

Start by building a base server VM with the following:

  • DO NOT ACTIVATE THIS VM
  • Hyper-V: configure 1 NIC, 1 virtual CPU (default), and 1GB RAM
  • Local Administrator account, set password Password;1
  • Install .NET Framework 3.5.1
  • Install .NET Framework 4.0 (prereq. for SQL)
  • Perform any desired cleanup steps
  • Run windows\system32\sysprep.exe from within the VM and choose OOBE, Generalize, Shutdown
  • Export the base VM for safe-keeping, this will be copied and imported for each VM later

You will need at least 4 activated base Windows Server machines in order to satisfy the demo requirements. Since these VMs will ultimately be participating in a domain network and running SQL Server, it is a requirment that these VMs are not cloned from the same machine (so we used sysprep). In the next few steps, we will take the current single base VM (sysprepped) and create 4 base VMs (activated). Note that we could activate at the end, but we would need to use more activations that way.

  • Copy the sysprepped base VM 4 times (create a naming scheme, something like base1, base2, ...)
  • Import each instance of the copied base VM, choosing to create a new machine ID each time
  • Run each instance of the new base VMs (base1, base2, ...) and go through initial windows setup process
  • Activate each instance of the new base VMs
  • Shutdown each new base VM, export for safe-keeping

EMU-DC (Domain Controller) -- shared between both sets of VM

This domain controller will be used for both sets of VMs (for Availability Group and Failover Clustering demos).

  • Copy and import one of the 4 activated base VMs
  • Configure 1 NIC, 1 vCPU, 1GB RAM
  • Configure static IP of 192.168.31.2
  • Install Active Directory Domain Services role
    • new domain in new forest, FQDN of contoso.com, install DNS server, rest defaults
  • Add contoso\sqlservice domain account for SQL service instance (same password as Admin)
  • Group Policy Edit -> Windows Settings | Security Settings | Local Policies, Security Options
    • "Domain controller: refuse machine account password changes" -> Enabled
    • "Domain member: maximum machine account password age" -> 999

AlwaysOn Availability Group demo VMs

The AlwaysOn Availability Group demos require 3 VMs to be built.

EMU-SQL1 -- (SQL Server Primary Replica)

  • Copy and import one of the 4 activated base VMs
  • Configure 1 NIC, 1 vCPU, 1GB RAM
  • Configure static IP of 192.168.31.3
  • Join contoso.com domain
  • Install Windows Server Failover Cluster feature
  • Install SQL Server 2012 default instance of database engine, management tools
    • use mixed mode auth, contoso\sqlservice account to run database instance
  • Turn off firewall for ease of configuration and demo
  • Load SSMS and test connect to instance, wait for and select option not to participate in customer experience improvement program

EMU-SQL2 -- (SQL Server Secondary Replica, automatic failover)

  • Same configuration as SQL1 except use IP of 192.168.31.4

EMU-SQL3 -- (SQL Server Secondary Replica, read-only)

  • Same configuration as SQL1 except use IP of 192.168.31.5

Setup failover cluster

  • Configure all VMs to be on the same VLAN (in Hyper-V settings select the same virtual lan #)
  • In EMU-SQL1, load the Failover Cluster Manager and select "Validate a Configuration" to ensure that all VMs are ready to go (use wizard to enter all 3 VMs and make sure that it passes)
  • Creat the cluster
    • select "Create a Cluster"
    • select the same 3 machines that were just validated
    • Use "EMU-AGClstr" for cluster name
    • use 192.168.31.6 for the cluster address
  • For (all SQL* machines)
    • In SQL Server Configuration Manager, enable "AlwaysOn High Availability" service (see properties for default instance)

AlwaysOn Failover Clustering demo VMs

The AlwaysOn Failover Clustering demos require 3 VMs to be built. Shut down the the EMU-SQL* machines so that we can work on this set.

EMU-SAN -- (iSCSI target)

  • Copy and import one of the 4 activated base VMs
  • Configure 2 NICs, 1 vCPU, 1GB RAM
  • Configure NIC #1 to static IP of 192.168.31.10, DNS of 192.168.31.2
    • This will be the domain adapter
  • Configure NIC #2 to static IP of 192.168.81.1
    • This will be the iSCSI adapter
  • Join contoso.com domain
  • Turn off firewall for ease of configuration and demo
  • Install iSCSI Software Target 3.3 (search for the free download from Microsoft)
    • don't join Customer Experience Improvement Program
    • don't use Microsoft Update
  • Launch "Microsoft iSCSI Software Target"
    • create iSCSI Target
    • use "SAN" as target name
    • for iSCSI Initiators Identifiers use IPs for EMU-SVR1 and EMU-SVR2 (192.168.81.5 and 6)
    • right-click on newly created "SAN" target and select "Create Virtual Disk..."
      • create a 5GB shared disk, a 1GB quorum disk

EMU-SVR1 -- (SQL Server Failover Cluster Node)

  • Copy and import one of the 4 activated base VMs
  • Configure 2 NICs, 1 vCPU, 1GB RAM
  • Configure NIC #1 to static IP of 192.168.31.20, DNS of 192.168.31.2
    • This will be the domain adapter
  • Configure NIC #2 to static IP of 192.168.81.5
    • This will be the iSCSI adapter
  • Join contoso.com domain
  • Turn off firewall for ease of configuration and demo
  • Install Windows Server Failover Cluster feature
  • Launch "iSCSI Initiator" setup
    • connect to 192.168.81.1 as the target
    • refresh Discovered targets section until target appears
    • Click Connect and use defaults
    • If the target doesn't show after initial refresh, try entering 192.168.81.1 into quick connect

EMU-SVR2 -- (SQL Server Failover Cluster Node)

This server is meant to be almost identical to SVR1 as it will be participating in a cluster. The only difference is that you should use a static IP of 192.168.31.21 for the domain network NIC and 192.168.81.6 for the iSCSI network NIC.

Setup continued -- Bring iSCSI disks Online

  • On EMU-SVR1, go to Volumes and Devices tab and select Auto Configure
  • Use Server Manager to take the new iSCSI volumes online & initialize, use MBR
  • Create new volumes, format, assign drive letters
  • Drives should now show up in cluster manager under Storage node)

Setup continued -- Finish Cluster Setup

Use same general steps as before to setup cluster with SVR1 and SVR2 machines.

  • In Failover Cluster Manager, configure Node and Disk Majority quorum, use the 1GB cluster disk as storage witness
  • For (all SVR* machines)
    • Install SQL Server 2012 default instance as a failover cluster
    • For SQL Server cluster network name "SQLClstr", use 192.168.31.7 for the SQL cluster IP
    • use contoso\sqlservice account for database engine and agent services
    • use the remaining 5GB iSCSI disk for data
    • On SVR2, use SQL Setup and then choose the "Add node..." option
  • Load SSMS and test connect to the cluster instance "SQLClstr", choose not to participate in customer experience improvement

Potential issues:
**
**If you are trying to log into a VM with domain credentials and see an error message about the trust relationship to the DC, here is a workaround. Log into the VM using local Administrator account, same Password;1. Next, open a command prompt and reset the machine account password:

netdom resetpwd /server:emu-dc /userd:contoso\administrator /password:Password;1

Finally, log out and test the domain login again. It should now work.

Post Build Checklist (for all VMs)

  • Ensure following features are added/enabled: Desktop Experience, Quality Windows Audio Video Experience, Windows PowerShell Integrated Scripting Environment, XPS Viewer
  • Services set to Automatic: Themes, Windows Audio
  • Remote Desktop enabled for Administrator
  • Add desktop icons for Computer, User Files, Recycle Bin
  • Change to Windows 7 theme (for any of the VMs you plan to show during demo)
  • Add scenario appropriate desktop wallpaper (e.g. SQL Server 2012)
  • Select auto-arrange desktop icons
  • Notification Area Icons: hide Volume and Action Center notifications
  • 'Do Not Show Server Manager' enabled
  • Disable expiration for Administrator passwords (local and domain), contoso\sqlservice
  • Disable windows update
  • Ensure all software is activated (if desired)
  • Cleanup event logs with powershell:
    • wevtutil el | Foreach-Object {Write-Host "Clearing $_"; wevtutil cl "$_"}
  • Run disk cleanup on VM drive, delete any temporary installation files, etc.
  • Clear browser history, etc.
  • Ensure size of VHD is approprate (account for pagefile and additional demo material)
  • Shutdown VM
  • Configure final VM settings (for consistency and to avoid warnings on import)
    • Remove any attached ISO images from drives
    • Set network adapters to "not connected"
  • Export VM
  • Mount VHD to host OS (use disk management tool), delete the following:
    • c:\pagefile.sys
    • c:\temp\.*
    • c:\Recycle.Bin
    • c:\windows\temp\.*
    • defrag VM drive TWICE
  • Detach VHD from host OS
  • Compact VHD with Hyper-V (use Edit Disk functionality...)
  • If RARing for distribution/handoff, consider using self-extracting archive option (use -sfx option) and split into manageable sized volumes. In addition, consider using the -rr option to include recovery records in the archive so that data may be recovered in the event that some get corrupted
  • If RARing, also perform a test extraction locally to make sure everything works as expected. If especially paranoid, also perform a test import of that test extraction -- sometimes strange things happen

Return to [[SQL Server 2012 Developer Training Kit Virtual Machine]] wiki