Functions/Install-SdtSqlInstance.ps1
|
function Install-SdtSqlInstance { <# .SYNOPSIS This function installs and configures SQL Server on computer .DESCRIPTION This function take ServerName, SQLServiceAccount, InstanceName etc as parameters, and installl SQL Server on Server. .PARAMETER ServerName Name of the Server where SQL Services will be installed .PARAMETER Version Select appropriate Sql Server version. Available options are 2014, 2016, 2017 and 2019. .PARAMETER Edition Select appropriate Sql Edition to install. Available options are Developer, Enterprise, Standard and Express. .PARAMETER SQLServiceAccount SQL Server Service account to choose out of "Corporate\DevSQL", "Corporate\ProdSQL" and "Corporate\QASQL". By default 'Corporate\DevSQL' is passed. .PARAMETER InstanceName Name of the Instance. By default assumed to be default installation 'MSSQLSERVER'. .PARAMETER SQLServiceAccountPassword Password for SQL Service account. By Default will be fetched from SQLDBATools Inventory. .PARAMETER SAPassword Password for SA account. By Default will be fetched from SQLDBATools Inventory. .PARAMETER Administrators AD accounts that are to be made SysAdmin in SqlInstance. By default, 'Coprorate\SQL Admins' is added. .PARAMETER SetupParentFolderOnTarget Path on target Server where the SQL Server setup would be copied from Inventory. .PARAMETER ModifyConfigFile With this switch, Installation will allow to change the settings like data/log/tempdb/root directories inside ConfigurationFile.ini file before Sql Installation. .PARAMETER PerformDBAConfigurations With this switch, Post Installation, all the DBA configurations will be applied through function Set-SdtDbaConfigurations. .EXAMPLE Install-SdtSqlInstance -ServerName 'testvm' -Version 2014 -Edition 'Developer' -PerformDBAConfigurations This command will install SQL Server 2014 Developer edition as default instance on server 'testvm' with all other default parameter values. Also perform DBA configurations like adding DBA database, optimizing settings of model database etc. To perform this, a call to Set-SdtDbaConfigurations is made. .EXAMPLE Install-SdtSqlInstance -ServerName 'testvm' -Version 2014 -Edition 'Developer' -SQLServiceAccount 'Corporate\ProdSQL' This command will install SQL Server 2014 Developer edition as default instance on server 'testvm' using 'Corporate\ProdSQL' as service account with all other default parameter values. .EXAMPLE Install-SdtSqlInstance -ServerName 'testvm' -Version 2014 -Edition 'Developer' -ModifyConfigFile -Confirm:$false This command will wait for user to modify 'Configurationfile.ini' from copied target setup before proceeding with default SQL instance installation with SQL Server 2014 Developer edition. .LINK https://github.com/imajaydwivedi/SQLDBATools #> [CmdletBinding(SupportsShouldProcess=$True, ConfirmImpact='High')] Param( [Parameter(Mandatory=$true)] [Alias('ComputerName')] [String]$ServerName, [Parameter(Mandatory=$true)] [ValidateSet(2014, 2016, 2017, 2019)] [String] $Version = 2014, [Parameter(Mandatory=$true)] [ValidateSet('Developer','Enterprise','Standard','Express')] [String] $Edition = 'Developer', [Parameter(Mandatory=$false)] [ValidateSet("Corporate\DevSQL", "Corporate\ProdSQL", "Corporate\QASQL")] [string] $SQLServiceAccount = 'Corporate\DevSQL', [Parameter(Mandatory=$false)] [string] $InstanceName = 'MSSQLSERVER', [Parameter(Mandatory=$false)] [string] $SQLServiceAccountPassword, [Parameter(Mandatory=$false)] [string] $SAPassword, [Parameter(Mandatory=$false)] [string] $Administrators = 'Corporate\SQL Admins', [Parameter(Mandatory=$false)] [string] $SetupParentFolderOnTarget = 'C:\', [Parameter(Mandatory=$false)] [switch] $ModifyConfigFile, [Parameter(Mandatory=$false)] [Alias('PerformPostInstallationSteps')] [switch] $PerformDBAConfigurations, [Parameter(Mandatory=$false)] [switch] $RebootIfRequired ) if($SetupParentFolderOnTarget.EndsWith('\') -eq $false){$SetupParentFolderOnTarget += '\'}; Write-Verbose "Creating credential for SQLDBATools for PSRemoting"; $InventoryServerName = $Global:sdtInventoryInstance.Split('\')[0]; $CurrentHostName = $env:COMPUTERNAME; # File Path for Credentials & Key # Check if Host & Inventory Server are same if($InventoryServerName -eq $CurrentHostName) { $SQLDBATools = Get-Module -ListAvailable -Name SQLDBATools | Select-Object -ExpandProperty ModuleBase; $AESKeyFilePath = "$SQLDBATools\SQLDBATools_AESKey.key"; $credentialFilePath = "$SQLDBATools\SQLDBATools_Credentials.xml"; }else { $SQLDBATools = Invoke-Command -ComputerName $InventoryServerName -ScriptBlock {Get-Module -ListAvailable -Name SQLDBATools | Select-Object -ExpandProperty ModuleBase;} $AESKeyFilePath = "\\$InventoryServerName\$($SQLDBATools.Replace(':','$'))\SQLDBATools_AESKey.key"; $credentialFilePath = "\\$InventoryServerName\$($SQLDBATools.Replace(':','$'))\SQLDBATools_Credentials.xml"; } [string]$SdtSqlServerRepository = $Global:SdtSqlServerRepository; if($SdtSqlServerRepository.EndsWith('\') -eq $false){$SdtSqlServerRepository += '\'}; [string]$userName = $Global:SQLDBATools_CorporateAccount; # Create credential Object $AESKey = Get-Content $AESKeyFilePath; $pwdTxt = (Import-Clixml $credentialFilePath | Where-Object {$_.UserName -eq $userName}).Password; [SecureString]$securePwd = $pwdTxt | ConvertTo-SecureString -Key $AESKey; [PSCredential]$credentialObject = New-Object System.Management.Automation.PSCredential -ArgumentList $userName, $securePwd; Write-Verbose "Registering PSSessionConfiguration for SQLDBATools"; # Create PSSessionConfig $ScriptBlock = { $PSConfigEnabled = $false; Get-PSSessionConfiguration -Name SQLDBATools -ErrorAction SilentlyContinue | ForEach-Object {$PSConfigEnabled = $_.Enabled} | Out-Null; if($PSConfigEnabled -eq $false) { Register-PSSessionConfiguration -Name SQLDBATools -RunAsCredential $Using:credentialObject -Force -WarningAction Ignore; } } Invoke-Command -ComputerName $ServerName -ScriptBlock $ScriptBlock; Write-Verbose "Starting PSRemoting Session to perform SQL Installation"; $scriptBlock = { $VerbosePreference = $Using:VerbosePreference; $ConfirmPreference = $Using:ConfirmPreference; $WhatIfPreference = $Using:WhatIfPreference; $DebugPreference = $Using:DebugPreference; $SdtSqlServerRepository = $Using:SdtSqlServerRepository; $Version = $Using:Version; $Edition = $Using:Edition; $SetupFolder = $SdtSqlServerRepository+"$Version\$Edition"; $SQLServiceAccount = $Using:SQLServiceAccount; $SetupFolder_Local = $Using:SetupParentFolderOnTarget; $InstanceName = $Using:InstanceName; $SQLServiceAccountPassword = $Using:SQLServiceAccountPassword; $SAPassword = $Using:SAPassword; $Administrators = $Using:Administrators; # Copy Setup File Write-Verbose "Copying SQL Server setup from path '$SetupFolder' to '$SetupFolder_Local' .."; #if(-not (Test-Path -Path "$($SetupFolder_Local)$Edition\") ) { if(-not (Test-Path -Path "$SetupFolder_Local\$Version`_$Edition\") ) { #Copy-Item "$SetupFolder" -Destination "$SetupFolder_Local" -Recurse -Force; Copy-Item -Path "$SetupFolder" -Destination "$SetupFolder_Local\$Version`_$Edition\" -Recurse -Force; } $response = "YES"; if($Using:ModifyConfigFile) { Write-Output "Kindly make required changes in below Configfile: `nnotepad '\\$($env:COMPUTERNAME)\$($SetupFolder_Local.Replace(':','$'))$Version`_$Edition\ConfigurationFile.ini'`n"; $response = Read-Host "Type `"YES`" if you are done with Configuration change"; if($response -ne "YES") {$response = "NO"} } if($response -ne "YES") { Write-Output "Yes, response was not received. So exiting SQL Installation."; return; } # Start Sql Server Installation Set-Location "$($SetupFolder_Local)$Version`_$Edition\"; Write-Verbose "Starting SQL Server setup from path '$($SetupFolder_Local)$Version`_$Edition\' .."; Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process | Out-Null; . .\AutoBuild.ps1; AutoBuild -SQLServiceAccount $SQLServiceAccount -InstanceName $InstanceName -SQLServiceAccountPassword $SQLServiceAccountPassword -SAPassword $SAPassword -Administrators $Administrators; } if($PSCmdlet.ShouldProcess("$ServerName")) { Invoke-Command -ComputerName $ServerName -ScriptBlock $scriptBlock -ConfigurationName SQLDBATools -ErrorVariable err; } Write-Verbose "Installation portion ended."; if($RebootIfRequired) { Write-Verbose "Rebooting server $ServerName using Set-SdtServerState"; Set-SdtServerState -ServerName $ServerName -Reboot -Force; $SQLServiceName = if($InstanceName -eq 'MSSQLSERVER'){'MSSQLSERVER'}ELSE{"MSSQL`$$InstanceName"} $EndTime = (Get-Date).AddMinutes(10); Do { Start-Sleep -Seconds 20; try { $SQLService = Get-WmiObject win32_service -ComputerName $ServerName -Filter "name='$SQLServiceName'" -ErrorAction Stop -ErrorVariable err; } catch { if($err.ErrorRecord.Exception.Message.Contains('The RPC server is unavailable')){ Write-Verbose "Server is stil rebooting"; } } } while($EndTime.CompareTo([System.DateTime]::Now) -gt 0 -and [string]::IsNullOrEmpty($SQLService)); # Wait for 5 minutes Write-Verbose "Server is up again after reboot" } if($PerformDBAConfigurations) { Write-Verbose "Proceeding for Post Instalaltion Configurations using Set-SdtDbaConfigurations"; Set-SdtDbaConfigurations -SqlInstance $SqlInstance } if($RebootIfRequired) { Write-Verbose "Rebooting server $ServerName using Set-SdtServerState"; Set-SdtServerState -ServerName $ServerName -Reboot -Force; $SQLServiceName = if($InstanceName -eq 'MSSQLSERVER'){'MSSQLSERVER'}ELSE{"MSSQL`$$InstanceName"} $EndTime = (Get-Date).AddMinutes(10); Do { Start-Sleep -Seconds 20; try { $SQLService = Get-WmiObject win32_service -ComputerName $ServerName -Filter "name='$SQLServiceName'" -ErrorAction Stop -ErrorVariable err; } catch { if($err.ErrorRecord.Exception.Message.Contains('The RPC server is unavailable')){ Write-Verbose "Server is stil rebooting"; } } } while($EndTime.CompareTo([System.DateTime]::Now) -gt 0 -and [string]::IsNullOrEmpty($SQLService)); # Wait for 5 minutes Write-Verbose "Server is up again after reboot" Write-Output "Configuration of server is complete"; } } |