SQL Server 2016 Scripted Powershell Installation

May 29, 2019 In Microsoft Powershell SQL Server

Okay so back to some actual contributions to the community, this is going to be an ongoing Powershell series in which I show deployment of virtual machines, SQL server installations, configurations and then also migration methodologies handled via scripted manner.

After you have your virtual machine deployed (which I will cover in another post) via VMWare PowerCLI scripted deployment (VMWare in this case), installing SQL server which via the GUI is no problem. (This could also be leveraged in Azure, AWS, etc, its SQL centric outside of the VM deployment). However what if you have 100s, 1000s, to do, it also provides a predictable deployment manner as well. Core of parts of this script are roughly based around parts of the configuration.ini file – so these vary based on SQL server version. This will cover SQL Server 2016.

This script has some prompts for SQL Version (16 or 17)
CU
SP
Port Used, Instance Name, Max Memory based off installed memory and reduces it by 4096. Also sets MAXDOP, Cost Threshold, and much more.

These can be easily changed as well. They are based on central file share location, which I have mocked up as I removed my Windows share, but can be easily inputted to have the SQL binaries, CU, SP, and SSMS installer.

Make sure you have the sqlserver and dbatools powershell modules installed prior to attempting to run the script and update the locations of your SQL media.

Enjoy, much-much more to come.

<#
# Installs SQL Server 2016
#
# This script:            Installs SQL Server 2016, Applicable CU, SSMS.
# Usage:                  Run this script on the SQL Primary Server as a user with local Administrative permissions on the server
#>
  If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole(`
        [Security.Principal.WindowsBuiltInRole] “Administrator”))

    {
        Write-Warning “You do not have Admin rights to run this script!`Please elevate PoSh this script as an Administrator!”
        Break
    }

# Below variables are customizable
$servername=$env:computername
$sqlversion=Read-Host "Enter SQL Server Version, 2016 or 2017)"
$sqlspver=Read-Host "Enter SQL Service Pack Version, SP1, SP2 or SP3)"
$sqlcuver=Read-Host "Enter CU Version, CU1, CU2, CU3 etc.)"
$Port=Read-Host "SQL Port"
$folderpath="\\FILESHARELOCATION\SQL_Deploy"
$inifile="$folderpath\ConfigurationFile.ini"
# Next line sets user as a SQL sysadmin - If Applicable
$yourusername="waw.local\sql-svc"
# Path to the SQL media
$SQLsource="\\FILESHARELOCATION\$sqlversion"
$SQLInstallDrive = "C:"
# configurationfile.ini settings 
$ACTION="Install"
$ASCOLLATION="Latin1_General_CI_A"
$ErrorReporting="False"
$SUPPRESSPRIVACYSTATEMENTNOTICE="True"
$IACCEPTROPENLICENSETERMS="True"
$ENU="True"
$QUIET="True"
$QUIETSIMPLE="False"
$UpdateEnabled="True"
$USEMICROSOFTUPDATE="False"
$FEATURES="SQLENGINE,REPLICATION,FULLTEXT,CONN,IS,BC,SDK"
$UpdateSource="MU"
$HELP="False"
$INDICATEPROGRESS="False"
$X86="False"
$INSTANCENAME=Read-Host "Enter Instance Name"
$INSTALLSHAREDDIR="$SQLInstallDrive\Program Files\Microsoft SQL Server"
$INSTALLSHAREDWOWDIR="$SQLInstallDrive\Program Files (x86)\Microsoft SQL Server"
$INSTANCEID=Read-Host "Enter Instance ID"
$SQLTELSVCACCT="NT Service\SQLTELEMETRY"
$SQLTELSVCSTARTUPTYPE="Automatic"
$ISTELSVCSTARTUPTYPE="Automatic"
$ISTELSVCACCT="NT Service\SSISTELEMETRY130"
$INSTANCEDIR="$SQLInstallDrive\Program Files\Microsoft SQL Server"
$AGTSVCACCOUNT=Read-Host "Enter Service Account Username"
$AGTSVCPASSWORD=Read-Host "Enter Service Account Password"
$AGTSVCSTARTUPTYPE="Automatic"
$ISSVCSTARTUPTYPE="Automatic"
$ISSVCACCOUNT="NT AUTHORITY\System"
$COMMFABRICPORT="0"
$COMMFABRICNETWORKLEVEL="0"
$COMMFABRICENCRYPTION="0"
$MATRIXCMBRICKCOMMPORT="0"
$SQLSVCSTARTUPTYPE="Automatic"
$FILESTREAMLEVEL="1"
$ENABLERANU="False"
$SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
$SQLSVCACCOUNT="NT AUTHORITY\System"
#$SQLSVCPASSWORD="NT AUTHORITY\System"
$SQLSVCINSTANTFILEINIT="True"
$SQLSYSADMINACCOUNTS="$yourusername"
$SECURITYMODE="SQL"
$SAPWD=Read-Host "Enter SA Password"
$SQLTEMPDBFILECOUNT="4"
$SQLTEMPDBFILESIZE="1024"
$SQLTEMPDBFILEGROWTH="0"
$SQLTEMPDBLOGFILESIZE="128"
$SQLTEMPDBLOGFILEGROWTH="128"
#$SQLTEMPDBDIR="G:\TEMPDB1,G:\TEMPDB2"
$SQLTEMPDBLOGDIR="G:\TEMPDBLogs"
$INSTALLSQLDATADIR="G:\SQLDATA"
$SQLUSERDBDIR="G:\SQLDATA"
$SQLUSERDBLOGDIR="G:\SQLLogs"
$ADDCURRENTUSERASSQLADMIN="True"
$TCPENABLED="1"
$NPENABLED="1"
$BROWSERSVCSTARTUPTYPE="Automatic"
$IAcceptSQLServerLicenseTerms="True"

