Create wiki pages for using dbatools

[valerymoskalenko]

Just few examples

Set Admin Account

$AdminEmail = "valery.moskalenko@ciellos.com"
$AdminUserInfo = Invoke-DbaQuery -SqlInstance localhost -Database AxDb -Query "Select top 1 name, Id, networkalias, enable, partition from Userinfo where Id = 'Admin' order by PARTITION asc" 
if ($AdminUserInfo.networkalias -ne $AdminEmail)
{
    Write-Host "Setting new Admin account to" $AdminEmail
    Set-D365Admin -AdminSignInName "valery.moskalenko@ciellos.com" -EnableException
}

Restore DB (.bak) to DEV VM

$f = Get-ChildItem C:\users\Adminc7936238e0\Downloads\AxDB_CTS-1005-BU2-202005051340.bak  #Please note that this file should be accsessable from SQL server service account
$dbName = 'AxDB_CTS1005BU2'  #Temporary Database name for new AxDB. Use a file name or any meaningfull name.
 
 
#############################################
$ErrorActionPreference = "Stop"
Write-Host "Installing or Updating PowerShell modules" -ForegroundColor Yellow
Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -Scope AllUsers
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
Install-Module -Name @('d365fo.tools','dbatools') -SkipPublisherCheck -Scope AllUsers
Update-Module -Name @('d365fo.tools','dbatools')

## Stop D365FO instance
Write-Host "Stopping D365FO environment" -ForegroundColor Yellow
Stop-D365Environment

## Restore New Database to SQL Server. Database name is AxDB_NEW
Write-Host "Restoring new Database" -ForegroundColor Yellow
#$f = Get-ChildItem C:\users\Admind9fca084f4\Downloads\AxDB_CTS-1005-BU2-202005051340.bak  #Please note that this file should be accsessable from SQL server service account
$f | Unblock-File
#$dbName = 'AxDB_CTS1005BU2'  #$f.BaseName
$f | Restore-DbaDatabase -SqlInstance localhost -DatabaseName $dbName -ReplaceDbNameInFile -Verbose
Rename-DbaDatabase -SqlInstance localhost -Database $dbName -LogicalName "$($f.BaseName)_<FT>"
 
## Backup curent AxDB just in case. You can find this DB as AxDB_original.
## You can skip this step
Write-Host "Backup current AxDB (Optional)" -ForegroundColor Yellow
Backup-DbaDatabase -SqlInstance localhost -Database AxDB -Type Full -CompressBackup -BackupFileName dbname-1005_original-backuptype-timestamp.bak -ReplaceInName

#Remove AxDB_Original database, if it exists
Write-Host "Switching databases" -ForegroundColor Yellow
Remove-D365Database -DatabaseName AxDB_original
#Switch AxDB   AxDB_original <-- AxDB <-- AxDB_NEW
Switch-D365ActiveDatabase -NewDatabaseName $dbName
 
## Enable SQL Change Tracking
Write-Host "Enabling SQL Change Tracking" -ForegroundColor Yellow
## ALTER DATABASE AxDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
Invoke-DbaQuery -SqlInstance localhost -Database AxDB -Query "ALTER DATABASE AxDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)"
 
## Disable all current Batch Jobs
Write-Host "Disabling all current Batch Jobs" -ForegroundColor Yellow
Invoke-DbaQuery -SqlInstance localhost -Database AxDB -Query "UPDATE BatchJob SET STATUS = 0 WHERE STATUS IN (1,2,5,7) --Set any waiting, executing, ready, or canceling batches to withhold."
 
## Trancate System tables. Values there will be re-created after AOS start
Write-Host "Trancating System tables. Values there will be re-created after AOS start" -ForegroundColor Yellow
$sqlSysTablesTruncate = @"
TRUNCATE TABLE SYSSERVERCONFIG
TRUNCATE TABLE SYSSERVERSESSIONS
TRUNCATE TABLE SYSCORPNETPRINTERS
TRUNCATE TABLE SYSCLIENTSESSIONS
TRUNCATE TABLE BATCHSERVERCONFIG
TRUNCATE TABLE BATCHSERVERGROUP
"@
Invoke-DbaQuery -SqlInstance localhost -Database AxDB -Query $sqlSysTablesTruncate
 
## INFO: get Admin email address/tenant
Write-Host "Getting information about tenant and admin account from AxDB" -ForegroundColor Yellow
Invoke-DbaQuery -SqlInstance localhost -Database AxDB -Query "Select ID, Name, NetworkAlias from UserInfo where ID = 'Admin'"
 
## Execute Database Sync
Write-Host "Executing Database Sync" -ForegroundColor Yellow
Invoke-D365DbSync -ShowOriginalProgress
 
## Start D365FO environment. Then open UI and refresh Data Entities. 
Write-Host "Starting D365FO environment. Then open UI and refresh Data Entities." -ForegroundColor Yellow
Start-D365Environment

Schedule Index optimization job

#region Schedule script to Optimize Indexes on Databases -->
$scriptPath = 'C:\Scripts'
$scriptName = 'Optimize-AxDB.ps1'
 
Write-Host “Installing Ola Hallengren's SQL Maintenance scripts”
Import-Module -Name dbatools
Install-DbaMaintenanceSolution -SqlInstance . -Database master
Write-Host “Running Ola Hallengren's IndexOptimize tool”
 
Write-Host "Saving Script..." -ForegroundColor Yellow
$script = @'
#region run Ola Hallengren's IndexOptimize
    $sqlIndexOptimize = "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',
        @MaxDOP = 0"

    Import-Module -Name dbatools
    Invoke-DbaQuery -SqlInstance localhost -Query $sqlIndexOptimize
#endregion run Ola Hallengren's IndexOptimize
'@

$scriptFullPath = Join-Path $scriptPath $scriptName
 
New-Item -Path $scriptPath -ItemType Directory -Force
Set-Content -Value $script -Path $scriptFullPath -Force
 
Write-Host "Running Script for the first time..." -ForegroundColor Yellow
Invoke-Expression $scriptFullPath
 
Write-Host "Registering the Script as Scheduled Task to run it Daily..." -ForegroundColor Yellow
$atStartUp =  New-JobTrigger -Daily -At "3:07 AM" -DaysInterval 1 -RandomDelay 00:40:00
$option = New-ScheduledJobOption -StartIfIdle -MultipleInstancePolicy IgnoreNew 
Register-ScheduledJob -Name AXDBOptimizationDailyTask -Trigger $atStartUp -FilePath $scriptFullPath -ScheduledJobOption $option 
#Unregister-ScheduledJob -Name AXDBOptimizationDailyTask   
 
Write-Host "Registering the Script as Scheduled Task to run it at Startup..." -ForegroundColor Yellow
$atStartUp = New-JobTrigger -AtStartup -RandomDelay 00:55:00
$option = New-ScheduledJobOption -StartIfIdle -MultipleInstancePolicy IgnoreNew 
Register-ScheduledJob -Name AXDBOptimizationStartupTask -Trigger $atStartUp -FilePath $scriptFullPath -ScheduledJobOption $option 
#Unregister-ScheduledJob -Name AXDBOptimizationStartupTask 
#endregion Schedule script to Optimize Indexes on Databases <--