Create a VM in Hyper-V then Run DSC to Install SQL

Demo-BaseVM.ps1

#Install-Module -Name Convert-WindowsImage
#Requires module dism : https://docs.microsoft.com/en-us/powershell/module/dism/get-windowsoptionalfeature?view=win10-ps

Set-Location $HOME

$isoFilePath      = 'F:\ISO\en_windows_server_2016_updated_feb_2018_x64_dvd_11636692.iso'
$SwitchName       = Get-VMSwitch -SwitchType External | Select-Object -expand Name -First 1
$ImageName        = 'Windows Server 2016 Datacenter (Desktop Experience)'
$ImageVhdFilePath = 'F:\VMs\Virtual Hard Disks\WindowsServer2016-Image.1.vhdx'

###
### Create the Virtual Disk from the ISO
###
$convertwindowsimageParameters = @{
SourcePath          = $isoFilePath
Edition             = 'Windows Server 2016 Datacenter (Desktop Experience)'
VHDPath             = $ImageVhdFilePath
VHDPartitionStyle   = 'GPT'
VHDFormat           = 'VHDX'
RemoteDesktopEnable = $true
}
Convert-WindowsImage @convertwindowsimageParameters -Verbose       

### Add features to the VHD
#-Source E:\sources\sxs
Install-WindowsFeature -Vhd $ImageVhdFilePath -IncludeAllSubFeature -Verbose -Name @(
    'Web-Server'
    'Web-Mgmt-Tools'
    'Windows-Identity-Foundation'
    'NET-Framework-Features'
    'NET-Framework-45-Features'
    'Application-Server'
)

$MountPath = "C:\Temp\Mount$(Get-Random -Maximum 999)"

Write-Verbose "$(Get-Date) Mounting the new VHD."
New-Item -Path $MountPath -ItemType directory -Force | Out-Null
Mount-WindowsImage -ImagePath $ImageVhdFilePath -Index 1 -Path $MountPath | Out-Null

## TODO: broken - need to figure out what the features are now in 2016, they've changed since 2012
Enable-WindowsOptionalFeature -Path $MountPath -FeatureName @(
    'Web-Server'
    'Web-Mgmt-Tools'
    'Windows-Identity-Foundation'
    'NET-Framework-Features'
    'NET-Framework-45-Features'
    'Application-Server'
)

Get-WindowsOptionalFeature -Path $MountPath | select -ExpandProperty Featurename | sort

### Close and Save the VHD
Write-Verbose "$(Get-Date) Dismounting the new VHD."
Dismount-WindowsImage -Path $MountPath -Save | Out-Null  

Demo-NewSqlVM.ps1

<#
.Synopsis
   Creates a new SQL virtual machine
.DESCRIPTION
   This command results in a new VM running SQL Server
   It depends on: 
   1. Windows Server 2016 ISO (from MSDN)
   2. SQL Server ISO (from MSDN)
   3. An existing Active Directory (should probably make this optional, but for my purposes I domain join the SQL servers)
   Once the VM is started, WinRM is used to start the DSC configuration which finishes the configuration.

	WARNING: there are hard coded paths and settings in this script!!!

	At a high level this script does the following:
	-Creates a virtual hard drive from the Windows intallation ISO
	-Creates a new Unattend.xml file
	-Copies the Unattend.xml into the copied VHDX
	-Creates a new VM using the copied VDHX
	-Starts the new VM
	-Adds a DVD drive to the VM using the ISO that contains the SQL installation files
	-Enables RDP and Firewall rules in the VM
	-Installs the certificate and private key in the VM
	-Starts the DSC configuration process using a pushed configuration
	-Waits for the SQL Server Service to be running
.EXAMPLE
    Create a VM with a generated name.
   .\Demo-NewSqlVM.ps1 -Verbose
.EXAMPLE
    Create a VM with a provided name.
   .\Demo-NewFimVM.ps1 -Name MySqlVM0001 -Verbose
.OUTPUTS
   the VM created by this command
.NOTES
   TODO: implement -AsJob

#>
[CmdletBinding()]
[OutputType([Microsoft.HyperV.PowerShell.VirtualMachine])]
Param
(
    # The name of the new virtual machine
    $Name = "CMVM$(Get-Random -Minimum 10000 -Maximum 99999)",

    # The credential used to join the domain
    [PSCredential]
    $DomainJoinCredential,
    
    # The credential used for the local administrator
    [PSCredential]
    $LocalAdminCredential
)