# do not edit below this line

$conffile= @"
[OPTIONS]
Action="$ACTION"
ErrorReporting="$ERRORREPORTING"
Quiet="$Quiet"
Features="$FEATURES"
InstanceName="$INSTANCENAME"
InstanceDir="$INSTANCEDIR"
INSTANCEID="$INSTANCEID"
SQLSVCAccount="$AGTSVCACCOUNT"
SQLSVCPASSWORD="$AGTSVCPASSWORD"
SQLSVCINSTANTFILEINIT="$SQLSVCINSTANTFILEINIT"
SECURITYMODE="$SECURITYMODE"
SQLSysAdminAccounts="$SQLSYSADMINACCOUNTS"
SQLSVCStartupType="$SQLSVCSTARTUPTYPE"
AGTSVCACCOUNT="$AGTSVCACCOUNT"
AGTSVCPASSWORD="$AGTSVCPASSWORD"
AGTSVCSTARTUPTYPE="$AGTSVCSTARTUPTYPE"
SAPWD="$SAPWD"
SQLTEMPDBDIR="G:\TEMPDB1" "G:\TEMPDB2"
SQLTEMPDBLOGDIR="$SQLTEMPDBLOGDIR"
SQLTEMPDBFILECOUNT="$SQLTEMPDBFILECOUNT"
SQLTEMPDBFILESIZE="$SQLTEMPDBFILESIZE"
SQLTEMPDBFILEGROWTH="$SQLTEMPDBFILEGROWTH"
SQLTEMPDBLOGFILESIZE="$SQLTEMPDBLOGFILESIZE"
SQLTEMPDBLOGFILEGROWTH="$SQLTEMPDBLOGFILEGROWTH"
INSTALLSQLDATADIR="$INSTALLSQLDATADIR"
SQLUSERDBDIR="$SQLUSERDBDIR"
SQLUSERDBLOGDIR="$SQLUSERDBLOGDIR"
ISSVCACCOUNT="$ISSVCACCOUNT" 
ISSVCSTARTUPTYPE="$ISSVCSTARTUPTYPE"
ASCOLLATION="$ASCOLLATION"
SQLCOLLATION="$SQLCOLLATION"
TCPENABLED="$TCPENABLED"
NPENABLED="$NPENABLED"
IAcceptSQLServerLicenseTerms="$IAcceptSQLServerLicenseTerms"
"@


