forked from ahmetrende/PowerShell
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Install-SqlServer.ps1
261 lines (223 loc) · 10.5 KB
/
Install-SqlServer.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
<#PSScriptInfo
.SYNOPSIS
Install-SqlServer: SQL Server Unattended Installation
.DESCRIPTION
This script provides installing a SQL engine service, cumulative update and management studio to a local or remote server.
You may need to tweak the script for your environment.
Requirements:
- PowerShell 5.1 or above
- dbatools module (https://dbatools.io)
- CredSSP authentication
- Standard folder structure*
- Active directory environment
*The folder structure should be like this:
<YourSetupFolderPath>
-VersionNumber
-Iso files
-Cumulative update setup files
-Tools
-SSMS setup files.
My folder structure for SQL Server 2019
C:\Setup\2019\SqlServer2019.ISO
C:\Setup\2019\SQLServer2019-KB4548597-x64.exe
C:\Setup\Tools\SSMS-Setup-ENU.exe
.EXAMPLE
#With this example, you can install a SQL engine, cumulative update and management studio on "SqlServer01".
$CredEngine = Get-Credential
$CredSa = Get-Credential 'sa'
$Params = @{
DestinationServer = "SqlServer01"
SetupFilesPath = "C:\Setup"
Version = 2019
InstallEngine = $true
InstallCU = $true
InstallSSMS = $true
SqlCollation = "Latin1_General_CI_AS"
InstancePath = "C:\Program Files\Microsoft SQL Server"
DataPath = "C:\Data"
LogPath = "C:\Log"
TempPath = "C:\TempDB"
BackupPath = "C:\Backup"
EngineCredential = $CredEngine
AgentCredential = $CredEngine
SaCredential = $CredSa
Credential = $CredEngine
AdminAccount = "$($env:userdomain)\DBAdmin"
Restart = $true
WhatIf = $false
VerboseCommand = $false
EnableException = $true
}
Install-SqlServer @Params
.NOTES
Version : 1.2 (2020-09-15)
File Name : Install-SqlServer.ps1
Author : Ahmet Rende ([email protected])
GitHub : https://github.com/ahmetrende
#>
function Install-SqlServer {
[CmdletBinding()]
param (
[string]$DestinationServer = $env:COMPUTERNAME
,[string]$SetupFilesPath = "C:\Setup"
,[int]$Version = 2019
,[switch]$InstallEngine
,[switch]$InstallCU
,[switch]$InstallSSMS
,[string]$SqlCollation = "Latin1_General_CI_AS"
,[string]$InstancePath = "C:\Program Files\Microsoft SQL Server"
,[string]$DataPath = "C:\Data"
,[string]$LogPath = "C:\Log"
,[string]$TempPath = "C:\TempDB"
,[string]$BackupPath = "C:\Backup"
,[Parameter(Mandatory=$true)][pscredential]$EngineCredential
,[pscredential]$AgentCredential
,[Parameter(Mandatory=$true)][pscredential]$SaCredential
,[pscredential]$Credential
,[string]$AdminAccount = "$($env:userdomain)\$($env:USERNAME)"
,[switch]$Restart
,[switch]$WhatIf
,[switch]$VerboseCommand
,[switch]$EnableException
)
$ErrorActionPreference = 'Stop'
$DestinationServer = [System.Net.Dns]::GetHostByName($DestinationServer) | select -ExpandProperty HostName
Write-Host "### SQL Server Unattended Installation for [$DestinationServer] ###" -ForegroundColor Yellow
if(!$InstallEngine -and !$InstallCU -and !$InstallSSMS) {
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] No action.'") -ForegroundColor Gray
}
else {
#region Internal Params
if(!$EngineCredential) { $EngineCredential = Get-Credential }
if(!$AgentCredential) { $AgentCredential = $EngineCredential }
if(!$Credential) { $Credential = $EngineCredential }
$SetupFilesPathUnc = "\\$(([System.Net.Dns]::GetHostByName(($env:COMPUTERNAME))).HostName)\$($SetupFilesPath.Replace(':', '$'))\$Version"
$RemoteSetupFilesPath = "$($InstancePath.SubString(0,1)):\SqlServerSetup"
$RemoteSetupFilesPathUnc = "\\$DestinationServer\$RemoteSetupFilesPath" -replace ':', '$'
#endregion Internal Params
#region CredSSP
Enable-WSManCredSSP -DelegateComputer '*' -Force -Role Client > $null
Enable-WSManCredSSP -Force -Role Server > $null
Invoke-Command -ComputerName $DestinationServer -Credential $Credential -ScriptBlock {
$ErrorActionPreference = 'Stop'
Enable-WSManCredSSP -DelegateComputer '*' -Force -Role Client > $null
Enable-WSManCredSSP -Force -Role Server > $null
}
Test-WSMan -ComputerName $DestinationServer -Credential $Credential -Authentication Credssp > $null
#endregion CredSSP
#region dbatools
if (Get-Module -ListAvailable -Name dbatools) {
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] dbatools module exists. Skipping this command.'") -ForegroundColor Gray
Import-Module dbatools
}
else {
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] dbatools module does not exist. Downloading... '") -NoNewline
Install-PackageProvider -Name NuGet -Force -Confirm:$false > $null
Install-module dbatools -Force -Confirm:$false
Import-Module dbatools
Write-Host "OK" -ForegroundColor Green
}
#endregion dbatools
}
#region InstallEngine
if ($InstallEngine) {
$IsoFileName = Get-ChildItem -Path "$SetupFilesPath\$Version" -Filter "*$Version*.ISO" |
Sort-Object @{Expression = {$_.VersionInfo.ProductBuildPart}; Descending = $true} | Select-Object -First 1 -ExpandProperty Name
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] Mounting ISO file... '") -NoNewline
$mountResult = Mount-DiskImage -ImagePath "$SetupFilesPath\$Version\$IsoFileName" -PassThru
$volumeInfo = $mountResult | Get-Volume
$driveInfo = Get-PSDrive -Name $volumeInfo.DriveLetter
Write-Host "OK" -ForegroundColor Green
Start-Sleep -Seconds 1
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] Extracting ISO files to remote folder... '") -NoNewline
Remove-Item -Path ("$RemoteSetupFilesPathUnc\$IsoFileName").Replace(".ISO", "\") -Force -ErrorAction SilentlyContinue -Recurse -Confirm:$false
Copy-Item -Path $driveInfo.Root -Destination ("$RemoteSetupFilesPathUnc\$IsoFileName").Replace(".ISO", "\") -Recurse
Dismount-DiskImage -ImagePath "$SetupFilesPath\$Version\$IsoFileName"
Write-Host "OK" -ForegroundColor Green
#Custom Config
$config = @{
AGTSVCSTARTUPTYPE = "Automatic"
SQLCOLLATION = $SqlCollation
SQLTEMPDBFILESIZE = 1024
SQLTEMPDBFILEGROWTH = 512
SQLTEMPDBLOGFILESIZE = 1024
SQLTEMPDBLOGFILEGROWTH = 256
#SQLMAXDOP = 1
}
$InstallParams = @{
SqlInstance = $DestinationServer
Version = $Version
Feature = "Engine"
SaCredential = $SaCredential
Path = ("$RemoteSetupFilesPath\$IsoFileName").Replace(".ISO", "\")
DataPath = $DataPath
LogPath = $LogPath
TempPath = $TempPath
BackupPath = $BackupPath
AdminAccount = $AdminAccount
AuthenticationMode = "Mixed"
EngineCredential = $EngineCredential
AgentCredential = $AgentCredential
Credential = $Credential
PerformVolumeMaintenanceTasks = $true
Restart = $Restart
Verbose = $VerboseCommand
WhatIf = $WhatIf
InstancePath = $InstancePath
Configuration = $config
Confirm = $false
EnableException = $EnableException
UpdateSourcePath = $SetupFilesPathUnc
}
#Install Engine
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] Installing engine... '")
Install-DbaInstance @InstallParams
}
#endregion InstallEngine
#region InstallCU
if ($InstallCU) {
$CuFilePath = Get-ChildItem -Path $SetupFilesPathUnc -Filter "SQLServer$Version*" |
Sort-Object @{Expression = {$_.VersionInfo.ProductBuildPart}; Descending = $true} | Select-Object -First 1 -ExpandProperty FullName
#Install CU
$UpdateParams = @{
ComputerName = $DestinationServer
Path = $CuFilePath
Credential = $Credential
ExtractPath = $RemoteSetupFilesPath
Restart = $Restart
Verbose = $VerboseCommand
WhatIf = $WhatIf
Confirm = $false
EnableException = $EnableException
}
Get-DbaBuildReference -Update -EnableException
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] Installing CU... '")
Update-DbaInstance @UpdateParams
}
#endregion InstallCU
#region InstallSSMS
if ($InstallSSMS -and !$WhatIf) {
#Copy SSMS exe
Copy-Item -Path "$SetupFilesPath\Tools\SSMS-Setup-ENU.exe" -Destination $RemoteSetupFilesPathUnc -Force
Write-Host (Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff '[$DestinationServer] Installing SSMS... '") -NoNewline
$ArgList = "/install /quiet /norestart /log $RemoteSetupFilesPath\Log\ssms.log"
Invoke-Command -ComputerName $DestinationServer -Authentication Credssp -Credential $Credential -ScriptBlock {
Param($RemoteSetupFilesPath, $ArgList)
if(!(Test-Path -Path "$RemoteSetupFilesPath\Log\" )){New-Item -ItemType Directory -Path "$RemoteSetupFilesPath\Log" -ErrorAction SilentlyContinue > $null}
Start-Process "$RemoteSetupFilesPath\SSMS-Setup-ENU.exe" $ArgList -Wait
} -ArgumentList $RemoteSetupFilesPath, $ArgList -Verbose:$VerboseCommand
if (Get-Content -Path "$RemoteSetupFilesPathUnc\Log\ssms.log" -Tail 1 | Select-String "Exit code: 0x0, restarting" -Quiet) {
Write-Host "OK" -ForegroundColor Green
}
else {
Write-Host "Failed" -ForegroundColor Red
}
}
elseif ($InstallSSMS -and $WhatIf){
Write-Host "What if: Performing the operation `"Install SSMS`" on target `"$DestinationServer`"."
}
#endregion InstallSSMS
if($InstallEngine -or $InstallCU -or $InstallSSMS) {
Write-Host "### SQL Server Unattended Installation for [$DestinationServer] ###" -ForegroundColor Green
}
}