NTS.Tools.MSSQL.psm1
|
function Get-SQLISO { <# .Description downloads sql iso from microsoft .Parameter Version version of the iso .Parameter Outpath path where the iso is stored .Example # stores the iso to $Outpath Get-SQLISO -Outpath $Outpath .NOTES downloads the sql enterprise eval setup and with it the iso #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateSet("2019", "2022")] [string] $Version, [Parameter(Mandatory = $true)] [string] $Outpath ) switch ($Version) { "2019" { $DownloadURL = "https://download.microsoft.com/download/4/8/6/486005eb-7aa8-4128-aac0-6569782b37b0/SQL2019-SSEI-Eval.exe" } "2022" { $DownloadURL = "https://download.microsoft.com/download/4/1/b/41b9a8c3-c2b4-4fcc-a3d5-62feed9e6885/SQL2022-SSEI-Eval.exe" } Default { throw "no version was selected or not supported" } } New-ItemIfNotExists -Path $Outpath -ItemType Directory $Outpath = (Get-Item -Path $Outpath).FullName $SQLEvalSetupPath = "$($Version)-SSEI-Eval.exe" $SQLEvalSetupFullPath = "$($Outpath)\$($SQLEvalSetupPath)" # downloading eval setup try { Start-FileDownload -DownloadURL $DownloadURL -FileOutPath $SQLEvalSetupFullPath } catch { throw "error downloading eval setup: $($PSItem.Exception.Message)" } # downloading iso try { $ISOPath = (Get-ChildItem -Path $Outpath | Where-Object -FilterScript { $PSItem.Name -like "SQL*.iso" }).FullName if ($null -ne $ISOPath -and (Test-Path -Path $ISOPath) -eq $true) { if ((Get-Item $ISOPath).LastWriteTime -gt (Get-Date).AddHours(-2)) { # juenger als zwei stunden # do nothing Write-Output "$($env:COMPUTERNAME): found sql iso at $($ISOPath), will use it" } else { Write-Output "$($env:COMPUTERNAME): found sql iso at $($ISOPath), removing the files because too old" Remove-Item -Path $ISOPath -Recurse -Force | Out-Null } } Write-Output "$($env:COMPUTERNAME): downloading sql iso with eval setup" $Arguments = "/ACTION=Download /MEDIAPATH=$($Outpath)\ /MEDIATYPE=ISO /LANGUAGE=en-US /QUIET" Start-Process $SQLEvalSetupFullPath -ArgumentList $Arguments -Wait -NoNewWindow Remove-Item -Path $SQLEvalSetupFullPath -Force } catch { throw "error downloading iso: $($PSItem.Exception.Message)" } Write-Output "$($env:COMPUTERNAME): finished download - check folder $($Outpath)" } function Initialize-SQLSetup { <# .Description prepares the sql installation files for i .Parameter Outpath path where the data should be cached .Example # stores the files to $TempFolderForSQL Initialize-SQLSetup -Outpath $TempFolderForSQL .NOTES downloads the sql install files and stores them extracted to the outpath #> [CmdletBinding()] param ( [Parameter(Mandatory = $false)] [ValidateSet("2019", "2022")] [string] $Version = "2022", [Parameter(Mandatory = $true)] [string] $Outpath ) New-ItemIfNotExists -Path $Outpath -ItemType Directory $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 -Version $Version -Outpath $Outpath } catch { throw "error during download - $($PSItem.Exception.Message)" } # copying files try { if ((Test-Path -Path $SQLFileFolder) -eq $true) { if ((Get-Item $SQLFileFolder).LastWriteTime -gt (Get-Date).AddHours(-2)) { # juenger als zwei stunden # do nothing Write-Output "$($env:COMPUTERNAME): found setup files at $($SQLFileFolder) will use it" } else { Write-Output "$($env:COMPUTERNAME): found setup files at $($SQLFileFolder), removing the files because too old" Remove-Item -Path $SQLFileFolder -Recurse -Force | Out-Null } } 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): copy files from iso to $($SQLFileFolder)" Copy-Item -Path "$($Volume.DriveLetter):\" -Destination $SQLFileFolder -Recurse -Force Write-Output "$($env:COMPUTERNAME): finished copy job" Dismount-DiskImage $MountedISOs.ImagePath | Out-Null Write-Output "$($env:COMPUTERNAME): iso was dismounted" } catch { throw "error during mount or copy - $($PSItem.Exception.Message)" } } function Install-SQLADServiceAccount { <# .Description adds the local server to a group with permission to the serviec account and then installs it .Parameter SQLServiceAccount name of the group managed service account .Parameter GroupWithPermissions name of the group with permissions to retrieve the password of the group managed service account .Example # this installs a gMSA Install-SQLADServiceAccount -SQLServiceAccount $SQLEngine.Name -GroupWithPermissions $SQLEngine.GroupWithPermissions .NOTES - the server needs to be member of a domain - the group managed service account must exit - the user which runs this command must have permissions to add the local device to the group #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $SQLServiceAccount, [Parameter(Mandatory = $true)] [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 -Wait Start-Sleep -Seconds 5 Start-Process -FilePath klist -ArgumentList "purge -lh 0 -li 0x3e7" -NoNewWindow -Wait Start-Sleep -Seconds 10 Install-ADServiceAccount -Identity ($SQLServiceAccount + "$") if ((Test-ADServiceAccount -Identity ($SQLServiceAccount + "$") -WarningAction SilentlyContinue) -eq $false) { throw "service account $($SQLServiceAccount)$ is not installed, please verify" } } catch { throw "could not install $($SQLServiceAccount) - $($PSItem.Exception.Message)" } } function Install-SQLInstance { <# .Description adds the local server to a group with permission to the serviec account and then installs it .Parameter Name name of group managed service account .Parameter UseLocalAccount specify if local accounts are used .Parameter UseGmSA specify if group managed service accounts are used .Parameter EngineAccountName service account name for sql engine .Parameter AgentAccountName service account name for sql agent .Parameter UseMixedAuth when used, the instance will use sql and windows authentication .Parameter SAPWD sa pwd .Parameter INSTALLSQLDATADIR specifies the data directory for SQL Server data files .Parameter INSTANCEDIR specifies nondefault installation directory for instance-specific components .Parameter SQLBACKUPDIR backup file path for the sql database .Parameter SQLUSERDBDIR user file path for the sql database .Parameter SQLUSERDBLOGDIR user log file path for the sql database .Parameter SQLTEMPDBDIR temp file path for the sql database .Parameter SQLTEMPDBLOGDIR temp log file path for the sql database .Parameter Features features that should be installed, like 'SQLENGINE,FULLTEXT,CONN' .Parameter SQLSYSADMINACCOUNTS accounts that should be admin on that instance .Parameter SQLMinRAM minimum ram for instance .Parameter SQLMaxRAM maximum ram for instance .Example # this installs an sql instance with the name PB1 which uses gmsa with customized file paths Install-SQLInstance -Name "PB1" ` -Features "SQLENGINE" ` -SQLSYSADMINACCOUNTS ('"' + "IC\T1-CMAdmin" + '" "' + "IC\T1-CMAdmins" + '"') ` -SQLBACKUPDIR "S:\SQL\PB1\BACKUP\DATA" ` -SQLUSERDBDIR "S:\SQL\PB1\USERDATA\DATA" ` -SQLUSERDBLOGDIR "S:\SQL\PB1\USERLOG\LOG" ` -SQLTEMPDBDIR "S:\SQL\PB1\TEMPDATA\DATA" ` -SQLTEMPDBLOGDIR "S:\SQL\PB1\TEMPLOG\LOG" ` -UseGmSA ` -EngineAccountName $SQLEngine ` -AgentAccountName $SQLAgent .Example # this installs an sql instance with the name INSTANCE and local accounts to default file paths Install-SQLInstance -Name "INSTANCE" -UseLocalAccount .NOTES #> [CmdletBinding(DefaultParameterSetName = 'local')] param ( [Parameter(ParameterSetName = 'local', Mandatory = $false)] [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)] [string] $SetupPath = ".\SQLFiles\setup.exe", [Parameter(ParameterSetName = 'local', Mandatory = $true)] [Parameter(ParameterSetName = 'gmsa', Mandatory = $true)] [string] $Name, [Parameter(ParameterSetName = 'local')] [switch] $UseLocalAccount, [Parameter(ParameterSetName = 'gmsa')] [switch] $UseGmSA, [Parameter(ParameterSetName = 'gmsa', Mandatory = $true)] [string] $EngineAccountName, [Parameter(ParameterSetName = 'gmsa', Mandatory = $true)] [string] $AgentAccountName, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [switch] $UseMixedAuth, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SAPWD, [Parameter(ParameterSetName = 'local', Mandatory = $false)] [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)] [string] $INSTALLSQLDATADIR, [Parameter(ParameterSetName = 'local', Mandatory = $false)] [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)] [string] $INSTANCEDIR, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLBACKUPDIR, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLUSERDBDIR, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLUSERDBLOGDIR, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLTEMPDBDIR, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLTEMPDBLOGDIR, [Parameter(ParameterSetName = 'local', Mandatory = $false)] [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)] [string] $Features, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLSYSADMINACCOUNTS, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [string] $SQLMinRAM, [Parameter(ParameterSetName = 'local')] [Parameter(ParameterSetName = 'gmsa')] [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 ($SQLSYSADMINACCOUNTS -eq "") { $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 instance $($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="' + $env:ProgramFiles + '\Microsoft SQL Server"') ('/INSTALLSHAREDWOWDIR="' + ${env:ProgramFiles(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 -ne "") { $INSTALLSQLDATADIR_Arguments = @( ('/INSTALLSQLDATADIR="' + $INSTALLSQLDATADIR + '"') ) $Arguments = $Arguments + $INSTALLSQLDATADIR_Arguments } if ($INSTANCEDIR -ne "") { $INSTANCEDIR_Arguments = @( ('/INSTANCEDIR="' + $INSTANCEDIR + '"') ) $Arguments = $Arguments + $INSTANCEDIR_Arguments } if ($SQLMinRAM -ne "") { $MinRAMArguments = @( ('/SQLMINMEMORY="' + $SQLMinRAM + '"') ) $Arguments = $Arguments + $MinRAMArguments } if ($SQLMaxRAM -ne "") { $MaxRAMArguments = @( ('/SQLMAXMEMORY="' + $SQLMaxRAM + '"') ) $Arguments = $Arguments + $MaxRAMArguments } $Process = Start-Process $SetupPath -ArgumentList $Arguments -Wait -NoNewWindow -PassThru if ($Process.ExitCode -ne 0 -and $Process.ExitCode -ne 3010) { $Message = Search-SQLSummaryLog -SearchString "Exit message" throw "check logs of sql setup - $($env:ProgramFiles)\Microsoft SQL Server\*\Setup Bootstrap\Log `nexit code: $($Process.ExitCode)`nmessage found: $($Message)" } elseif ($Process.ExitCode -eq 3010) { if (Test-RebootPending) { Write-Warning "A reboot is pending, please reboot before procceeding" } } } catch { throw "error during installation of sql instance $($Name): $($Psitem.EXception.Message)" } # service customization try { if ($UseGmSA) { Write-Output "$($env:COMPUTERNAME): configuring SQLAgent`$$($Name) 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): configuring MSSQL`$$($Name) 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 instance $($Name)" } function Set-SQLInstanceStaticPort { <# .Description configures the sql instance to use a static tcp port for all ips .Parameter InstanceName sql instance name .Parameter StaticPort port which should be used .Example # this installs an sql instance with the name PB1 which uses gmsa with customized file paths .Example # this installs an sql instance with the name INSTANCE and local accounts to default file paths Set-SQLInstanceStaticPort -InstanceName "INSTANCE" -StaticPort "1503" .NOTES Support is only available for SQL 2019 Standard / Enterprise #> param ( [Parameter(Mandatory = $true)] [string] $InstanceName, [Parameter(Mandatory = $true)] [string] $StaticPort ) $RegPath = (Resolve-Path -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL*$($InstanceName)\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\").Path if (Test-Path -Path $RegPath) { try { $SQLSettings = Get-ItemProperty -Path $RegPath if ($SQLSettings.TcpDynamicPorts -ne $StaticPort) { Write-Output "$($env:COMPUTERNAME): set tcp port of $($InstanceName) to $($StaticPort)" Set-ItemProperty -Path $RegPath -Name TcpPort -Value $StaticPort Set-ItemProperty -Path $RegPath -Name TcpDynamicPorts -Value "" } Write-Output "$($env:COMPUTERNAME): restarting $($InstanceName) to apply change" Get-Service "*$($InstanceName)*" | Restart-Service -Force -WarningAction SilentlyContinue } catch { throw "could not configure the static port on $($InstanceName) - $($PSItem.Exception.Message)" } } else { throw "could not find the registry path for the instance" } } function Test-SQLDatabaseConnection { [CmdletBinding()] param ( [Parameter(Position = 0, Mandatory = $True, ValueFromPipeline = $True)] [string] $Server, [Parameter(Position = 1, Mandatory = $false)] [string] $Database = "master", [Parameter(Position = 2, Mandatory = $True, ParameterSetName = "SQLAuth")] [pscredential] $SACredential, [Parameter(Position = 2, Mandatory = $True, ParameterSetName = "WindowsAuth")] [switch] $UseWindowsAuthentication ) if ($Server -notlike "*\*") { $Server = "$($env:COMPUTERNAME)\$($Server)" } # connect to the database, then immediatly close the connection. If an exception occurrs it indicates the conneciton was not successful. $dbConnection = New-Object System.Data.SqlClient.SqlConnection if (!$UseWindowsAuthentication) { $dbConnection.ConnectionString = "Data Source=$($Server); uid=$($SACredential.UserName); pwd=$($SACredential.GetNetworkCredential().Password); Database=$($Database);Integrated Security=False" } else { $dbConnection.ConnectionString = "Data Source=$($Server); Database=$($Database);Integrated Security=True;" } try { Measure-Command { $dbConnection.Open() } | Out-Null $Success = $true } # exceptions will be raised if the database connection failed. catch { $Success = $false } Finally { # close the database connection $dbConnection.Close() } return $Success } function Get-SQLCU { <# .Description downloads sql cu from microsoft .Parameter Version version of the cu .Parameter Outpath path where the cu is stored .Example # stores the cu to $Outpath Get-SQLISO -Version 2019_latest -Outpath $Outpath .NOTES downloads the sql enterprise eval setup and with it the iso #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateSet("2019_latest", "2022_latest")] [string] $Version, [Parameter(Mandatory = $true)] [string] $Outpath ) $ErrorActionPreference = 'Stop' if ($Outpath[-1] -eq "\") { $Outpath = $Outpath.Substring(0, $Outpath.Length - 1) } $SQLCUFilePath = "$($Outpath)\SQL-$($Version)-cu.exe" switch ($Version) { "2019_latest" { $DownloadURL = "https://www.microsoft.com/en-us/download/confirmation.aspx?id=100809" } "2022_latest" { $DownloadURL = "https://www.microsoft.com/en-us/download/confirmation.aspx?id=105013" } Default { throw "no version was selected or not supported" } } try { $Content = Invoke-WebRequest -UseBasicParsing -Uri $DownloadURL $UpdateLink = ($Content.Links | Where-Object -FilterScript { $PSItem.href -like "*download.microsoft.com*" -and $PSItem.outerHTML -like "*download manually*" }).href Start-FileDownload -DownloadURL $UpdateLink -FileOutPath $SQLCUFilePath } catch { throw "$($env:COMPUTERNAME): error getting sql cu files - $($PSItem.Exception.Message)" } Write-Output "$($env:COMPUTERNAME): finished download - check folder $($Outpath)" } function Install-SQLCU { <# .Description installs sql 2019 latest cu to all local instances .Parameter Version version string of sql cu, like 2019_latest .Parameter TempFolder folder, where files will be stored temporally .Example # this installes sql 2019 latest cu to all instances Install-SQLCU -Version "2019_latest" .NOTES #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateSet("2019_latest", "2022_latest")] [string] $Version, [Parameter(Mandatory = $false)] [string] $TempFolder = "$($env:ProgramData)\NTS\SQL\CU" ) $ErrorActionPreference = 'Stop' $SQLCUFilePath = "$($TempFolder)\SQL-$($Version)-cu.exe" $SQLCUUnpackedFilePath = "$($TempFolder)\$($Version)-cu\" try { Get-SQLCU -Version $Version -Outpath $TempFolder # unpacking sql cu to file system if ((Test-Path -Path $SQLCUFilePath) -eq $false) { throw "cannot find cu package file" } Write-Output "$($env:COMPUTERNAME): unpacking sql cu" $Arguments = "/X:$($SQLCUUnpackedFilePath)" $Process = Start-Process $SQLCUFilePath -ArgumentList $Arguments -Wait -NoNewWindow -PassThru if ($Process.ExitCode -ne 0) { throw "there was an error unpacking cu files" } } catch { throw "$($env:COMPUTERNAME): error getting sql cu files - $($PSItem.Exception.Message)" } try { # installation Write-Output "$($env:COMPUTERNAME): installing sql cu" $Arguments = "/ACTION=Patch /ALLINSTANCES /QUIET /IACCEPTSQLSERVERLICENSETERMS /ENU" $Process = Start-Process ($SQLCUUnpackedFilePath + "setup.exe") -ArgumentList $Arguments -Wait -NoNewWindow -PassThru if ($Process.ExitCode -ne 0) { $FinalResult = Search-SQLSummaryLog -SearchString "Final result" $ExitMessage = Search-SQLSummaryLog -SearchString "Exit message" if ($ExitMessage -like "*No features were updated during the setup execution. The requested features may not be installed or features are already at a higher patch level*") { Write-Output "$($env:COMPUTERNAME): no updates were applied, because already at a higher or equal patch level" } elseif ($FinalResult -like "*Passed but reboot required, see logs for details*") { Write-Output "$($env:COMPUTERNAME): sql cu install but reboot required" } else { throw "there was an error installing sql cu - $($ExitMessage)" } } # cleanup Start-FolderCleanUp -FolderToRemove $TempFolder } catch { throw "$($env:COMPUTERNAME): error sql cu installation - $($PSItem.Exception.Message) - see logs at C:\Program Files\Microsoft SQL Server\*\Setup Bootstrap\Log\" } } function Install-SQLSSMS { <# .Description this installs the latest version of sql sql management studio .Example # this installs the latest version of sql ssms Install-SQLSSMS .NOTES always installs the latest version from https://aka.ms/ssmsfullsetup #> [CmdletBinding()] param ( [Parameter(Mandatory = $false)] [ValidateSet("Latest","19.1","19.0.2","19.0.1","18.12.1","17.9.1")] [string] $Version = "Latest" ) switch ($Version) { "Latest" { $SetupURL = "https://aka.ms/ssmsfullsetup" } "19.1" { $SetupURL = "https://download.microsoft.com/download/a/c/a/aca4e29f-6925-4d50-a06b-5576c6ea629f/SSMS-Setup-ENU.exe" } "19.0.2" { $SetupURL = "https://download.microsoft.com/download/9/f/8/9f8197f4-0f71-42a3-8717-b2817c77b820/SSMS-Setup-ENU.exe" } "19.0.1" { $SetupURL = "https://download.microsoft.com/download/a/3/2/a32ae99f-b6bf-4a49-a076-e66503ccb925/SSMS-Setup-ENU.exe" } "18.12.1" { $SetupURL = "https://download.microsoft.com/download/8/a/8/8a8073d2-2e00-472b-9a18-88361d105915/SSMS-Setup-ENU.exe" } "17.9.1" { $SetupURL = "https://download.microsoft.com/download/D/D/4/DD495084-ADA7-4827-ADD3-FC566EC05B90/SSMS-Setup-ENU.exe" } Default { throw "no version was specified or not supported" } } $TempFolderPath = "$($env:ProgramData)\NTS\SQL\SSMS\" $SetupFilePath = $TempFolderPath + "SSMS-$($Version).exe" # download try { New-ItemIfNotExists -Path $TempFolderPath -ItemType Directory Write-Output "$($env:COMPUTERNAME): downloading SSMS" Start-FileDownload -DownloadURL $SetupURL -FileOutPath $SetupFilePath } catch { throw "error downloading the setup - $($PSItem.Exception.Message)" } # install try { Write-Output "$($env:COMPUTERNAME): installing SSMS" Start-Process -FilePath $SetupFilePath -ArgumentList "/quiet /norestart" -Wait -NoNewWindow Write-Output "$($env:COMPUTERNAME): finished installing SSMS" Start-FolderCleanUp -FolderToRemove $TempFolderPath } catch { throw "error installing - $($PSItem.Exception.Message)" } } function Get-SQLSSRSSetup { <# .Description this downloads the sql reporting services setup .Parameter Version version of sql reporting services setup .Parameter Outpath where should the setup file be stored .Example # this will download the setup to $SSRSTempFolder Get-SQLSSRSSetup -TempFolder $SSRSTempFolder .NOTES setup file name will be "$($TempFolder)\SQL$($Version)_ReportingServices-latest.exe" #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateSet("2022","2019")] [string] $Version, [Parameter(Mandatory = $false)] [string] $Outpath = "$($env:ProgramData)\NTS\SQL\ReportingServices" ) try { switch ($Version) { "2022" { $DownloadURL = "https://download.microsoft.com/download/8/3/2/832616ff-af64-42b5-a0b1-5eb07f71dec9/SQLServerReportingServices.exe" } "2019" { $DownloadURL = "https://download.microsoft.com/download/1/a/a/1aaa9177-3578-4931-b8f3-373b24f63342/SQLServerReportingServices.exe" } Default { throw "no version was specified or not supported" } } $SQL_RPServices_FilePath = "$($Outpath)\SQL$($Version)_ReportingServices-latest.exe" New-ItemIfNotExists -Path $Outpath -ItemType Directory # $Content = Invoke-WebRequest -UseBasicParsing -Uri $DownloadURL # $SetupLink = ($Content.Links | Where-Object -FilterScript { $PSItem.href -like "*download.microsoft.com*" -and $PSItem.outerHTML -like "*download manually*" }).href Write-Output "$($env:COMPUTERNAME): downloading sql reporting services" Start-FileDownload -DownloadURL $DownloadURL -FileOutPath $SQL_RPServices_FilePath } catch { throw "$($env:COMPUTERNAME): $($PSItem.Exception.Message)" } } function Install-SQLSSRS { <# .Description this will install sql reporting services .Parameter Version version of sql reporting services setup .Parameter SetupFilePath path to setup file .Example # this will install ssrs to the local server Install-SQLSSRS -Version 2019 .NOTES setup file name must be at "$($TempFolder)\SQL$($Version)_ReportingServices-latest.exe" #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateSet("2022","2019")] [string] $Version, [Parameter(Mandatory = $false)] [string] $SetupFilePath = "$($env:ProgramData)\NTS\SQL\ReportingServices\SQL$($Version)_ReportingServices-latest.exe", [Parameter(Mandatory = $false)] [string] $SQL_RPServices_LogPath = "$($env:ProgramData)\NTS\SQL\ReportingServices\install.log" ) try { if (Test-Path -Path $SetupFilePath) { Write-Output "$($env:COMPUTERNAME): installing sql reporting services" $Arguments = "/quiet /IAcceptLicenseTerms /Edition=Eval /norestart /log $($SQL_RPServices_LogPath)" $Process = Start-Process $SetupFilePath -ArgumentList $Arguments -Wait -NoNewWindow -PassThru if ($Process.ExitCode -eq 3010) { if (Test-RebootPending) { Write-Warning "A reboot is pending, please reboot before procceeding" } } elseif ($Process.ExitCode -ne 0 -and $Process.ExitCode -ne 3010) { throw "error installing ssrs - exit code $($Process.ExitCode) `ncheck logs at $($SQL_RPServices_LogPath)" } } else { throw "setup file not found at $($SetupFilePath)" } } catch { throw "$($env:COMPUTERNAME): - $($PSItem.Exception.Message)" } } function Initialize-SQLSSRS { <# .Description this will configure the sql reporting services .Parameter Version version of sql reporting services setup .Parameter SQL_Instance mssql instance .Parameter SSRS_ServiceAccountCredentials credentials for the service account of ssrs, must be an domain user .Parameter SQL_DB_Name name of ssrs db .Example # this will configure the ssrs to use a domain account as service Initialize-SQLSSRS -SQL_Instance "$($env:COMPUTERNAME)\$($using:CM_SQL_RPT_InstanceName)" -SSRS_ServiceAccountCredentials $using:CM_SQL_SSRS_ServiceAccountCredentials -TempFolder $SSRSTempFolder .NOTES https://blog.aelterman.com/2018/01/03/complete-automated-configuration-of-sql-server-2017-reporting-services/ https://github.com/mrsquish/AutomationScripts/blob/main/ConfigureSSRS.ps1 https://gist.github.com/SvenAelterman/f2fd058bf3a8aa6f37ac69e5d5dd2511 #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateSet("2022","2019", "2017")] [string] $Version, [Parameter(Mandatory = $true)] [string] $SQL_Instance, [Parameter(Mandatory = $true)] [pscredential] $SSRS_ServiceAccountCredentials, [Parameter(Mandatory = $false)] [string] $SQL_DB_Name = "ReportServer" ) switch ($Version) { "2022" { $WMI_Namespace = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v16\Admin" } #????? "2019" { $WMI_Namespace = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v15\Admin" } "2017" { $WMI_Namespace = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin" } Default { throw "no version was selected or the version is unsupported" } } if ($SQL_Instance -notlike "*\*") { $SQL_Instance = "$($env:COMPUTERNAME)\$($SQL_Instance)" } # test sql instance connection if ((Test-SQLDatabaseConnection -Server $SQL_Instance -UseWindowsAuthentication) -eq $false) { throw "could not connect to sql instance $($SQL_Instance) using integrated security" } try { $RSConfig = Get-WmiObject -Class "MSReportServer_ConfigurationSetting" -Namespace $WMI_Namespace If ($RSConfig.IsInitialized -eq $true) { throw "ssrs is already initialized, stopping" } Write-Output "$($env:COMPUTERNAME): configuring sql reporting services" # set service account $RSConfig = Get-WmiObject -Class "MSReportServer_ConfigurationSetting" -Namespace $WMI_Namespace $useBuiltInServiceAccount = $false Write-Output "$($env:COMPUTERNAME): configuring $($SSRS_ServiceAccountCredentials.UserName) as service account for ssrs" $RSConfig.SetWindowsServiceIdentity($useBuiltInServiceAccount, $($SSRS_ServiceAccountCredentials.UserName), $($SSRS_ServiceAccountCredentials.GetNetworkCredential().Password)) | out-null # need to reset the URLs for domain service account to work Write-Output "$($env:COMPUTERNAME): configuring http urls" $HTTPport = 80 $RSConfig.RemoveURL("ReportServerWebService", "http://+:$($HTTPport)", 1033) | out-null $RSConfig.RemoveURL("ReportServerWebApp", "http://+:$($HTTPport)", 1033) | out-null $RSConfig.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033) | out-null $RSConfig.SetVirtualDirectory("ReportServerWebApp", "Reports", 1033) | out-null $RSConfig.ReserveURL("ReportServerWebService", "http://+:$($HTTPport)", 1033) | out-null $RSConfig.ReserveURL("ReportServerWebApp", "http://+:$($HTTPport)","fesfäjhgseouzfglesö", 1033) | out-null # restart SSRS service for changes to take effect # Restart-Service -Name $RSConfig.ServiceName -Force # retrieve the current configuration $RSConfig = Get-WmiObject -Class "MSReportServer_ConfigurationSetting" -Namespace $WMI_Namespace # get the ReportServer and ReportServerTempDB creation script [string]$dbscript = $RSConfig.GenerateDatabaseCreationScript($SQL_DB_Name, 1033, $false).Script # import the SQL Server PowerShell module Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force -Scope Process Import-Module "sqlps" -DisableNameChecking | Out-Null # establish a connection to the database server # $ErrorActionPreference = 'SilentlyContinue' # $RunCount = 0 # do { Write-Output "$($env:COMPUTERNAME): connecting to instance $($SQL_Instance)" $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $SQL_Instance $conn.ApplicationName = "SSRS Configuration Script" $conn.StatementTimeout = 0 $conn.Connect() # Start-Sleep -Seconds 5 # $RunCount++ # } # while ($conn.IsOpen -eq $false -and $RunCount -lt 6) # $ErrorActionPreference = 'Continue' if ($conn.IsOpen -ne $true) { throw "could not connect to $($SQL_Instance)" } $smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $conn # create the ReportServer and ReportServerTempDB databases Write-Output "$($env:COMPUTERNAME): generating databases in instance $($SQL_Instance)" $db = $smo.Databases["master"] $db.ExecuteNonQuery($dbscript) # set permissions for the databases $dbscript = $RSConfig.GenerateDatabaseRightsScript($RSConfig.WindowsServiceIdentityConfigured, $SQL_DB_Name, $false, $true).Script $db.ExecuteNonQuery($dbscript) # set the database connection info # check hresult auf wert 0 Write-Output "$($env:COMPUTERNAME): configuring db connection for ssrs to instance $($SQL_Instance) with db $($SQL_DB_Name)" $RSConfig.SetDatabaseConnection($SQL_Instance, $SQL_DB_Name, 2, "", "") | Out-Null $RSConfig.InitializeReportServer($RSConfig.InstallationID) | Out-Null # restart services # check hresult auf wert 0 $RSConfig.SetServiceState($false, $false, $false) | Out-Null Restart-Service $RSConfig.ServiceName $RSConfig.SetServiceState($true, $true, $true) | Out-Null } catch { throw "$($env:COMPUTERNAME): $($PSItem.Exception.Message)" } } function Search-SQLSummaryLog { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $SearchString ) try { $SummaryLogFilePath = (Get-Item -Path "$($env:ProgramFiles)\Microsoft SQL Server\*\Setup Bootstrap\Log\Summary.txt" | Sort-Object -Property LastWriteTimeUtc -Descending | Select-Object -First 1).FullName $Content = Get-Content -Path $SummaryLogFilePath if ($null -eq $Content) { throw "file could not be found" } else { $FilteredContent = $Content | Select-String -Pattern $SearchString if ($null -eq $FilteredContent) { return "no message found with selected search pattern" } else { $Message = $FilteredContent[0].Tostring().Replace($SearchString, "").Trim() if ($Message -like "*:*") { $Message = $Message.Replace(":", "").Trim() } return $Message } } } catch { throw $PSItem.Exception.Message } } function Add-SQLDBRole { <# .Description this function can be used to add a role to db for an sql login .Parameter InstanceName name of the instance .Parameter SQLogin sql login name .Parameter DBName database name .Parameter DBRole role that should be granted to the sql login .Example # adds db_owner to local system on susdb Add-SQLDBRole -InstanceName "$($env:COMPUTERNAME)\$using:CM_SQL_WSUS_InstanceName" -SQLogin "NT AUTHORITY\SYSTEM" -DBName "SUSDB" -DBRole 'db_owner' .NOTES https://www.sqlservercentral.com/blogs/use-powershell-to-add-a-login-to-a-database-role-in-all-databases #> param ( [Parameter(Mandatory = $true)] [string] $InstanceName, [Parameter(Mandatory = $true)] [string] $SQLogin, [Parameter(Mandatory = $true)] [string] $DBName, [Parameter(Mandatory = $true)] [string] $DBRole ) try { # Load the SMO assembly [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | Out-Null # Connect to the instance using SMO $SQLServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName # Get the defined login - if it doesn't exist it's an error $SQLoginObject = $SQLServer.Logins[$SQLogin] if ($null -eq $SQLoginObject) { throw "$($SQLogin) is not a valid SQL Server Login on this instance." } $SQLLoginName = $SQLoginObject.Name $SQLDatabaseObject = $SQLServer.Databases[$DBName] # Check to see if the login is a user in this database $SQLUserObject = $SQLDatabaseObject.Users[$SQLLoginName] if ($null -eq $SQLUserObject) { # Not present, so add it $SQLUserObject = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($SQLDatabaseObject, $SQLLoginName) $SQLUserObject.Login = $SQLLoginName $SQLUserObject.Create() } # Check to see if the user is a member of the db_owner role if ($SQLUserObject.IsMember($DBRole) -ne $True) { # Not a member, so add that role $SQLConnection = new-object system.data.SqlClient.SqlConnection("Data Source=$($InstanceName);Integrated Security=SSPI;Initial Catalog=$($DBName)"); $SQLConnection.Open() $SQLQuery = "EXEC sp_addrolemember @rolename = N'$($DBRole)', @membername = N'$($SQLLoginName)'" $SQLCommand = new-object "System.Data.SqlClient.SqlCommand" ($SQLQuery, $SQLConnection) Write-Output "$($env:COMPUTERNAME): adding db role $($DBRole) to $($SQLogin) on instance $($InstanceName) for db $($DBName)" $SQLCommand.ExecuteNonQuery() | out-null $SQLConnection.Close() } } catch { throw "could not grant sql db role - $($PSItem.Exception.Message)" } } function Add-SQLMountPoint { <# .Description this function creates a mountpoint .Parameter DiskNumber number of disk which should be configured .Parameter SQLBasePath where should be mountpoint be created .Parameter DiskLabel label for the volume and the underlaying folders .Example # this checks if hyper-v is installed Confirm-HyperV .NOTES #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $DiskNumber, [Parameter(Mandatory = $false)] [string] $SQLBasePath = "C:\SQL\", [Parameter(Mandatory = $true)] [string] $DiskLabel ) $MountPath = $SQLBasePath + $DiskLabel Write-Output "$($env:COMPUTERNAME): formating disk for $($DiskLabel)" Set-Disk -Number $DiskNumber -IsOffline $false Set-Disk -Number $DiskNumber -IsReadOnly $false Get-Disk -Number $DiskNumber | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -UseMaximumSize | Format-Volume -NewFileSystemLabel $DiskLabel -FileSystem ReFS -AllocationUnitSize 65536 -SetIntegrityStreams $false | Out-Null New-Item -ItemType Directory -Path $MountPath | Out-Null Get-Partition -DiskNumber $DiskNumber | Add-PartitionAccessPath -AccessPath $MountPath -ErrorAction SilentlyContinue if ($DiskLabel -like "*LOG*") { New-Item -ItemType Directory -Path "$($SQLBasePath)\$($DiskLabel)\LOG" -ErrorAction SilentlyContinue | Out-Null } else { New-Item -ItemType Directory -Path "$($SQLBasePath)\$($DiskLabel)\DATA" -ErrorAction SilentlyContinue | Out-Null } Start-Sleep -Seconds 2 } |