# Check for Script Directory & file
if (Test-Path "$folderpath"){
 write-host "The folder '$folderpath' already exists, will not recreate it."
 } else {
mkdir "$folderpath"
}
if (Test-Path "$folderpath\ConfigurationFile.ini"){
 write-host "The file '$folderpath\ConfigurationFile.ini' already exists, removing..."
 Remove-Item -Path "$folderpath\ConfigurationFile.ini" -Force
 } else {

}
# Create file:
write-host "Creating '$folderpath\ConfigurationFile.ini'..."
New-Item -Path "$folderpath\ConfigurationFile.ini" -ItemType File -Value $Conffile



# start the SQL installer
Try
{
if (Test-Path $SQLsource){
 write-host "About to install SQL Server $sqlversion..." -nonewline
$fileExe =  "$SQLsource\setup.exe"
$CONFIGURATIONFILE = "$folderpath\ConfigurationFile.ini"
& $fileExe  /CONFIGURATIONFILE=$CONFIGURATIONFILE
Write-Host "done!" -ForegroundColor Green
 } else {
write-host "Could not find the media for SQL Server 2016..."
break
}}
catch
{write-host "Something went wrong with the installation of SQL Server 2016, aborting."
break}

#Restart the Core Engine
Restart-DbaService -ComputerName $servername -Force
Start-Sleep 30

# start the SQL Server 2016 SP downloader
$filepath="$folderpath\$sqlversion\$sqlspver\*.exe"
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2016 $sqlspver..." -nonewline
$URL = "https://download.microsoft.com/download/C/4/F/C4F908C9-98ED-4E5F-88D5-7D6A5004AEBD/SQLServer2017-KB4092643-x64.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
 else {
write-host "found the SQL Server 2016 CU Installer, no need to download it..."
}
# start the SQL Server 2016 SP installer
write-host "about to install SQL Server 2016 CU..." -nonewline
$Parms = " /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "done!" -ForegroundColor Green

# start the SQL Server 2016 CU downloader
$filepath="$folderpath\$sqlversion\$sqlspver\$sqlcuver\*.exe"
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2016 CU..." -nonewline
$URL = "https://download.microsoft.com/download/C/4/F/C4F908C9-98ED-4E5F-88D5-7D6A5004AEBD/SQLServer2017-KB4092643-x64.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
 else {
write-host "found the SQL Server 2016 CU Installer, no need to download it..."
}
# start the SQL Server 2016 CU installer
write-host "about to install SQL Server 2016 CU..." -nonewline
$Parms = " /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "done!" -ForegroundColor Green

### start the SQL SSMS downloader
$filepath="$folderpath\SSMS\SSMS-Setup-ENU_Full_17.9.1.EXE"
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2017 SSMS..." -nonewline
$URL = "https://go.microsoft.com/fwlink/?linkid=870039"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
 else {
write-host "found the SQL SSMS Installer, no need to download it..."
}
### start the SQL SSMS installer
write-host "about to install SQL Server 2017 SSMS..." -nonewline#$Parms = " /Install /Quiet /Norestart /Logs SQLServerSSMSlog.txt"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "done!" -ForegroundColor Green




##Set SQL Port
Set-DbaTcpPort -SqlInstance $servername\$instancename -Port $Port -Confirm:$false


Start-Sleep 5

Import-Module NetSecurity
 
#add new firewall rules	
New-NetFirewallRule -DisplayName 'SQL Server Port $Port' -Name 'SQL_Server Port' `
    -Description 'Allow communication for SQL Servers over specific TCP ports' `
    -Enabled True -Direction Inbound -Protocol TCP -LocalPort $Port -Action Allow -Verbose

New-NetFirewallRule -DisplayName 'SQL Server AG' -Name 'SQL_Server AG' `
    -Description 'Allow communication for SQL Servers over specific TCP ports for Availability Groups' `
    -Enabled True -Direction Inbound -Protocol TCP -LocalPort  5022 -Action Allow -Verbose
	
