VMWare Workstation Lab

This is where I spend a lot of my down time. Building the lab is time consuming, and dusts off those Server level skillsets, but once it is in place the tough part is building the examples in the databases, potentially on every version form SQL2005 to the current SQL2022. Creating examples featuring External Tables using Polybase, column store indexes and memory optimized tables, Change Data Capture and Change Tracking, Partitioning, Service Broker, as well as the bread and butter things like Always Availability Groups, Replication and Log Shipping and many other things that demonstrate that me, as a DBA, actually work with and play with the more advanced features that Are Available in SQL Server, but might not have traction in a companies environment yet.

I used to keep SQL2000 and SQL2005 versions in my Lab, but finally decided anything over ten years old, really doesn’t need my attention any more. I really concentrate on SQL2016 and above now.

How do you build your lab? right now, I provision any new machine form a base image that was sysprep-ed, and waiting to reboot so it assigns a new name.

I have a stack of PowerShell Scripts that install , say, SQL2019, and then installs a favorite set of software via Chocolatey or from a known share on my virtual domain, where all the installers exist. The next set of scripts go ahead and set Server settings like Distributed Transaction Coordinator and more, and then finally run a loooong collection of SQL scripts on the instance to put my favorite tools in place, extended events, procs, backup and maintenance and the baseline I have established over decades of experience and improvements.

My laptop that supports this Labhas 64 gig of RAM, and that lets me run maybe 20 or so VMs with small 2-3 gig footprints before I cripple my performance. However, if I were to try to start 20VM’s at the same time, I end up getting a disk IO storm, that I cannot seem to recover from, as I run out of Patients waiting.

i use this PowerShell below as an example of how I wake up my Lab, in a specific order(the primary domain controller first), with a modest Start-Sleep command to prevent me from killing my disk IO, SSDs are fast, but VM’s are IO intensive when they wake up fast.

function Start-SpecificVMs()
{ 
  [string[]] $SpecificMachines = @(
    'C:\VirtualMachines\VMWareWorkstation\Win2016(ADDC)\Win2016(ADDC).vmx',
    'C:\VirtualMachines\VMWareWorkstation\Win2016Dev\Win2016Dev.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2008R2\SQL2008R2.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2012\SQL2012.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2014\SQL2014.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2016\SQL2016.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2016MountPoints\SQL2016MountPoints.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2017\SQL2017.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019\SQL2019.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019Encrypted\SQL2019Encrypted.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019Mask\SQL2019Mask.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019PolyBase\SQL2019PolyBase.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019ReplicationPub\SQL2019ReplicationPub.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019ReplicationSub\SQL2019ReplicationSub.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019AAG_N1\SQL2019AAG_N1.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019AAG_N2\SQL2019AAG_N2.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019FCI_N1\SQL2019FCI_N1.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2019FCI_N2\SQL2019FCI_N2.vmx',
    'C:\VirtualMachines\VMWareWorkstation\SQL2022\SQL2022.vmx')

  ###################################################################################################
  ## Specific Machines
  ###################################################################################################

  [string[]] $CurrentlyRunningMachines = &"C:\Program Files (x86)\VMware\VMware Workstation\vmrun.exe" list
  $AllVirtualMachines = Get-ChildItem "C:\VirtualMachines\VMWareWorkstation\" -Filter "*.vmx" -Recurse
  foreach($VMFav in $SpecificMachines) # $VM = $AllVirtualMachines[0]
  {
    if ($VMFav.EndsWith(".vmx"))
    { 
      $JustTheFileName    = [System.IO.Path]::GetFileName($VMFav) 
      if ($CurrentlyRunningMachines.Contains($VMFav))
      {
        Write-Host "running" $JustTheFileName -ForegroundColor Gray
      }
      else
      { 
        $startTime = (Get-Date).DateTime
        Write-Host "Starting" $JustTheFileName -ForegroundColor Green
        &"C:\Program Files (x86)\VMware\VMware Workstation\vmrun.exe" -T ws start $VMFav  nogui
        Start-Sleep -Seconds 30 ##avoid  boot storm where vms overwhelm the disk IO
        $endTime = (Get-Date).DateTime
        $ts = New-TimeSpan -Start $startTime -End $endTime
        Write-Host ("Elapsed Time:mm:ss:mss: "  + $ts.Minutes.ToString("00") + ':' + $ts.Seconds.ToString("00") + ':' + $ts.Milliseconds.ToString("00")) -ForegroundColor Yellow

      }
   
    }
  }
}
Start-SpecificVMs