-
Notifications
You must be signed in to change notification settings - Fork 19
/
Copy pathPrepare-D365DevelopmentMachine.ps1
411 lines (319 loc) · 15.1 KB
/
Prepare-D365DevelopmentMachine.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
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
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
<# Prepare-D365DevelopmentMachine
#
# Preparation:
# So that the installations do not step on each other: First run windows updates, also
# wait for antimalware to run scan...otherwise this will take a long time and we do not
# want an automatic reboot to occur while this script is executing.
#
# Execute this script:
# Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('http://192.166.1.15:8000/Prepare-D365DevelopmentMachine.ps1'))
#
# Tested on Windows 10 and Windows Server 2016
# Tested on F&O 7.3 OneBox and F&O 8.1 OneBox and a 10.0.11 Azure Cloud Hosted Environment (CHE) deployed from LCS
#
# Ideas:
# Download useful SQL and PowerShell scripts, using Git?
#>
#region Install additional apps using Chocolatey
#update visual studio
Start-Process -Wait `
-FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" `
-ArgumentList 'update --passive --norestart --installpath "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional"'
#install TrudAX
$repo = "TrudAX/TRUDUtilsD365"
$releases = "https://api.github.com/repos/$repo/releases"
$path = "C:\Temp\Addin"
If (!(test-path $path)) {
New-Item -ItemType Directory -Force -Path $path
}
cd $path
Write-Host Determining latest release
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$tag = (Invoke-WebRequest -Uri $releases -UseBasicParsing | ConvertFrom-Json)[0].tag_name
$files = @("InstallToVS.exe", "TRUDUtilsD365.dll", "TRUDUtilsD365.pdb")
Write-Host Downloading files
foreach ($file in $files) {
$download = "https://github.com/$repo/releases/download/$tag/$file"
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Invoke-WebRequest $download -Out $file
Unblock-File $file
}
Start-Process "InstallToVS.exe" -Verb runAs
# Set file and folder path for SSMS installer .exe
$folderpath = "c:\windows\temp"
$filepath = "$folderpath\SSMS-Setup-ENU.exe"
#If SSMS not present, download
if (!(Test-Path $filepath)) {
write-host "Downloading SQL Server SSMS..."
$URL = "https://aka.ms/ssmsfullsetup"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url, $filepath)
Write-Host "SSMS installer download complete" -ForegroundColor Green
}
else {
write-host "Located the SQL SSMS Installer binaries, moving on to install..."
}
# start the SSMS installer
write-host "Beginning SSMS install..." -nonewline
$Parms = " /Install /Quiet /Norestart /Logs log.txt"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "SSMS installation complete" -ForegroundColor Green
#run windows update
Install-Module PSWindowsUpdate
Install-WindowsUpdate -MicrosoftUpdate -AcceptAll -AutoReboot
#endregion
If (Test-Path -Path "$env:ProgramData\Chocolatey") {
choco upgrade chocolatey -y -r
choco upgrade all --ignore-checksums -y -r
}
Else {
Write-Host "Installing Chocolatey"
Set-ExecutionPolicy Bypass -Scope Process -Force;
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072;
Invoke-Expression ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
#Determine choco executable location
# This is needed because the path variable is not updated
# This part is copied from https://chocolatey.org/install.ps1
$chocoPath = [Environment]::GetEnvironmentVariable("ChocolateyInstall")
if ($chocoPath -eq $null -or $chocoPath -eq '') {
$chocoPath = "$env:ALLUSERSPROFILE\Chocolatey"
}
if (!(Test-Path ($chocoPath))) {
$chocoPath = "$env:SYSTEMDRIVE\ProgramData\Chocolatey"
}
$chocoExePath = Join-Path $chocoPath 'bin\choco.exe'
$LargeTables = @(
#"LargeTables"
)
$packages = @(
"adobereader"
"azure-cli"
"azure-data-studio"
"azurepowershell"
"dotnetcore"
"fiddler"
"git.install"
"googlechrome"
"notepadplusplus.install"
"p4merge"
"7zip"
"postman"
"sysinternals"
"vscode"
"visualstudio-codealignment"
"vscode-azurerm-tools"
"vscode-powershell"
"winmerge"
)
# Install each program
foreach ($packageToInstall in $packages) {
Write-Host "Installing $packageToInstall" -ForegroundColor Green
& $chocoExePath "install" $packageToInstall "-y" "-r"
}
}
#endregion
#region Installing d365fo.tools
# This is requried by Find-Module, by doing it beforehand we remove some warning messages
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
# Installing d365fo.tools
If ((Find-Module -Name d365fo.tools).InstalledDate -eq $null) {
Write-Host "Installing d365fo.tools"
Write-Host " Documentation: https://github.com/d365collaborative/d365fo.tools"
Install-Module -Name d365fo.tools -SkipPublisherCheck -Scope AllUsers
}
else {
Write-Host "Updating d365fo.tools"
Update-Module -name d365fo.tools -SkipPublisherCheck -Scope AllUsers
}
Write-Host "Setting web browser homepage to the local environment"
Get-D365Url | Set-D365StartPage
Write-Host "Setting Management Reporter to manual startup to reduce churn and Event Log messages"
Get-D365Environment -FinancialReporter | Set-Service -StartupType Manual
Write-Host "Setting Windows Defender rules to speed up compilation time"
Add-D365WindowsDefenderRules -Silent
#endregion
#region Local User Policy
# Set the password to never expire
Get-WmiObject Win32_UserAccount -filter "LocalAccount=True" | ? { $_.SID -Like "S-1-5-21-*-500" } | Set-LocalUser -PasswordNeverExpires 1
# Disable changing the password
$registryPath = "HKCU:\Software\Microsoft\Windows\CurrentVersion\Policies\System"
$name = "DisableChangePassword"
$value = "1"
If (!(Test-Path $registryPath)) {
New-Item -Path $registryPath -Force | Out-Null
New-ItemProperty -Path $registryPath -Name $name -Value $value -PropertyType DWORD -Force | Out-Null
}
Else {
$passwordChangeRegKey = Get-ItemProperty -Path $registryPath -Name $Name -ErrorAction SilentlyContinue
If (-Not $passwordChangeRegKey) {
New-ItemProperty -Path $registryPath -Name $name -Value $value -PropertyType DWORD -Force | Out-Null
}
Else {
Set-ItemProperty -Path $registryPath -Name $name -Value $value
}
}
#endregion
#region Privacy
# Disable Windows Telemetry (requires a reboot to take effect)
Set-ItemProperty -Path HKLM:\SOFTWARE\Policies\Microsoft\Windows\DataCollection -Name AllowTelemetry -Type DWord -Value 0
Get-Service DiagTrack, Dmwappushservice | Stop-Service | Set-Service -StartupType Disabled
# Start Menu: Disable Bing Search Results
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Search -Name BingSearchEnabled -Type DWord -Value 0
# Start Menu: Disable Cortana
If (!(Test-Path "HKCU:\SOFTWARE\Microsoft\Personalization\Settings")) {
New-Item -Path "HKCU:\SOFTWARE\Microsoft\Personalization\Settings" -Force | Out-Null
}
Set-ItemProperty -Path "HKCU:\SOFTWARE\Microsoft\Personalization\Settings" -Name "AcceptedPrivacyPolicy" -Type DWord -Value 0
If (!(Test-Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization")) {
New-Item -Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization" -Force | Out-Null
}
Set-ItemProperty -Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization" -Name "RestrictImplicitTextCollection" -Type DWord -Value 1
Set-ItemProperty -Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization" -Name "RestrictImplicitInkCollection" -Type DWord -Value 1
If (!(Test-Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization\TrainedDataStore")) {
New-Item -Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization\TrainedDataStore" -Force | Out-Null
}
Set-ItemProperty -Path "HKCU:\SOFTWARE\Microsoft\InputPersonalization\TrainedDataStore" -Name "HarvestContacts" -Type DWord -Value 0
If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search")) {
New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search" -Force | Out-Null
}
Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search" -Name "AllowCortana" -Type DWord -Value 0
#endregion
#region Install and run Ola Hallengren's IndexOptimize
Function Execute-Sql {
Param(
[Parameter(Mandatory = $true)][string]$server,
[Parameter(Mandatory = $true)][string]$database,
[Parameter(Mandatory = $true)][string]$command
)
Process {
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$cmd.CommandText = $command
try {
$scon.Open()
$cmd.ExecuteNonQuery()
}
catch [Exception] {
Write-Warning $_.Exception.Message
}
finally {
$scon.Dispose()
$cmd.Dispose()
}
}
}
If (Test-Path "HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL") {
Write-Host "Installing dbatools PowerShell module"
Install-Module -Name dbatools -SkipPublisherCheck -Scope AllUsers
Import-Module dbatools
Set-DbaMaxMemory -SqlInstance . -Max 4096
Write-Host "Installing Ola Hallengren's SQL Maintenance scripts"
Import-Module -Name dbatools
Install-DbaMaintenanceSolution -SqlInstance . -Database master
Write-Host "Installing FirstAidResponder PowerShell module"
Install-DbaFirstResponderKit -SqlInstance . -Database master
Write-Host "Install latest CU"
$PathExists = Test-Path("C:\temp\SqlKB")
if ($PathExists -eq $false) {
mkdir "C:\temp\SqlKB"
}
$DownloadPath = "C:\temp\SqlKB"
$BuildTargets = Test-DbaBuild -SqlInstance . -MaxBehind 0CU -Update | Where-Object { !$PSItem.Compliant } | Select-Object -ExpandProperty BuildTarget -Unique
Get-DbaBuildReference -Build $BuildTargets | ForEach-Object { Save-DbaKBUpdate -Path $DownloadPath -Name $PSItem.KBLevel };
Update-DbaInstance -ComputerName . -Path $DownloadPath -Confirm:$false
$BuildTargets = Test-DbaBuild -SqlInstance . -MaxBehind 0CU -Update | Where-Object { !$PSItem.Compliant } | Select-Object -ExpandProperty BuildTarget -Unique
Get-DbaBuildReference -Build $BuildTargets | ForEach-Object { Save-DbaKBUpdate -Path $DownloadPath -Name $PSItem.KBLevel };
Update-DbaInstance -ComputerName . -Path $DownloadPath -Confirm:$false
Write-Host "Adding trace flags"
Enable-DbaTraceFlag -SqlInstance . -TraceFlag 174, 834, 1204, 1222, 1224, 2505, 7412
Write-Host "Restarting service"
Restart-DbaService -Type Engine -Force
Write-Host "Setting recovery model"
Set-DbaDbRecoveryModel -SqlInstance . -RecoveryModel Simple -Database AxDB -Confirm:$false
Write-Host "Setting database options"
$sql = "ALTER DATABASE [AxDB] SET AUTO_CLOSE OFF"
Execute-Sql -server "." -database "AxDB" -command $sql
$sql = "ALTER DATABASE [AxDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF"
Execute-Sql -server "." -database "AxDB" -command $sql
Write-Host "Setting batchservergroup options"
$sql = "delete batchservergroup where SERVERID <> 'Batch:'+@@servername
insert into batchservergroup(GROUPID, SERVERID, RECID, RECVERSION, CREATEDDATETIME, CREATEDBY)
select GROUP_, 'Batch:'+@@servername, 5900000000 + cast(CRYPT_GEN_RANDOM(4) as bigint), 1, GETUTCDATE(), '-admin-' from batchgroup
where not EXISTS (select recid from batchservergroup where batchservergroup.GROUPID = batchgroup.GROUP_)"
Execute-Sql -server "." -database "AxDB" -command $sql
Write-Host "purging disposable data"
$sql = "truncate table batchjobhistory
truncate table batchhistory
truncate table eventcud
truncate table sysdatabaselog
delete batchjob where status in (3, 4, 8)
delete batch where not exists (select recid from batchjob where batch.BATCHJOBID = BATCHJOB.recid)
EXEC sp_msforeachtable
@command1 ='truncate table ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%tmp'')'"
Execute-Sql -server "." -database "AxDB" -command $sql
Write-Host "purging staging tables data"
$sql = "EXEC sp_msforeachtable
@command1 ='truncate table ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%staging'')'"
Execute-Sql -server "." -database "AxDB" -command $sql
Write-Host "purging disposable large tables data"
$LargeTables | ForEach-Object {
$sql = "delete $_ where $_.CREATEDDATETIME < dateadd(""MM"", -2, getdate())"
Execute-Sql -server "." -database "AxDB" -command $sql
}
$sql = "DELETE [REFERENCES] FROM [REFERENCES]
JOIN Names ON (Names.Id = [REFERENCES].SourceId OR Names.Id = [REFERENCES].TargetId)
JOIN Modules ON Names.ModuleId = Modules.Id
WHERE Module LIKE '%Test%' AND Module <> 'TestEssentials'"
Execute-Sql -server "." -database "DYNAMICSXREFDB" -command $sql
Write-Host "Reclaiming freed database space"
Invoke-DbaDbShrink -SqlInstance . -Database "AxDb" -FileType Data
Invoke-DbaDbShrink -SqlInstance . -Database "AxDb", "DYNAMICSXREFDB" -FileType Data
Write-Host "Running Ola Hallengren's IndexOptimize tool"
# http://calafell.me/defragment-indexes-on-d365-finance-operations-virtual-machine/
$sql = "EXECUTE master.dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 25,
@LogToTable = 'N',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'"
Execute-Sql -server "." -database "master" -command $sql
Write-Host "Reclaiming database log space"
Invoke-DbaDbShrink -SqlInstance . -Database "AxDb", "DYNAMICSXREFDB" -FileType Log -ShrinkMethod TruncateOnly
}
Else {
Write-Verbose "SQL not installed. Skipped Ola Hallengren's index optimization"
}
#endregion
#region Update PowerShell Help, power settings
Write-Host "Updating PowerShell help"
$what = ""
Update-Help -Force -Ea 0 -Ev what
If ($what) {
Write-Warning "Minor error when updating PowerShell help"
Write-Host $what.Exception
}
# Set power settings to High Performance
Write-Host "Setting power settings to High Performance"
powercfg.exe /SetActive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c
#endregion
#region Configure Windows Updates when Windows 10
if ((Get-WmiObject Win32_OperatingSystem).Caption -Like "*Windows 10*") {
#Write-Host "Changing Windows Updates to -Notify to schedule restart-"
#Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\WindowsUpdate\UX\Settings -Name UxOption -Type DWord -Value 1
Write-Host "Disabling P2P Update downlods outside of local network"
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\DeliveryOptimization\Config -Name DODownloadMode -Type DWord -Value 1
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\DeliveryOptimization -Name SystemSettingsDownloadMode -Type DWord -Value 3
}
#endregion