New-NetFirewallRule -DisplayName 'SQL Server UDP 1434' -Name 'SQL_Server UDP 1434' `
    -Description 'Allow communication for SQL Servers over specific UDP ports for SQL Browser Service' `
    -Enabled True -Direction Inbound -Protocol UDP -LocalPort  1434 -Action Allow -Verbose

#enable DTC firewall rules
Enable-NetFirewallRule -DisplayName "Distributed Transaction Coordinator (RPC)"
Enable-NetFirewallRule -DisplayName "Distributed Transaction Coordinator (RPC-EPMAP)"
Enable-NetFirewallRule -DisplayName "Distributed Transaction Coordinator (TCP-In)"


## Set Cost Threshold For Parallelism.
write-host "Set Cost Threshold For Parallelism"
Set-DbaSpConfigure $servername\$instancename -Name 'CostThresholdForParallelism' -Value 50

 
## Set maximum degree of parallelism.
write-host "Set Max Degree of Parallelism to $MaxDop"
Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'MaxDegreeOfParallelism' -Value 4


## Set Optimize For Ad-hoc Workloads.
########################################
write-host "Set Optimize For Ad-hoc Workloads"
Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'OptimizeAdhocWorkloads' -Value 1

#####################################
## SetMAX Memory
#####################################
 
#Load the SQL Server management assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
#connect to the SQL Server instance using Smo
$srv = New-Object Microsoft.SQLServer.Management.SMO.Server $servername\$instancename
 
##################################
## Set maximum memory.
########################################
 
#Get total RAM and convert it to GB
$TotalPhysicalMemory = [math]::Round($srv.PhysicalMemory/1024)
#subtract 4 GB from the total RAM
$memForSQL = $TotalPhysicalMemory - 4
#convert back to MB
$memForSQL = $memForSQL * 1024 #must be in MB
 
write-host "Set max server memory to $memForSQL"
Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'MaxServerMemory' -Value $memForSQL
##Set MIN Memory
Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'MinServerMemory' -Value 2048


##Restart Core and Agent Services
Stop-DbaService -ComputerName $servername -Type Engine -Force
Start-Sleep 20
Start-DbaService -ComputerName $servername -Type Engine
Start-DbaService -ComputerName $servername -Type Agent
Start-Sleep 20

Write-Host "Process complete" ;

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

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

# Installs SQL Server 2016

# This script: Installs SQL Server 2016, Applicable CU, SSMS.

# Usage: Run this script on the SQL Primary Server as a user with local Administrative permissions on the server

If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole(`

[Security.Principal.WindowsBuiltInRole] “Administrator”))

{

Write-Warning “You do not have Admin rights to run this script!`Please elevate PoSh this script as an Administrator!”

Break

}

# Below variables are customizable

$servername=$env:computername

$sqlversion=Read-Host "Enter SQL Server Version, 2016 or 2017)"

$sqlspver=Read-Host "Enter SQL Service Pack Version, SP1, SP2 or SP3)"

$sqlcuver=Read-Host "Enter CU Version, CU1, CU2, CU3 etc.)"

$Port=Read-Host "SQL Port"

$folderpath="\\FILESHARELOCATION\SQL_Deploy"

$inifile="$folderpath\ConfigurationFile.ini"

# Next line sets user as a SQL sysadmin - If Applicable

$yourusername="waw.local\sql-svc"

# Path to the SQL media

$SQLsource="\\FILESHARELOCATION\$sqlversion"

$SQLInstallDrive = "C:"

# configurationfile.ini settings

$ACTION="Install"

$ASCOLLATION="Latin1_General_CI_A"

$ErrorReporting="False"

$SUPPRESSPRIVACYSTATEMENTNOTICE="True"

$IACCEPTROPENLICENSETERMS="True"

$ENU="True"

$QUIET="True"

$QUIETSIMPLE="False"

$UpdateEnabled="True"

$USEMICROSOFTUPDATE="False"

$FEATURES="SQLENGINE,REPLICATION,FULLTEXT,CONN,IS,BC,SDK"

$UpdateSource="MU"

$HELP="False"

$INDICATEPROGRESS="False"

$X86="False"

$INSTANCENAME=Read-Host "Enter Instance Name"