$VerbosePreference = 'continue'
$SwitchName       = Get-VMSwitch -SwitchType External | Select-Object -expand Name -First 1
#$ImageName        = "Windows Server 2016 Datacenter (Desktop Experience)"
$ImageVhdFilePath = "F:\VMs\Virtual Hard Disks\WindowsServer2016-Image.vhdx"
$isoFilePath      = "F:\ISO\en_windows_server_2016_updated_feb_2018_x64_dvd_11636692.iso"
$sqlIsoFilePath   = "F:\ISO\en_sql_server_2016_enterprise_with_service_pack_1_x64_dvd_9542382.iso"
$crmIsoFilePath   = "F:\ISO\en_microsoft_dynamics_crm_server_2016_x86_x64_dvd_7171743.iso"
$vhdFilePath      = "F:\VMs\Virtual Hard Disks\$Name.vhdx"
$StartUpMemoryGB  = 4
$ProcessorCount   = 2
$UnattendFilePath = "$HOME\$Name.xml"
$MountPath        = "C:\Temp$Name"

Write-Verbose "Using unattend file: $HOME\$Name.xml" 
Write-Verbose "VM will have $ProcessorCount processors."
Write-Verbose "VM will have $StartUpMemoryGB GB memory."
Write-Verbose "Using base VHD:  $ImageVhdFilePath"
Write-Verbose "Creating new VHD: $vhdFilePath"
Write-Verbose "VM will use switch: $SwitchName"
Write-Verbose "Using mount path: $MountPath"

#region Test pre-reqs
if (-not (Get-Service vmms | Where-Object Status -eq 'Running'))
{
    Throw "Hyper-V Virtual Machine Management Service is not runnning.  HELP!"
}
if (-not (Get-Module Dism -ListAvailable))
{
    Throw "The Dism module is not available.  HELP!"
}
if (-not (Test-Path $ImageVhdFilePath))
{
    Throw "Could not find the base VHD image: $ImageVhdFilePath"
}
if (-not (Test-Path .\Demo-SqlDscConfiguration.ps1))
{
    Throw "Could not find the DSC configuration"
}
#endregion

Write-Verbose "$(Get-Date) Creating the unattend.xml file for the new VM."
[xml]$UnattendFile = @'
<?xml version='1.0' encoding='utf-8'?>
<unattend xmlns="urn:schemas-microsoft-com:unattend">
    <settings pass="specialize">
    <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <ComputerName>{0}</ComputerName>
        <TimeZone>Pacific Standard Time</TimeZone>
    </component>
    </settings>
    <settings pass="oobeSystem">
    <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS">
        <OOBE>
        <HideEULAPage>true</HideEULAPage>
        <SkipMachineOOBE>true</SkipMachineOOBE>
        <SkipUserOOBE>true</SkipUserOOBE>
        <ProtectYourPC>3</ProtectYourPC>
        <NetworkLocation>Work</NetworkLocation>
        </OOBE>
        <UserAccounts>
        <AdministratorPassword>
            <Value>{1}</Value>
            <PlainText>true</PlainText>
        </AdministratorPassword>
        </UserAccounts>
    </component>
    </settings>
</unattend>
'@ -F $Name, $LocalAdminCredential.GetNetworkCredential().Password
$UnattendFile.Save($UnattendFilePath)

Write-Verbose "$(Get-Date) Copying the base image to a new VHD."
Copy -Path $ImageVhdFilePath -Destination $vhdFilePath -Verbose -Force

### Increase the VHD size
Write-Verbose "Resizing VHD to 50GB"
Get-VHD -Path $vhdFilePath | Resize-VHD -SizeBytes 50GB

### Mount the VHD
Write-Verbose "$(Get-Date) Mounting the new VHD."
New-Item -Path $MountPath -ItemType directory -Force | Out-Null
Mount-WindowsImage -ImagePath $vhdFilePath -Index 1 -Path $MountPath | Out-Null

    ### Create the Temp folder
    mkdir "$MountPath\Temp"

    ### Copy DSC Configuration into the VM
    Copy -Path .\Demo-SqlDscConfiguration.ps1 -Destination "$MountPath\Temp" -Force

    ### Copy the Unattend XML
    Copy -Path $UnattendFilePath  -Destination "$MountPath\unattend.xml" -Force

    ### Copy the DSC Resources into the VHD
    dir F:\ISO\DscResources | copy -Destination "$MountPath\Program Files\WindowsPowerShell\Modules" -Recurse -Force


### Close and Save the VHD
Write-Verbose "$(Get-Date) Dismounting the new VHD."
Dismount-WindowsImage -Path $MountPath -Save | Out-Null  

### Create the VM
Write-Verbose "$(Get-Date) Creating the new VM: $Name"
New-VM -Name $Name -VHDPath $vhdFilePath -SwitchName $SwitchName -MemoryStartupBytes ($StartUpMemoryGB*1GB) -Generation 2 -BootDevice VHD | Out-Null
Set-VM -Name $Name -ProcessorCount $ProcessorCount
Set-VM -Name $Name -Notes "VM created by $(whoami) on $(Get-Date)"

