NTS-ConfigMgrTools

1.2.5.1

NTS-ConfigMgrTools.psm1

function New-VMVolume {
    param (
        [Parameter(Mandatory = $false, HelpMessage = "enter char for volume, e.g. V")]
        [char]
        $VMDriveLetter = 'V',

        [Parameter(Mandatory = $false, HelpMessage = "enter name for storagepool , e.g. Pool01")]
        [string]
        $StoragePoolName = "Pool01",

        [Parameter(Mandatory = $false, HelpMessage = "enter name for virtual disk, e.g. VDisk01")]
        [string]
        $VirtualDiskName = "VDisk01",

        [Parameter(Mandatory = $false, HelpMessage = "enter label for the volume, e.g. VMs")]
        [string]
        $VMVolumeName = "VMs"
    )

    try {
        if ($null -eq (Get-StoragePool -FriendlyName $StoragePoolName -ErrorAction SilentlyContinue)) {
            $PhysicalDisks = Get-PhysicalDisk -CanPool $true | Where-Object -FilterScript { $PSitem.Bustype -ne "USB" }
            $NVMe_Devices = $PhysicalDisks | Where-Object -FilterScript { $PSItem.Bustype -eq "NVMe" -and $PSitem.Size -gt 256GB }
            $Non_NVMe_Devices = $PhysicalDisks | Where-Object -FilterScript { $PSItem.Bustype -ne "NVMe" }
            if ($null -ne $NVMe_Devices) {
                $SelectedDisks = $NVMe_Devices
            }
            else {
                $SelectedDisks = $Non_NVMe_Devices
            }
            $StorageSubSystemFriendlyName = (Get-StorageSubSystem -FriendlyName "*Windows*").FriendlyName
            Write-Output "create storage pool '$($StoragePoolName)'"
            New-StoragePool -StorageSubSystemFriendlyName $StorageSubSystemFriendlyName -FriendlyName $StoragePoolName -PhysicalDisks $SelectedDisks | Out-Null
            if ($null -eq (Get-VirtualDisk -FriendlyName $VirtualDiskName -ErrorAction SilentlyContinue)) {
                Write-Output "create vdisk '$($VirtualDiskName)'"
                New-VirtualDisk -StoragePoolFriendlyName $StoragePoolName -FriendlyName $VirtualDiskName -UseMaximumSize -ProvisioningType Fixed -ResiliencySettingName Simple | Out-Null
                Initialize-Disk -FriendlyName $VirtualDiskName -PartitionStyle GPT | Out-Null
                $VDiskNumber = (Get-Disk -FriendlyName $VirtualDiskName).Number
                Write-Output "create volume '$($VMVolumeName)'"
                New-Volume -DiskNumber $VDiskNumber -FriendlyName $VMVolumeName -FileSystem ReFS -DriveLetter $VMDriveLetter | Out-Null
            }
            else {
                Write-Output "Virtual disk '$($VirtualDiskName)' already exists - skipping"
            }
        }
        else {
            Write-Output "Pool '$($StoragePoolName)' already exists - skipping"
        }
    }
    catch {
        Write-Output "error during creation of vm volume: $($PSItem.Exception.Message)"
    }
}
function New-VMVSwitch {
    param (
        # Course Shortcut
        [Parameter(Mandatory = $false, HelpMessage = "enter name for the virtual switch, e.g. LAN")]
        [string]
        $Course_Shortcut = "LAN"
    )
    
    try {
        if ($null -eq (Get-VMSwitch -Name $Course_Shortcut -ErrorAction SilentlyContinue)) {
            $pNICs = Get-NetAdapter -Physical | Where-Object -Property Status -eq "UP"
            $10G_NICs = $pNICs | Where-Object -Property LinkSpeed -EQ "10 Gbps"
            $1G_NICs = $pNICs | Where-Object -Property LinkSpeed -EQ "1 Gbps"
    
            if ($10G_NICs) {
                $Selected_NIC = $10G_NICs[0]
            }
            elseif ($1G_NICs) {
                $Selected_NIC = $1G_NICs[0]
            }
            else {
                (Get-NetAdapter -Physical | Where-Object -Property Status -eq "UP")[0]
            }
    
            Write-Output "create vswitch '$($Course_Shortcut)'"
            New-VMSwitch -Name $Course_Shortcut -NetAdapterName $Selected_NIC.Name -AllowManagementOS $false | Out-Null
            Add-VMNetworkAdapter -ManagementOS -SwitchName $Course_Shortcut -Name "vNIC-$($Course_Shortcut)"
            Rename-NetAdapter -Name $Selected_NIC.Name -NewName "pNIC-$($Course_Shortcut)"
        }
        else {
            Write-Output "virtual vswitch '$($Course_Shortcut)' already exists - skipping"
        }
    }
    catch {
        Write-Output "error during creation of virtual switch: $($PSItem.Exception.Message)"
    }
}
function Register-VM_in_CM {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false, HelpMessage = "pass the VM_Config_Obj object, should be like `
        `$VM_Config_Obj = @{}
        `$VM_01 = @{
            Name = '`$Course_Shortcut)-VWIN11-`$Participant_Number)1'
            RAM = `$RAM
            CPU = `$CPUCount
            CM_Collection_Name = `$CM_Collection_W11_Autopilot
            Credentials = `$VM_Credentials
            DiskSize = `$DynamicDiskSize
            MAC = ""
        }
        `$VM_Config_Obj.Add(`$VM_01.Name, `$VM_01)")]
        [hashtable]
        $VM_Config_Obj,

        [Parameter(Mandatory = $true, HelpMessage = "specify the fqdn of the sms provider")]
        [string]
        $CM_Siteserver_FQDN,

        [Parameter(Mandatory = $true, HelpMessage = "specify credentials to access the sms provider")]
        [PSCredential]
        $CM_Credentials
    )

    if ($null -eq $VM_Config_Obj) {
        throw "no VM_Config_Obj was supplied, please specify"
    }
    if ($null -eq $CM_Siteserver_FQDN) {
        throw "no CM_Siteserver_FQDN was supplied, please specify"
    }
    if ($null -eq $CM_Credentials) {
        throw "no CM_Credentials was supplied, please specify"
    }

    Write-Output "`nstarting configmgr preparation"
    Write-Output "------"

    Confirm-VMPresence -VM_Config_Obj $VM_Config_Obj

    try {
        Invoke-Command -ComputerName $CM_Siteserver_FQDN -Credential $CM_Credentials -ScriptBlock {
            $PSDriveName = "CM-PB2"
            $VM_Config_Obj = $using:VM_Config_Obj
            $CM_Collection_All_Systems_Name = "All Systems"

            Import-Module -Name ConfigurationManager
            New-PSDrive -Name $PSDriveName -PSProvider "CMSite" -Root $using:CM_Siteserver_FQDN -Description "Primary site" | Out-Null
            Set-Location -Path "$($PSDriveName):\"

            foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
                # check destination collection existance
                if ($null -eq (Get-CMCollection -Name $VM_Config_Obj.$VM.CM_Collection_Name)) {
                    Get-CMDevice -Name $VM | Remove-CMDevice -Force -Confirm:$false
                    throw "collection '$($VM_Config_Obj.$VM.CM_Collection_Name)' does not existing, device infos for '$($VM)' was removed"
                }
            }
            
            # create vm info
            foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
                Write-Output "'$($VM)': creating vm computer info in configmgr - macaddress '$($VM_Config_Obj.$VM.MAC)'"
                Get-CMDevice -Name $VM | Remove-CMDevice -Force -Confirm:$false
            }
            Start-Sleep -Seconds 5
            foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
                Import-CMComputerInformation -CollectionName $CM_Collection_All_Systems_Name -ComputerName $VM -MacAddress $VM_Config_Obj.$VM.MAC
            }

            # check vm collection membership
            foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
                #region all systems collection
                $Device_Exists_In_AllDevices = $false
                $All_Devices_Counter = 0
                Write-Output "'$($VM)': checking collection memberships"
                while ($Device_Exists_In_AllDevices -eq $false -and $All_Devices_Counter -lt 30) {
                    $CMDevice = Get-CMDevice -Name $VM
                    if ($CMDevice.Name -eq $VM) {
                        $Device_Exists_In_AllDevices = $true
                    }
                    else {
                        Start-Sleep -Seconds 5
                        $All_Devices_Counter++
                    }
                    Write-Output "'$($VM)': device not found in collection '$($CM_Collection_All_Systems_Name)'"
                    if ($All_Devices_Counter -eq 12) {
                        Write-Output "'$($VM)': triggering membership update in Collection '$($CM_Collection_All_Systems_Name)'"
                        Start-Sleep -Seconds (10 + (Get-Random -Maximum 50 -Minimum 10))
                        Get-CMCollection -Name $CM_Collection_All_Systems_Name | Invoke-CMCollectionUpdate
                    }
                }
                if ($All_Devices_Counter -ge 30) {
                    throw "'$($VM)': could not find in the collection '$($CM_Collection_All_Systems_Name)'"
                }
                else {
                    Write-Output "'$($VM)': '$($CM_Collection_All_Systems_Name)' - found, continuing"
                }
                #endregion
            
                # create vm membership rule
                Add-CMDeviceCollectionDirectMembershipRule -CollectionName $VM_Config_Obj.$VM.CM_Collection_Name -ResourceID $CMDevice.ResourceID

                #region destination collection
                $Device_Exists_In_Specified_Collection = $false
                $Specified_Collection_Counter = 0
                while ($Device_Exists_In_Specified_Collection -eq $false -and $Specified_Collection_Counter -lt 30) {
                    $Collection_Direct_Members = Get-CMDeviceCollectionDirectMembershipRule -CollectionName $VM_Config_Obj.$VM.CM_Collection_Name | Where-Object RuleName -eq $VM
                    if ($null -ne $Collection_Direct_Members) {
                        $Device_Exists_In_Specified_Collection = $true
                    }
                    else {
                        Start-Sleep -Seconds 5
                        $Specified_Collection_Counter++
                    }
                    Write-Output "'$($VM)': device not found in collection '$($VM_Config_Obj.$VM.CM_Collection_Name)'"
                    if ($Specified_Collection_Counter -eq 20) {
                        Write-Output "'$($VM)': triggering membership update in Collection '$($VM_Config_Obj.$VM.CM_Collection_Name)'"
                        Start-Sleep -Seconds (10 + (Get-Random -Maximum 50 -Minimum 10))
                        Get-CMCollection -Name $VM_Config_Obj.$VM.CM_Collection_Name | Invoke-CMCollectionUpdate
                    }
                }
                if ($Specified_Collection_Counter -ge 30) {
                    throw "'$($VM)': could not find in the collection '$($VM_Config_Obj.$VM.CM_Collection_Name)'"
                }
                else {
                    Write-Output "'$($VM)': '$($VM_Config_Obj.$VM.CM_Collection_Name)' - found, continuing"
                }
                #endregion
            }
            Start-Sleep -Seconds 60
            Set-Location -Path $env:SystemDrive
            Remove-PSDrive -Name $PSDriveName
        }
    }
    catch {
        throw "error during registration of device infos in configmgr: $($PSItem.Exception.Message)"
    }
    $SecondsToWait = 300
    Write-Output "`nfinished configmgr preparation, now waiting $($SecondsToWait) seconds for the configmgr database updates and stabilization"
    Start-Sleep -Seconds $SecondsToWait
}
function Start-VM_Deployment {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false, HelpMessage = "pass the VM_Config_Obj object, should be like `
        `$VM_Config_Obj = @{}
        `$VM_01 = @{
            Name = '`$Course_Shortcut)-VWIN11-`$Participant_Number)1'
            RAM = `$RAM
            CPU = `$CPUCount
            CM_Collection_Name = `$CM_Collection_W11_Autopilot
            Credentials = `$VM_Credentials
            DiskSize = `$DynamicDiskSize
            MAC = ""
        }
        `$VM_Config_Obj.Add(`$VM_01.Name, `$VM_01)")]
        [hashtable]
        $VM_Config_Obj
    )

    if ($null -eq $VM_Config_Obj) {
        throw "no VM_Config_Obj was supplied, please specify"
    }

    Write-Output "`nstarting vm deployments"
    Write-Output "------"

    try {
        foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
            Write-Output "'$($VM)': starting deployment"
            Start-Sleep -Seconds 2
            Start-VM -VMName $VM
        }
    }
    catch {
        throw "error while starting vms: $($PSItem.Exception.Message)"
    }
}
function Confirm-VM_Deployment {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false, HelpMessage = "pass the VM_Config_Obj object, should be like `
        `$VM_Config_Obj = @{}
        `$VM_01 = @{
            Name = '`$Course_Shortcut)-VWIN11-`$Participant_Number)1'
            RAM = `$RAM
            CPU = `$CPUCount
            CM_Collection_Name = `$CM_Collection_W11_Autopilot
            Credentials = `$VM_Credentials
            DiskSize = `$DynamicDiskSize
            MAC = ""
        }
        `$VM_Config_Obj.Add(`$VM_01.Name, `$VM_01)")]
        [hashtable]
        $VM_Config_Obj,

        [Parameter(Mandatory = $true, HelpMessage = "specify the fqdn of the sms provider")]
        [string]
        $CM_Siteserver_FQDN,

        [Parameter(Mandatory = $true, HelpMessage = "specify credentials to access the sms provider")]
        [PSCredential]
        $CM_Credentials
    )

    if ($null -eq $VM_Config_Obj) {
        throw "no VM_Config_Obj was supplied, please specify"
    }
    if ($null -eq $CM_Siteserver_FQDN) {
        throw "no CM_Siteserver_FQDN was supplied, please specify"
    }
    if ($null -eq $CM_Credentials) {
        throw "no CM_Credentials was supplied, please specify"
    }

    Write-Output "`nchecking vm os deployment status"
    Write-Output "------"

    Confirm-VMPresence -VM_Config_Obj $VM_Config_Obj

    try {
        Invoke-Command -ComputerName $CM_Siteserver_FQDN -Credential $CM_Credentials -ScriptBlock {
            $PSDriveName = "CM-PB2"

            Import-Module -Name ConfigurationManager
            New-PSDrive -Name $PSDriveName -PSProvider "CMSite" -Root $using:CM_Siteserver_FQDN -Description "Primary site" | Out-Null
            Set-Location -Path "$($PSDriveName):\"
    
            $Deployment_Finished = $false
            $Deployment_Check_Count = 0
            $CM_Deployment_Running = @{}
            foreach ($VM in $using:VM_Config_Obj.keys | Sort-Object) {
                $CM_Deployment_Running.Add($VM, $true)
            }
    
            do {
                $Deployment_Check_Count++
                $CM_All_Deployments = Get-CMDeploymentStatus | Get-CMDeploymentStatusDetails | `
                    Where-Object -FilterScript { $using:VM_Config_Obj.keys -contains $PSItem.Devicename } | `
                    Sort-Object -Property DeviceName
                Write-Output "timestamp : $(Get-Date -format "yyyy-MM-dd_HH.mm.ss") - $($Deployment_Check_Count)"
                if ($null -ne $CM_All_Deployments) {
                    foreach ($VM_Deployment in $CM_All_Deployments) {
                        if ($VM_Deployment.StatusDescription -notlike "*The task sequence manager successfully completed execution of the task sequence*") {
                            Write-Output "'$($VM_Deployment.DeviceName)': still running - $($VM_Deployment.StatusDescription)"
                            $CM_Deployment_Running.$($VM_Deployment.Devicename) = $true
                        }
                        else {
                            Write-Output "'$($VM_Deployment.Devicename)': finished - $($VM_Deployment.StatusDescription)"
                            $CM_Deployment_Running.$($VM_Deployment.Devicename) = $false
                        }
                    }
                }
                else {
                    Write-Output "Waiting on Deployments"
                }                
                Write-Output "------"
    
                if ($CM_Deployment_Running.Values -notcontains $true) {
                    $Deployment_Finished = $true
                }
                else {
                    Start-Sleep -Seconds 30
                }
    
            } while (($Deployment_Finished -eq $false) -and $Deployment_Check_Count -lt 180 )
    
            if ($Deployment_Check_Count -ge 200) {
                throw "deployment not finished after 100 mins"
            }

            Set-Location -Path $env:SystemDrive
            Remove-PSDrive -Name $PSDriveName
        }
    }
    catch {
        throw "error while checking deployment status: $($PSItem.Exception.Message)"
    }
    Write-Output "`nfinished vm deployments"
    Write-Output "------"
}
function New-VMs_Objectbased {
    param (
        [Parameter(Mandatory = $false, HelpMessage = "pass the VM_Config_Obj object, should be like `
        `$VM_Config_Obj = @{}
        `$VM_01 = @{
            Name = '`$Course_Shortcut)-VWIN11-`$Participant_Number)1'
            RAM = `$RAM
            CPU = `$CPUCount
            CM_Collection_Name = `$CM_Collection_W11_Autopilot
            Credentials = `$VM_Credentials
            DiskSize = `$DynamicDiskSize
            MAC = ""
        }
        `$VM_Config_Obj.Add(`$VM_01.Name, `$VM_01)")]
        [hashtable]
        $VM_Config_Obj,

        [Parameter(Mandatory = $false, HelpMessage = "enter name for the virtual switch, e.g. LAN")]
        [string]
        $Course_Shortcut = 'LAN',

        [Parameter(Mandatory = $false, HelpMessage = "enter char for volume, e.g. V")]
        [char]
        $VMDriveLetter = 'V'
    )

    if ($null -eq $VM_Config_Obj) {
        throw "no VM_Config_Obj was supplied, please specify"
    }
    
    try {
        $VM_Base_Path = $VMDriveLetter + ":\VMs"
        Write-Output "`nstarting vm creation"
        Write-Output "------"
        foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
            $VMVHDXPath = ($VM_Base_Path + "\" + $VM_Config_Obj.$VM.Name + "\" + $VM_Config_Obj.$VM.Name + ".vhdx")
            Write-Output "'$($VM_Config_Obj.$VM.Name)': creating vm"
            try {
                New-VHD -Path $VMVHDXPath -SizeBytes $VM_Config_Obj.$VM.DiskSize -Dynamic | Out-Null
                New-VM -Name $VM_Config_Obj.$VM.Name -MemoryStartupBytes $VM_Config_Obj.$VM.RAM -Path $VM_Base_Path -Generation 2 -VHDPath $VMVHDXPath -BootDevice NetworkAdapter -SwitchName $Course_Shortcut | Out-Null
            }
            catch {
                throw "'$($VM_Config_Obj.$VM.Name)': error during creation of vhdx or vm - $($PSItem.Exception.Message)"
            }
            try {
                Set-VMProcessor -VMName $VM_Config_Obj.$VM.Name -Count $VM_Config_Obj.$VM.CPU
                if ((Get-VM -Name $VM_Config_Obj.$VM.Name).DynamicMemoryEnabled) {
                    Set-VM -Name $VM_Config_Obj.$VM.Name -StaticMemory
                }

                Set-VMKeyProtector -VMName $VM_Config_Obj.$VM.Name -NewLocalKeyProtector
                Enable-VMTPM -VMName $VM_Config_Obj.$VM.Name
                Set-VM -AutomaticStartAction Start -VMName $VM_Config_Obj.$VM.Name -AutomaticStartDelay 10 
                Set-VM -AutomaticStopAction ShutDown -VMName $VM_Config_Obj.$VM.Name
                Get-VMIntegrationService -VMName $VM_Config_Obj.$VM.Name | Where-Object -Property Enabled -EQ $false | Enable-VMIntegrationService
            }
            catch {
                throw "'$($VM_Config_Obj.$VM.Name)': error while setting properties - $($PSItem.Exception.Message)"
            }
            Start-VM -Name $VM_Config_Obj.$VM.Name
            Start-Sleep -Seconds 2
            Stop-VM -Name $VM_Config_Obj.$VM.Name -Force -TurnOff
            Start-Sleep -Seconds 1
        }
        foreach ($VM in $VM_Config_Obj.Keys | Sort-Object) {
            $VM_Config_Obj.$VM.MAC = (Get-VM -Name $VM_Config_Obj.$VM.Name | Get-VMNetworkAdapter).MacAddress
            Set-VMNetworkAdapter -VMName $VM_Config_Obj.$VM.Name -StaticMacAddress $VM_Config_Obj.$VM.MAC
        }
    }
    catch {
        throw "error during creation of vms: $($PSItem.Exception.Message)"
    }

    Confirm-VMPresence -VM_Config_Obj $VM_Config_Obj
}
function Test-VMConnection {
    [cmdletbinding()]
    param (
        # VM object id
        [Parameter(Mandatory = $false, HelpMessage = "specify the vmid")]
        [Guid]
        $VMId,

        # local admin credentials
        [Parameter(HelpMessage = "specify credentials for the vm")]
        [ValidateNotNullOrEmpty()]
        [PSCredential]
        $LocalAdminCreds 
    )

    if ($null -eq $VMId) {
        throw "no VMId was supplied, please specify"
    }

    $VM = Get-VM -Id $VMId
    try {
        Write-Output "------"
        if ($VM.State -eq "Off") {
            Write-Output "------"
            Write-Output "'$($VM.Name)': not running - starting"
            $VM | Start-VM -WarningAction SilentlyContinue
        }

        # Wait for the VM's heartbeat integration component to come up if it is enabled
        $HearbeatIC = (Get-VMIntegrationService -VM $VM | Where-Object Id -match "84EAAE65-2F2E-45F5-9BB5-0E857DC8EB47")
        if ($HearbeatIC -and ($HearbeatIC.Enabled -eq $true)) {
            $StartTime = Get-Date
            do {
                $WaitForMinitues = 5
                $TimeElapsed = $(Get-Date) - $StartTime
                if ($($TimeElapsed).TotalMinutes -ge 5) {
                    throw "'$($VM.Name)': integration components did not come up after $($WaitForMinitues) minutes"
                } 
                Start-Sleep -sec 1
            } 
            until ($HearbeatIC.PrimaryStatusDescription -eq "OK")
            Write-Output "'$($VM.Name)': heartbeat IC connected"
        }
        do {
            $WaitForMinitues = 5
            $TimeElapsed = $(Get-Date) - $StartTime
            Write-Output "'$($VM.Name)': testing connection"
            if ($($TimeElapsed).TotalMinutes -ge 5) {
                throw "'$($VM.Name)': could not connect to ps direct after $($WaitForMinitues) minutes"
            } 
            Start-Sleep -sec 3
            $PSReady = Invoke-Command -VMId $VMId -Credential $LocalAdminCreds -ErrorAction SilentlyContinue -ScriptBlock { $True } 
        } 
        until ($PSReady)
    }
    catch {
        Write-Output "'$($VM.Name)': $($PSItem.Exception.Message)"
        break
    }
    return $PSReady
}
function Confirm-VMPresence {
    param (
        [Parameter(Mandatory = $false, HelpMessage = "pass the VM_Config_Obj object, should be like `
        `$VM_Config_Obj = @{}
        `$VM_01 = @{
            Name = '`$Course_Shortcut)-VWIN11-`$Participant_Number)1'
            RAM = `$RAM
            CPU = `$CPUCount
            CM_Collection_Name = `$CM_Collection_W11_Autopilot
            Credentials = `$VM_Credentials
            DiskSize = `$DynamicDiskSize
            MAC = ""
        }
        `$VM_Config_Obj.Add(`$VM_01.Name, `$VM_01)")]
        [hashtable]
        $VM_Config_Obj
    )
    
    $VM_Config_Obj.Keys | ForEach-Object {
        if ($null -eq (Get-VM -Name $VM_Config_Obj.$PSItem.Name)) {
            throw "'$($VM_Config_Obj.$PSItem.Name)': could not be found"
        }
    }
}
function Get-SQLISO {
    [CmdletBinding()]
    param (
        [Parameter(
            Mandatory = $true,
            HelpMessage = "where to store iso file, C:\ISO"
        )]
        [string]
        $Outpath
    )

    if ((Test-Path -Path $Outpath) -eq $false) {
        New-Item -Path $Outpath -ItemType Directory -Force | Out-Null
    }
    $Outpath = (Get-Item -Path $Outpath).FullName

    # downloading eval setup
    try {
        Write-Output "'$($env:COMPUTERNAME)': starting download of eval setup"
        $URL = "https://download.microsoft.com/download/4/8/6/486005eb-7aa8-4128-aac0-6569782b37b0/SQL2019-SSEI-Eval.exe"
        $SQLEvalSetupPath = "SQL2019-SSEI-Eval.exe"
        New-Item -Path $Outpath -Force -ItemType Directory | Out-Null 
        Invoke-WebRequest -UseBasicParsing -Uri $URL -OutFile "$($Outpath)\$($SQLEvalSetupPath)"
    }
    catch {
        throw "error downloading eval setup: $($PSItem.Exception.Message)"
    }

    # downloading iso
    try {
        Write-Output "'$($env:COMPUTERNAME)': starting download of sql iso with eval setup"
        $Arguments = "/ACTION=Download /MEDIAPATH=$($Outpath)\ /MEDIATYPE=ISO /LANGUAGE=en-US /QUIET"
        Start-Process "$($Outpath)\$($SQLEvalSetupPath)" -ArgumentList $Arguments -Wait -NoNewWindow        
    }
    catch {
        throw "error downloading iso: $($PSItem.Exception.Message)"
    }
    Write-Output "'$($env:COMPUTERNAME)': finished download - check folder '$($Outpath)'"
}
function Initialize-SQLSetup {
    [CmdletBinding()]
    param (
        [Parameter(
            Mandatory = $true,
            HelpMessage = "where to store the files"
        )]
        [string]
        $Outpath
    )
    
    if ((Test-Path -Path $Outpath) -eq $false) {
        New-Item -Path $Outpath -ItemType Directory -Force | Out-Null
    }
    $Outpath = (Get-Item -Path $Outpath).FullName

    # checks
    $SQLFileFolder = "$($Outpath)\SQLFiles\"
    if ((Test-Path -Path $SQLFileFolder) -eq $true ) {
        throw "'$($env:COMPUTERNAME)': folder '$($SQLFileFolder)' already exits, please cleanup or skip"
    }

    # downloading iso
    try {
        Get-SQLISO -Outpath $Outpath
    }
    catch {
        throw "error during download: $($PSItem.Exception.Message)"
    }
    
    # copying files
    try {
        Write-Output "'$($env:COMPUTERNAME)': mounting iso"
        $ISO = Get-ChildItem -Path $Outpath | Where-Object -FilterScript { $PSItem.Name -like "SQL*.iso" }
        $MountedISOs = Mount-DiskImage -PassThru -ImagePath $ISO.FullName
        $Volume = Get-Volume -DiskImage $MountedISOs
        Write-Output "'$($env:COMPUTERNAME)': file '$($ISO.FullName)' mounted to '$($Volume.DriveLetter):\'"
        Write-Output "'$($env:COMPUTERNAME)': copy files from iso to $($SQLFileFolder)"
        Copy-Item -Path "$($Volume.DriveLetter):\" -Destination $SQLFileFolder -Recurse -Force
        Write-Output "'$($env:COMPUTERNAME)': finished copy job"
        Write-Output "'$($env:COMPUTERNAME)': dismounting iso"
        Dismount-DiskImage $MountedISOs.ImagePath | Out-Null
        Write-Output "'$($env:COMPUTERNAME)': dismounting finished"
    }
    catch {
        throw "error during mount or copy: $($PSItem.Exception.Message)"
    }
}
function Install-SQLADServiceAccount {
    [CmdletBinding()]
    param (
        [Parameter(
            Mandatory = $true,
            HelpMessage = "name of the group managed service account (gmsa)"
        )]
        [string]
        $SQLServiceAccount,

        [Parameter(
            Mandatory = $true,
            HelpMessage = "group with privilege to retrieve password for the account"
        )]
        [string]
        $GroupWithPermissions
    )

    if ((Get-CimInstance -ClassName Win32_ComputerSystem).PartofDomain -eq $false) {
        throw "'$($env:COMPUTERNAME)': not a member of a domain"
    }

    Install-WindowsFeature -Name RSAT-AD-PowerShell | Out-Null

    if ($null -eq (Get-ADServiceAccount -Identity $SQLServiceAccount -ErrorAction SilentlyContinue)) {
        throw "service account '$($SQLServiceAccount)$' could not be found"
    }

    # adding device to group
    try {
        Write-Output "'$($env:COMPUTERNAME)': adding to group '$($GroupWithPermissions)'"
        $Self = Get-ADComputer -Identity $env:COMPUTERNAME
        Add-ADGroupMember -Identity $GroupWithPermissions -Members $Self
    }
    catch {
        throw "not able to add to group '$($GroupWithPermissions)': $($PSItem.Exception.Message)"
    }
    
    # installing and testing service account
    try {
        Write-Output "'$($env:COMPUTERNAME)': installing '$($SQLServiceAccount)'"
        Start-Process -FilePath klist -ArgumentList "purge -lh 0 -li 0x3e7" -NoNewWindow
        Install-ADServiceAccount -Identity ($SQLServiceAccount + "$")
        if ((Test-ADServiceAccount -Identity ($SQLServiceAccount + "$")) -eq $false) {
            throw "service account '$($SQLServiceAccount)$' is not installed, please verify"
        }
    }
    catch {
        throw "could install '$($env:SQLServiceAccount)' - $($PSItem.Exception.Message)"
    }
}
function Install-SQLInstance {
    [CmdletBinding(DefaultParameterSetName = 'local')]
    param (
        [Parameter(ParameterSetName = 'local', Mandatory = $true, HelpMessage = "name of group managed service account")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $true, HelpMessage = "name of group managed service account")]
        [string]
        $Name,

        [Parameter(ParameterSetName = 'local', HelpMessage = "specify if local accounts are used")]
        [switch]
        $UseLocalAccount,

        [Parameter(ParameterSetName = 'gmsa', HelpMessage = "specify if group managed service accounts are used")]
        [switch]
        $UseGmSA,

        [Parameter(ParameterSetName = 'gmsa', Mandatory = $true, HelpMessage = "service account name for sql engine")]
        [string]
        $EngineAccountName,

        [Parameter(ParameterSetName = 'gmsa', Mandatory = $true, HelpMessage = "service account name for sql agent")]
        [string]
        $AgentAccountName,

        [Parameter(ParameterSetName = 'local', HelpMessage = "when used, the instance will use sql and windows authentication")]
        [Parameter(ParameterSetName = 'gmsa', HelpMessage = "when used, the instance will use sql and windows authentication")]
        [switch]
        $UseMixedAuth,

        [Parameter(ParameterSetName = 'local', HelpMessage = "sa pwd")]
        [Parameter(ParameterSetName = 'gmsa', HelpMessage = "sa pwd")]
        [string]
        $SAPWD,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "specifies the data directory for SQL Server data files")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "specifies the data directory for SQL Server data files")]
        [string]
        $INSTALLSQLDATADIR,
        
        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "specifies nondefault installation directory for instance-specific components")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "specifies nondefault installation directory for instance-specific components")]
        [string]
        $INSTANCEDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "backup file path for the sql database")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "backup file path for the sql database")]
        [string]
        $SQLBACKUPDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "user file path for the sql database")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "user file path for the sql database")]
        [string]
        $SQLUSERDBDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "user log file path for the sql database")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "user log file path for the sql database")]
        [string]
        $SQLUSERDBLOGDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "temp file path for the sql database")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "temp file path for the sql database")]
        [string]
        $SQLTEMPDBDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "temp log file path for the sql database")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "temp log file path for the sql database")]
        [string]
        $SQLTEMPDBLOGDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "features that should be installed, like 'SQLENGINE,FULLTEXT,CONN'")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "features that should be installed, like 'SQLENGINE,FULLTEXT,CONN'")]
        [string]
        $Features,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "accounts that should be admin on that instance")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "accounts that should be admin on that instance")]
        [string]
        $SQLSYSADMINACCOUNTS,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "minimum ram for instance")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "minimum ram for instance")]
        [string]
        $SQLMinRAM,

        [Parameter(ParameterSetName = 'local', Mandatory = $false, HelpMessage = "maximum ram for instance")]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false, HelpMessage = "maximum ram for instance")]
        [string]
        $SQLMaxRAM
    )

    # define arguments
    if ($UseLocalAccount -eq $false -and $UseGmSA -eq $false) {
        throw "you have to specify 'UseLocalAccount' or 'UseGmSA'"
    }
    if ($UseMixedAuth -eq $false -and $null -eq $SAPWD) {
        throw "you have to specify 'SAPWD' when 'UseMixedAuth' is used"
    }
    if ($uselocalaccount) {
        $AGTSVCACCOUNT = 'NT Service\SQLAgent$' + $Name
        $SQLSVCACCOUNT = 'NT Service\MSSQL$' + $Name
    }
    elseif ($UseGmSA) {
        $AGTSVCACCOUNT = $env:USERDOMAIN + "\" + (Get-ADServiceAccount -Identity $AgentAccountName).SamAccountName
        $SQLSVCACCOUNT = $env:USERDOMAIN + "\" + (Get-ADServiceAccount -Identity $EngineAccountName).SamAccountName
    }
    $SQLTELSVCACCT = 'NT Service\SQLTELEMETRY$' + $Name
    if ($Features -eq "") {
        $Features = "SQLENGINE"
    }
    if ($null -eq $SQLBACKUPDIR) {
        $SQLBACKUPDIR = $INSTALLSQLDATADIR + "\BACKUP\DATA"
    }
    if ($null -eq $SQLUSERDBDIR) {
        $SQLUSERDBDIR = $INSTALLSQLDATADIR + "\USER\DATA"
    }
    if ($null -eq $SQLUSERDBLOGDIR) {
        $SQLUSERDBLOGDIR = $INSTALLSQLDATADIR + "\USERLOG\LOG"
    }
    if ($null -eq $SQLTEMPDBDIR) {
        $SQLTEMPDBDIR = $INSTALLSQLDATADIR + "\TEMP\DATA"
    }
    if ($null -eq $SQLTEMPDBLOGDIR) {
        $SQLTEMPDBLOGDIR = $INSTALLSQLDATADIR + "\TEMPLOG\LOG"
    }
    if ($null -eq $SQLSYSADMINACCOUNTS) {
        $SQLSYSADMINACCOUNTS = ('"' + "$($env:COMPUTERNAME)\Administrator" + '"')
    }

    # check dependencies
    try {
        if($UseGmSA){
            if($env:USERDOMAIN -eq $env:COMPUTERNAME){
                throw "you are logged in with a local user, domain user required"
            }
            if ((Test-ADServiceAccount -Identity "$($EngineAccountName)$") -eq $false) {
                throw "gmsa '$($EngineAccountName)' not installed"
            }
            if ((Test-ADServiceAccount -Identity "$($AgentAccountName)$") -eq $false) {
                throw "gmsa '$($AgentAccountName)' not installed"
            }
        }
        $InstalledInstances = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' -ErrorAction SilentlyContinue).InstalledInstances
        if ($InstalledInstances -contains $Name) {
            throw "SQL instance is already installed '$($Name)'"
        }
    }
    catch {
        throw "error during prerequesits check : $($Psitem.Exception.Message)"
    }

    # start installation
    try {
        Write-Output "'$($env:COMPUTERNAME)': starting install of '$($Name)'"
        $Arguments = @(
            '/IACCEPTSQLSERVERLICENSETERMS="True"'
            '/IACCEPTPYTHONLICENSETERMS="False"'
            '/ACTION="Install"'
            '/ENU="True"'
            '/IACCEPTROPENLICENSETERMS="False"'
            '/SUPPRESSPRIVACYSTATEMENTNOTICE="False"'
            '/QUIET="True"'
            '/QUIETSIMPLE="False"'
            '/UpdateEnabled="True"'
            '/USEMICROSOFTUPDATE="False"'
            '/SUPPRESSPAIDEDITIONNOTICE="False"'
            '/UpdateSource="MU"'
            ('/FEATURES=' + $Features)
            ('/INSTANCENAME="' + $Name + '"')
            '/INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"'
            '/INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"'
            ('/INSTANCEID="' + $Name + '"')
            ('/SQLTELSVCACCT="' + $SQLTELSVCACCT + '"')
            '/SQLTELSVCSTARTUPTYPE="Automatic"'            
            ('/AGTSVCACCOUNT="' + $AGTSVCACCOUNT + '"')
            '/AGTSVCSTARTUPTYPE="Automatic"'
            '/SQLSVCSTARTUPTYPE="Automatic"'
            '/SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"'
            ('/SQLSVCACCOUNT="' + $SQLSVCACCOUNT + '"')
            ('/SQLSYSADMINACCOUNTS=' + $SQLSYSADMINACCOUNTS)
            ('/SQLBACKUPDIR="' + $SQLBACKUPDIR + '"')
            ('/SQLUSERDBDIR="' + $SQLUSERDBDIR + '"')
            ('/SQLUSERDBLOGDIR="' + $SQLUSERDBLOGDIR + '"')
            ('/SQLTEMPDBDIR="' + $SQLTEMPDBDIR + '"')
            ('/SQLTEMPDBLOGDIR="' + $SQLTEMPDBLOGDIR + '"')
            '/TCPENABLED="1"'
            '/NPENABLED="0"'
            '/BROWSERSVCSTARTUPTYPE="Automatic"'
        )
        if ($UseMixedAuth -eq $true){
            $SAArguments = @(
                '/SECURITYMODE="SQL"'
                '/SAPWD="' + $SAPWD + '"'
            )
            $Arguments = $Arguments + $SAArguments
        }
        if ($INSTALLSQLDATADIR -eq $true){
            $INSTALLSQLDATADIR_Arguments = @(
                ('/INSTALLSQLDATADIR="' + $INSTALLSQLDATADIR + '"')
            )
            $Arguments = $Arguments + $INSTALLSQLDATADIR_Arguments
        }
        if ($INSTANCEDIR -eq $true){
            $INSTANCEDIR_Arguments = @(
                ('/INSTANCEDIR="' + $INSTANCEDIR + '"')
            )
            $Arguments = $Arguments + $INSTANCEDIR_Arguments
        }
        if ($SQLMinRAM -eq $true){
            $MinRAMArguments = @(
                ('/SQLMINMEMORY="' + $SQLMinRAM + '"')
            )
            $Arguments = $Arguments + $MinRAMArguments
        }
        if ($SQLMaxRAM -eq $true){
            $MaxRAMArguments = @(
                ('/SQLMAXMEMORY="' + $SQLMaxRAM + '"')
            )
            $Arguments = $Arguments + $MaxRAMArguments
        }
        $Process = Start-Process ".\SQLFiles\setup.exe" -ArgumentList $Arguments -Wait -NoNewWindow -PassThru
        if ($Process.ExitCode -ne 0) {
            throw "check logs of sql setup - %temp%\sqlsetup*.log or C:\Program Files\Microsoft SQL Server\*\Setup Bootstrap\Log"
        }
    }
    catch {
        throw "error during installation of sql instance '$($Name)': $($Psitem.EXception.Message)"
    }
    # service customization
    try {
        if($UseGmSA){
            Write-Output "'$($env:COMPUTERNAME)': setting agent service to autostart delayed"
            $Agent = (Get-Service -Name "SQLAgent`$$($Name)").Name
            Start-Process "sc" -ArgumentList ('config "' + $($Agent) + '" start=delayed-auto') -NoNewWindow

            Write-Output "'$($env:COMPUTERNAME)': setting agent service to autostart delayed"
            $Engine = (Get-Service -Name "MSSQL`$$($Name)").Name    
            Start-Process "sc" -ArgumentList ('config "' + $($Engine) + '" start=delayed-auto') -NoNewWindow
        }
    }
    catch {
        throw "error during service customization : $($Psitem.EXception.Message)"
    }
    Write-Output "'$($env:COMPUTERNAME)': finished install of '$($Name)'"
}
function Add-VMDisk {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true, HelpMessage = "name of vm")]
        [string]
        $VMName,

        [Parameter(Mandatory = $true, HelpMessage = "path of the vhdx file")]
        [string]
        $VHDXPath,

        [Parameter(Mandatory = $false, HelpMessage = "size of the vhdx file")]
        [string]
        $VHDXSize = 80GB
    )
    Write-Host "'$($VMName)': creating disk '$($VHDXPath)'"
    New-VHD -Path $VHDXPath  -SizeBytes $VHDXSize -Dynamic | Out-Null
    Add-VMHardDiskDrive -VMName $VMName -Path $VHDXPath 
}
function Set-VMIPConfig {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $VMName,

        [Parameter(Mandatory = $true)]
        [pscredential]
        $VMCredential,

        [Parameter(Mandatory = $true)]
        [string]
        $IPAddress,

        [Parameter(Mandatory = $true)]
        [int]
        $NetPrefix,

        [Parameter(Mandatory = $true)]
        [string]
        $DefaultGateway,

        [Parameter(Mandatory = $true)]
        [string[]]
        $DNSAddresses
    )
    
    try {
        Write-Host "'$($VMName)': configuring network"
        Invoke-Command -VMName $VMName -Credential $VMCredential -ScriptBlock {
            $InterfaceObject = (Get-NetAdapter)[0]
            Write-Host "'$($env:COMPUTERNAME)': nic with mac '$($InterfaceObject.MacAddress)' was selected"
            If (($InterfaceObject | Get-NetIPConfiguration).IPv4Address.IPAddress) {
                $InterfaceObject | Remove-NetIPAddress -AddressFamily "IPv4" -Confirm:$false
            }
            If (($InterfaceObject | Get-NetIPConfiguration).Ipv4DefaultGateway) {
                $InterfaceObject | Remove-NetRoute -AddressFamily "IPv4" -Confirm:$false
            }
            Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\services\Tcpip\Parameters\Interfaces\$($InterfaceObject.InterfaceGuid)" -Name EnableDHCP -Value 0
            Start-Sleep -Seconds 2
    
            Write-Host "'$($env:COMPUTERNAME)': nic with mac '$($InterfaceObject.MacAddress)' will have ip '$($IPAddress)'"
            $InterfaceObject | New-NetIPAddress -IPAddress $using:IPAddress -AddressFamily "IPv4" -PrefixLength $using:NetPrefix -DefaultGateway $using:DefaultGateway | Out-Null
            $InterfaceObject | Set-DnsClientServerAddress -ServerAddresses $using:DNSAddresses
        }
    }
    catch {
        throw "'$($VMName)': error setting ip interface - $($PSItem.Exception.Message)"
    }
}
function Add-VMToDomain {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $VMName,

        [Parameter(Mandatory = $true)]
        [pscredential]
        $VMCredential,

        [Parameter(Mandatory = $true)]
        [string]
        $DomainName,

        [Parameter(Mandatory = $true)]
        [pscredential]
        $DomainCredential,        

        [Parameter(Mandatory = $false)]
        [string]
        $OUPath,

        [Parameter(Mandatory = $false)]
        [switch]
        $NoReboot
    )

    try {
        Confirm-VMState -VMObject (Get-VM -Name $VMName) -VMCredential $VMCredential
        Write-Output "'$($VMName)': joining domain '$($DomainName)'"
        Invoke-Command -VMName $VMName -Credential $VMCredential -ScriptBlock { 
            Start-Sleep -Seconds 1
            if ($NoReboot -eq $true) {
                if ($null -ne $OUPath) {
                    Add-Computer -Credential $using:DomainCredential -DomainName $using:DomainName -OUPath $using:OUPath -WarningAction SilentlyContinue
                }
                else {
                    Add-Computer -Credential $using:DomainCredential -DomainName $using:DomainName -WarningAction SilentlyContinue
                }
            }
            else {
                if ($null -ne $OUPath) {
                    Add-Computer -Credential $using:DomainCredential -DomainName $using:DomainName -OUPath $using:OUPath -Restart -WarningAction SilentlyContinue
                }
                else {
                    Add-Computer -Credential $using:DomainCredential -DomainName $using:DomainName -Restart -WarningAction SilentlyContinue
                }
            }
        }
        if ($NoReboot -eq $true) {
            Write-Output "'$($VMName)': domainjoin succesful - vm reboot required"
        }
        else {
            Write-Output "'$($VMName)': domainjoin succesful - vm will do reboot"
        }        
    }
    catch {
        throw "'$($VMName)': error joining to domain '$($DomainName)' - $($PSItem.Exception.Message)"
    }
}
function Confirm-VMState {
    param (
        # VM Objects
        [Parameter(Mandatory = $true)]
        [System.Object]
        $VMObject,

        [Parameter(Mandatory = $true)]
        [PSCredential]
        $VMCredential
    )
  
    if ($VMObject.State -ne "Running") {
        Start-VM -VM $VMObject
        Start-Sleep -Seconds 10
    }
    if (Test-VMConnection -VMId $VMObject.Id -LocalAdminCreds $VMCredential) {
        # Write-Output "'$($VMObject.Name)': connected succesfully"
    }
    else {
        throw "'$($VMObject.Name)': error while connecting with ps direct"
    }
}