$INSTALLSHAREDDIR="$SQLInstallDrive\Program Files\Microsoft SQL Server"

$INSTALLSHAREDWOWDIR="$SQLInstallDrive\Program Files (x86)\Microsoft SQL Server"

$INSTANCEID=Read-Host "Enter Instance ID"

$SQLTELSVCACCT="NT Service\SQLTELEMETRY"

$SQLTELSVCSTARTUPTYPE="Automatic"

$ISTELSVCSTARTUPTYPE="Automatic"

$ISTELSVCACCT="NT Service\SSISTELEMETRY130"

$INSTANCEDIR="$SQLInstallDrive\Program Files\Microsoft SQL Server"

$AGTSVCACCOUNT=Read-Host "Enter Service Account Username"

$AGTSVCPASSWORD=Read-Host "Enter Service Account Password"

$AGTSVCSTARTUPTYPE="Automatic"

$ISSVCSTARTUPTYPE="Automatic"

$ISSVCACCOUNT="NT AUTHORITY\System"

$COMMFABRICPORT="0"

$COMMFABRICNETWORKLEVEL="0"

$COMMFABRICENCRYPTION="0"

$MATRIXCMBRICKCOMMPORT="0"

$SQLSVCSTARTUPTYPE="Automatic"

$FILESTREAMLEVEL="1"

$ENABLERANU="False"

$SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

$SQLSVCACCOUNT="NT AUTHORITY\System"

#$SQLSVCPASSWORD="NT AUTHORITY\System"

$SQLSVCINSTANTFILEINIT="True"

$SQLSYSADMINACCOUNTS="$yourusername"

$SECURITYMODE="SQL"

$SAPWD=Read-Host "Enter SA Password"

$SQLTEMPDBFILECOUNT="4"

$SQLTEMPDBFILESIZE="1024"

$SQLTEMPDBFILEGROWTH="0"

$SQLTEMPDBLOGFILESIZE="128"

$SQLTEMPDBLOGFILEGROWTH="128"

#$SQLTEMPDBDIR="G:\TEMPDB1,G:\TEMPDB2"

$SQLTEMPDBLOGDIR="G:\TEMPDBLogs"

$INSTALLSQLDATADIR="G:\SQLDATA"

$SQLUSERDBDIR="G:\SQLDATA"

$SQLUSERDBLOGDIR="G:\SQLLogs"

$ADDCURRENTUSERASSQLADMIN="True"

$TCPENABLED="1"

$NPENABLED="1"

$BROWSERSVCSTARTUPTYPE="Automatic"

$IAcceptSQLServerLicenseTerms="True"

# do not edit below this line

$conffile= @"

[OPTIONS]

Action="$ACTION"

ErrorReporting="$ERRORREPORTING"

Quiet="$Quiet"

Features="$FEATURES"

InstanceName="$INSTANCENAME"

InstanceDir="$INSTANCEDIR"

INSTANCEID="$INSTANCEID"

SQLSVCAccount="$AGTSVCACCOUNT"

SQLSVCPASSWORD="$AGTSVCPASSWORD"

SQLSVCINSTANTFILEINIT="$SQLSVCINSTANTFILEINIT"

SECURITYMODE="$SECURITYMODE"

SQLSysAdminAccounts="$SQLSYSADMINACCOUNTS"

SQLSVCStartupType="$SQLSVCSTARTUPTYPE"

AGTSVCACCOUNT="$AGTSVCACCOUNT"

AGTSVCPASSWORD="$AGTSVCPASSWORD"

AGTSVCSTARTUPTYPE="$AGTSVCSTARTUPTYPE"

SAPWD="$SAPWD"

SQLTEMPDBDIR="G:\TEMPDB1" "G:\TEMPDB2"

SQLTEMPDBLOGDIR="$SQLTEMPDBLOGDIR"

SQLTEMPDBFILECOUNT="$SQLTEMPDBFILECOUNT"

SQLTEMPDBFILESIZE="$SQLTEMPDBFILESIZE"

SQLTEMPDBFILEGROWTH="$SQLTEMPDBFILEGROWTH"

