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 ) $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 ) $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)'" } |