### Start the VM and wait for the OS
Write-Verbose "$(Get-Date) Starting the VM."
Start-VM -Name $Name
do {Write-Verbose "$(Get-Date) Waiting for the VM heartbeat."; Start-Sleep -Seconds 10} 
until ((Get-VMIntegrationService $Name | ?{$_.name -eq "Heartbeat"}).PrimaryStatusDescription -eq "OK")
do {
    Write-Verbose "$(Get-Date) Waiting for the VM IP Address."; Start-Sleep -Seconds 10
    $VMIPAddress = Get-VMNetworkAdapter -VMName $Name | Select -Expand IPAddresses | Select -First 1
    ##TODO - validate the WMIPAddress is routable (not 169...)
} 
until ($VMIPAddress)
do{Write-Verbose "$(Get-Date) Waiting for WSMan to respond."; Start-Sleep -Seconds 30}
until(Test-WSMan -ComputerName $VMIPAddress -ErrorAction SilentlyContinue)

### Add the new VM to the TrustedHosts for this VM Host
Write-Verbose "$(Get-Date) Adding the VM to the TrustedHosts on the VM host."
Set-Item WSMan:\localhost\Client\TrustedHosts -Value "$VMIPAddress" -Concatenate -Force

### Add a DVD Drive
Write-Verbose "$(Get-Date) Adding a DVD Drive."
Add-VMDvdDrive -VMName $Name

### Mount the SQL 
Write-Verbose "$(Get-Date) Mounting the SQL ISO."
Set-VMDvdDrive -VMName $Name -Path $sqlIsoFilePath

### Add a DVD Drive
Write-Verbose "$(Get-Date) Adding a DVD Drive."
Add-VMDvdDrive -VMName $Name

### Mount the Windows ISO 
Write-Verbose "$(Get-Date) Mounting the Windows ISO."
$vmDvdDrives = Get-VMDvdDrive -VMName $Name
Set-VMDvdDrive -VMName $Name -Path $isoFilePath -ControllerLocation ($vmDvdDrives.ControllerLocation | Sort-Object | Select-Object -Last 1)

### Add a DVD Drive
Write-Verbose "$(Get-Date) Adding a DVD Drive."
Add-VMDvdDrive -VMName $Name

### Mount the CRM ISO 
Write-Verbose "$(Get-Date) Mounting the CRM ISO."
$vmDvdDrives = Get-VMDvdDrive -VMName $Name
Set-VMDvdDrive -VMName $Name -Path $crmIsoFilePath -ControllerLocation ($vmDvdDrives.ControllerLocation | Sort-Object | Select-Object -Last 1)

### Enable RDP
Write-Verbose "$(Get-Date) Using WinRM to endable RDP."
Invoke-Command -ComputerName $VMIPAddress -Credential $LocalAdminCredential -ScriptBlock {
    #Allow incoming RDP on firewall
    Enable-NetFirewallRule -DisplayGroup "Remote Desktop"
    #Enable secure RDP authentication
    Set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp' -Name "UserAuthentication" -Value 1   
}

### Set the MaxEnvelopeKb Size
Write-Verbose "Using WinRM to set the MaxEnvelopeKb Size for WSMan."
Invoke-Command -ComputerName $VMIPAddress -Credential $LocalAdminCredential -ScriptBlock {
    Set-Item -Path WSMan:\localhost\MaxEnvelopeSizekb -Value ([Int]([System.UInt32]::MaxValue / 1000)) #NOTE: the parameter says 'KB' so dividing by 1K to hit the max
}

### Start DSC
Write-Verbose "Using WinRM to start the DSC configuration."
Invoke-Command -ComputerName $VMIPAddress -Credential $LocalAdminCredential -ScriptBlock {
    C:\Temp\Demo-SqlDscConfiguration.ps1 -DomainCredential $Using:DomainCredential -LocalAdminCredential $Using:LocalAdminCredential
} 

do{
    Write-Verbose "$(Get-Date) Waiting for the SQL Service to reach the started state."
    
    $vm =  Get-WmiObject -Namespace root\virtualization\v2 -Class Msvm_ComputerSystem -Filter "ElementName='$Name'"
    $kvp = Get-WmiObject -Namespace root\virtualization\v2 -Query "Associators of {$Vm} Where AssocClass=Msvm_SystemDevice ResultClass=Msvm_KvpExchangeComponent"

    $kvpItems = [xml]"<KvpItems>$($kvp.GuestIntrinsicExchangeItems)</KvpItems>"
    $kvpItem = Select-Xml -Xml $kvpItems -XPath "/KvpItems/INSTANCE[PROPERTY/VALUE[.='SqlServiceStatus'] and PROPERTY/VALUE[.='Running']]" 
    Start-Sleep -Seconds 60
}
until($kvpItem)