SQLTEMPDBLOGFILESIZE="$SQLTEMPDBLOGFILESIZE"

SQLTEMPDBLOGFILEGROWTH="$SQLTEMPDBLOGFILEGROWTH"

INSTALLSQLDATADIR="$INSTALLSQLDATADIR"

SQLUSERDBDIR="$SQLUSERDBDIR"

SQLUSERDBLOGDIR="$SQLUSERDBLOGDIR"

ISSVCACCOUNT="$ISSVCACCOUNT"

ISSVCSTARTUPTYPE="$ISSVCSTARTUPTYPE"

ASCOLLATION="$ASCOLLATION"

SQLCOLLATION="$SQLCOLLATION"

TCPENABLED="$TCPENABLED"

NPENABLED="$NPENABLED"

IAcceptSQLServerLicenseTerms="$IAcceptSQLServerLicenseTerms"

# Check for Script Directory & file

if (Test-Path "$folderpath"){

write-host "The folder '$folderpath' already exists, will not recreate it."

} else {

mkdir "$folderpath"

}

if (Test-Path "$folderpath\ConfigurationFile.ini"){

write-host "The file '$folderpath\ConfigurationFile.ini' already exists, removing..."

Remove-Item -Path "$folderpath\ConfigurationFile.ini" -Force

} else {

}

# Create file:

write-host "Creating '$folderpath\ConfigurationFile.ini'..."

New-Item -Path "$folderpath\ConfigurationFile.ini" -ItemType File -Value $Conffile

# start the SQL installer

Try

{

if (Test-Path $SQLsource){

write-host "About to install SQL Server $sqlversion..." -nonewline

$fileExe = "$SQLsource\setup.exe"

$CONFIGURATIONFILE = "$folderpath\ConfigurationFile.ini"

& $fileExe /CONFIGURATIONFILE=$CONFIGURATIONFILE

Write-Host "done!" -ForegroundColor Green

} else {

write-host "Could not find the media for SQL Server 2016..."

break

}}

catch

{write-host "Something went wrong with the installation of SQL Server 2016, aborting."

break}

#Restart the Core Engine

Restart-DbaService -ComputerName $servername -Force

Start-Sleep 30

# start the SQL Server 2016 SP downloader

$filepath="$folderpath\$sqlversion\$sqlspver\*.exe"

if (!(Test-Path $filepath)){

write-host "Downloading SQL Server 2016 $sqlspver..." -nonewline

$URL = "https://download.microsoft.com/download/C/4/F/C4F908C9-98ED-4E5F-88D5-7D6A5004AEBD/SQLServer2017-KB4092643-x64.exe"

$clnt = New-Object System.Net.WebClient

$clnt.DownloadFile($url,$filepath)

Write-Host "done!" -ForegroundColor Green

}

else {

write-host "found the SQL Server 2016 CU Installer, no need to download it..."

}

# start the SQL Server 2016 SP installer

write-host "about to install SQL Server 2016 CU..." -nonewline

$Parms = " /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"

$Prms = $Parms.Split(" ")

& "$filepath" $Prms | Out-Null

Write-Host "done!" -ForegroundColor Green

# start the SQL Server 2016 CU downloader

$filepath="$folderpath\$sqlversion\$sqlspver\$sqlcuver\*.exe"

if (!(Test-Path $filepath)){

write-host "Downloading SQL Server 2016 CU..." -nonewline

$URL = "https://download.microsoft.com/download/C/4/F/C4F908C9-98ED-4E5F-88D5-7D6A5004AEBD/SQLServer2017-KB4092643-x64.exe"

$clnt = New-Object System.Net.WebClient

$clnt.DownloadFile($url,$filepath)

Write-Host "done!" -ForegroundColor Green

}

else {

write-host "found the SQL Server 2016 CU Installer, no need to download it..."

}

# start the SQL Server 2016 CU installer

write-host "about to install SQL Server 2016 CU..." -nonewline

$Parms = " /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"

$Prms = $Parms.Split(" ")

& "$filepath" $Prms | Out-Null

Write-Host "done!" -ForegroundColor Green

### start the SQL SSMS downloader

$filepath="$folderpath\SSMS\SSMS-Setup-ENU_Full_17.9.1.EXE"

if (!(Test-Path $filepath)){

write-host "Downloading SQL Server 2017 SSMS..." -nonewline

$URL = "https://go.microsoft.com/fwlink/?linkid=870039"

$clnt = New-Object System.Net.WebClient

$clnt.DownloadFile($url,$filepath)

Write-Host "done!" -ForegroundColor Green

}

else {

write-host "found the SQL SSMS Installer, no need to download it..."

}

### start the SQL SSMS installer

write-host "about to install SQL Server 2017 SSMS..." -nonewline#$Parms = " /Install /Quiet /Norestart /Logs SQLServerSSMSlog.txt"

$Prms = $Parms.Split(" ")

& "$filepath" $Prms | Out-Null

Write-Host "done!" -ForegroundColor Green

##Set SQL Port

Set-DbaTcpPort -SqlInstance $servername\$instancename -Port $Port -Confirm:$false

Start-Sleep 5

Import-Module NetSecurity

#add new firewall rules

New-NetFirewallRule -DisplayName 'SQL Server Port $Port' -Name 'SQL_Server Port' `

-Description 'Allow communication for SQL Servers over specific TCP ports' `

-Enabled True -Direction Inbound -Protocol TCP -LocalPort $Port -Action Allow -Verbose

New-NetFirewallRule -DisplayName 'SQL Server AG' -Name 'SQL_Server AG' `

-Description 'Allow communication for SQL Servers over specific TCP ports for Availability Groups' `

-Enabled True -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow -Verbose

New-NetFirewallRule -DisplayName 'SQL Server UDP 1434' -Name 'SQL_Server UDP 1434' `

-Description 'Allow communication for SQL Servers over specific UDP ports for SQL Browser Service' `

-Enabled True -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow -Verbose

#enable DTC firewall rules

Enable-NetFirewallRule -DisplayName "Distributed Transaction Coordinator (RPC)"

Enable-NetFirewallRule -DisplayName "Distributed Transaction Coordinator (RPC-EPMAP)"

Enable-NetFirewallRule -DisplayName "Distributed Transaction Coordinator (TCP-In)"

## Set Cost Threshold For Parallelism.

write-host "Set Cost Threshold For Parallelism"

Set-DbaSpConfigure $servername\$instancename -Name 'CostThresholdForParallelism' -Value 50

## Set maximum degree of parallelism.

write-host "Set Max Degree of Parallelism to $MaxDop"

Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'MaxDegreeOfParallelism' -Value 4

## Set Optimize For Ad-hoc Workloads.

########################################

write-host "Set Optimize For Ad-hoc Workloads"

Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'OptimizeAdhocWorkloads' -Value 1

#####################################

## SetMAX Memory

#####################################

#Load the SQL Server management assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

#connect to the SQL Server instance using Smo

$srv = New-Object Microsoft.SQLServer.Management.SMO.Server $servername\$instancename

##################################

## Set maximum memory.

########################################

#Get total RAM and convert it to GB

$TotalPhysicalMemory = [math]::Round($srv.PhysicalMemory/1024)

#subtract 4 GB from the total RAM

$memForSQL = $TotalPhysicalMemory - 4

#convert back to MB

$memForSQL = $memForSQL * 1024 #must be in MB

write-host "Set max server memory to $memForSQL"

Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'MaxServerMemory' -Value $memForSQL

##Set MIN Memory

Set-DbaSpConfigure -SqlInstance $servername\$instancename -Name 'MinServerMemory' -Value 2048

##Restart Core and Agent Services

Stop-DbaService -ComputerName $servername -Type Engine -Force

Start-Sleep 20

Start-DbaService -ComputerName $servername -Type Engine

Start-DbaService -ComputerName $servername -Type Agent

Start-Sleep 20

Write-Host "Process complete" ;

Microsoft / Powershell / SQL Server

SQL Server 2016 Scripted Powershell Installation

SQL Server 2016 Scripted Powershell Installation

Leave A Reply

Leave a Reply Cancel reply