### Output the new VM
Get-VM -Name $Name | Write-Output

Demo-SqlDscConfiguration.ps1

<#
NOTE - this is currently broken, pending:
- get creds in via parameters
- refine the SQL dsc resource parameters

This is the DSC configuration script for a SQL one-box.  

PRODUCTS
========
The following are installed by this configuration:
-SQL Server 2016

REQUIRED FILES
==============
This installation depends on files in the 'D' drive.  The files are located on an ISO attached to the VM.

The domain account is used for the following:
-Join the computer to the domain

BEFORE RUNNING THIS CONFIGURATION
=================================
This configuration depends on a machine with the following:
-the Certificate and Private Key installed to cert:LocalMachine\My

-DSC Resources copied to the Program Files\WindowsPowerShell\Modules folder
    -SqlServerDsc
    -ComputerManagement
    -xPendingReboot
    -PSDesiredStateConfiguration
#>

Param
(
    # The credential used to join the domain
    [PSCredential]
    $DomainCredential,
    [PSCredential]
    $LocalAdminCredential
)
$WindowsDvd = Get-Volume | Where FileSystemLabel -EQ SSS_X64FRE_EN-US_DV9
$SqlDvd = Get-Volume | Where FileSystemLabel -EQ SQL2016_x64_ENU 

$cert = New-SelfSignedCertificate -Type DocumentEncryptionCertLegacyCsp -DnsName 'DscEncryptionCert' -HashAlgorithm SHA256

$cert | Export-Certificate -FilePath "C:\Temp\dsc.cer" -Force

$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName               = (hostname)
            CertificateFile        = "C:\Temp\dsc.cer"
            PSDscAllowDomainUser   = $true
         }
    )
}

configuration SqlInstall
{
    Import-DscResource -ModuleName SqlServerDsc
    #Import-DsCResource -ModuleName xPendingReboot
    Import-DsCResource -ModuleName ComputerManagementDsc
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    node $AllNodes.NodeName
    {
        LocalConfigurationManager 
        { 
             CertificateId = (Dir Cert:\LocalMachine\My | Where Subject -eq CN=DscEncryptionCert | Select -ExpandProperty Thumbprint)             
             RebootNodeIfNeeded = $true
             ConfigurationModeFrequencyMins = '15'             
        } 

        #region Join the Domain
        Computer JoinDomain
        {
            Name             = $Node.NodeName
            DomainName       = $DomainCredential.GetNetworkCredential().Domain
            Credential       = $DomainCredential 
        }
        #endregion

        #region Windows Features
        WindowsFeature  WindowsIdentityFoundation
        {
            Ensure = "Present"
            Name   = "Windows-Identity-Foundation"
            IncludeAllSubFeature = $true   
        }

        WindowsFeature NetFramework35Core
        {
            Name = "NET-Framework-Core"
            Ensure = "Present"   
        }
 
        WindowsFeature NetFramework45Full
        {
            Name = "NET-Framework-45-Features"
            Ensure = "Present"
            IncludeAllSubFeature = $true
        }

        WindowsFeature WebMgmtTools
        {
            Ensure = "Present"
            Name   = "Web-Mgmt-Tools"
            IncludeAllSubFeature = $true  
        }

        WindowsFeature WebWebServer
        {
            Name = "Web-WebServer"
            Ensure = "Present"
            IncludeAllSubFeature = $true
        }

        SqlSetup 'InstallSQLServer'
        {
            InstanceName         = 'MSSQLSERVER'
            Features             = 'SQLENGINE,FULLTEXT'
            SQLCollation         = 'SQL_Latin1_General_CP1_CI_AS'                 
            SQLSysAdminAccounts  = $LocalAdminCredential.UserName, $DomainCredential.UserName               
            SourcePath           = "$($SqlDvd.DriveLetter):\"
            UpdateEnabled        = 'False'
            ForceReboot          = $false
            #DependsOn            = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
        }

        Registry HypervKvpForSqlService
	{
		Ensure    = "Present"
		Key       = "HKLM:\SOFTWARE\Microsoft\Virtual Machine\Auto"
		ValueName = "SqlServiceStatus"
		ValueData = "Running"
		DependsOn = '[SqlSetup]InstallSQLServer'
	}
        #endregion
    }
}

SqlInstall -ConfigurationData $ConfigurationData -OutputPath C:\Windows\Temp\SqlInstall

Set-DscLocalConfigurationManager -Path C:\Windows\Temp\SqlInstall

Start-DscConfiguration -Verbose -Wait -Path C:\Windows\Temp\SqlInstall -Force