Installer SQL Server med dbatools.io

Jeg er primært SQL - udvikler og det er også det jeg brænder for, Jeg kan godt finde ud af at installere en SQL Server og ”administrere” den, men for mig ligger det sjoveste i udvikling af nyt.

Når jeg så har udsigten til at skulle installere SQL Server på flere nye servere så kommer min indre ”dovne” udvikler frem og tænker, det må da kunne scriptes, så det kræver minimal indsats 😊 - Og fordelen ved at scripte installationer er også at det gøres ens og man ikke lige bliver forstyrret og glemmer et step.

Jeg har tidligere lavet noget i PowerShell til denne del, men jeg syntes hurtigt det bliver et stort og uoverskueligt script, jeg kan godt lide at scripts, eller kode generelt, er let at læse og forstå. Så denne gang kastede jeg mig over dbatools.io.

Dette er også PowerShell script, eller module, men her skal jeg "bare" importere andres arbejde og konfigurere det.

Med muligheden for igen at lære lidt nyt kastede jeg mig ud i at give det et forsøg 🤓  

Det bliver hurtig tydeligt for mig at med dbatools.io så kan jeg lave et script hvor jeg både kan installere og opsætte SQL Servere – Jeg syntes selv det er ret lige til at forstå hvad der sker i scriptet. Det eneste det kræver er at jeg har en iso file med installeren, i den rette SQL version og så er det bare at trykke execute 💪  

I dette script installeres SQL server version 2019, der tilføjes både en SQL admin gruppe og sa konto. Sql Serveren sættes op med traceflags og generel opsætning, Ola’s script installeres og sættes op (backupdelen fjernes da den tages af andet værktøj hos os).

if (Get-Module -ListAvailable -Name dbatools) {
    Write-Host "dbatools exists" -ForegroundColor Green
    Import-Module dbatools
} 
else {
    Install-Module dbatools
}


##Path to the install iso##
Mount-DiskImage -ImagePath "D:\Installfiles\SQLServer2019-x64-ENU-Dev.iso" -Confirm:$false


$instancename = 'MSSQLSERVER'
$computername = $env:COMPUTERNAME 
$full_sql_name = "$computername\$instancename"
$InstanceMaxMemory = 20000


##Service account to run the SQL server and agent service##
##Write password##
$svcAcc = Get-Credential Domain\sql_svc


$Password = ConvertTo-SecureString 'TopSecretPass49' -AsPlainText -Force
$saCredential = New-Object System.Management.Automation.PSCredential("sa",$Password)



$config = @{
      
    ##TEMPDB
    SQLTEMPDBFILECOUNT="8"
    SQLTEMPDBFILESIZE="2024"
    SQLTEMPDBFILEGROWTH="1024"   
    SQLTEMPDBLOGFILESIZE="1024"
    SQLTEMPDBLOGFILEGROWTH="1024"


    ##CONFIG
    SQLMAXDOP="0"
    TCPENABLED="1"
    SQLMAXMEMORY=$InstanceMaxMemory
} 



Install-DbaInstance -Version 2019 `
                    -Feature Engine,IntegrationServices,FullText `
                    -InstancePath "D:\Program Files\Microsoft SQL Server" `
                    -DataPath "E:\SqlData\$instancename" `
                    -LogPath "F:\SqlLog\$instancename" `
                    -TempPath "G:\Tempdb\$instancename" `
                    -BackupPath "H:\SQLBackup\$instancename" `
                    -AgentCredential $svcAcc `
                    -EngineCredential $svcAcc `
                    -SaCredential $saCredential `
                    -SqlCollation "SQL_Latin1_General_CP1_CI_AS" `
                    -SqlInstance $computername `
                    -InstanceName $instancename `
                    -AdminAccount "Domain\SQL_Admins" `
                    -AuthenticationMode Mixed `
                    -Configuration $config `
                    -PerformVolumeMaintenanceTasks `
                    -Path "i:\" `
                    -Confirm:$false


## Test connect to newly created SQL server instance ##
$testinstall = Get-DbaInstanceInstallDate -SqlInstance $full_sql_name -Credential $svcAcc 
if ($null -eq $testinstall){
    return $false


}
else{
    Write-Host 'Service installed . continuing'
}



Write-Host 'Setting up spn''s' -ForegroundColor Green
##Registrer SPN##
$fqdn = [System.Net.Dns]::GetHostByName($env:computerName).HostName


Set-DbaSpn -SPN "MSSQLSvc/$($fqdn)" -ServiceAccount $svc_acc_string
Set-DbaSpn -SPN "MSSQLSvc/$($fqdn):1433" -ServiceAccount $svc_acc_string


##Force Network Encryption on the instance##
Enable-DbaForceNetworkEncryption -SqlInstance $full_sql_name -Credential $svcAcc -Confirm:$false


##Set lock pages in memory an performvolume maintanance for SQL server account##
Set-DbaPrivilege -ComputerName $computername -Type LPIM,IFI -Credential $svcAcc


##Adds traceflags to service startup##
Set-DbaStartupParameter -SqlInstance $full_sql_name -TraceFlag 4199,2371,3226,7752 -Credential $svcAcc -Confirm:$false


##Setting config values##
Set-DbaSpConfigure -SqlInstance $full_sql_name -Name 'CostThresholdForParallelism' -Value 100
Set-DbaSpConfigure -SqlInstance $full_sql_name -Name 'backup compression default' -Value 1
Set-DbaSpConfigure -SqlInstance $full_sql_name -Name 'network packet size (B)' -Value 8066
Set-DbaSpConfigure -SqlInstance $full_sql_name -Name 'optimize for ad hoc workloads' -Value 1
Set-DbaSpConfigure -SqlInstance $full_sql_name -Name 'blocked process threshold (s)' -Value 120
Set-DbaSpConfigure -SqlInstance $full_sql_name -Name 'max degree of parallelism' -Value 0


##Install Ola Hallengren maintenance##
Install-DbaMaintenanceSolution -SqlInstance $full_sql_name -Database master -CleanupTime 72 -InstallJob


##Modify schedules and drop unnessesary jobs##
New-DbaAgentSchedule -SqlInstance $full_sql_name -Job 'IndexOptimize - USER_DATABASES' -Schedule IndexOptimize_userdatabases_daily -FrequencyType Daily -StartTime "033000" -Force
New-DbaAgentSchedule -SqlInstance $full_sql_name -Job 'DatabaseIntegrityCheck - USER_DATABASES' -Schedule DatabaseIntegrityCheck_userdatabases_weekly -FrequencyType Weekly -FrequencyInterval Sunday -StartTime  "010000" -Force
New-DbaAgentSchedule -SqlInstance $full_sql_name -Job 'DatabaseIntegrityCheck - SYSTEM_DATABASES' -Schedule DatabaseIntegrityCheck_systemdatabases_weekly -FrequencyType Weekly -FrequencyInterval Sunday -StartTime  "000000" -Force
New-DbaAgentSchedule -SqlInstance $full_sql_name -Job 'CommandLog Cleanup' -Schedule commandLog_cleanup_weekly -FrequencyType Weekly -FrequencyInterval Saturday -StartTime "010000" -Force 
New-DbaAgentSchedule -SqlInstance $full_sql_name -Job 'Output File Cleanup' -Schedule output_file_cleanup_weekly -FrequencyType Weekly -FrequencyInterval Saturday -StartTime "020000" -Force 


##Remove unused jobs##
Remove-DbaAgentJob -SqlInstance $full_sql_name -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL' -Confirm:$false
Remove-DbaAgentJob -SqlInstance $full_sql_name -Job 'DatabaseBackup - USER_DATABASES - DIFF' -Confirm:$false
Remove-DbaAgentJob -SqlInstance $full_sql_name -Job 'DatabaseBackup - USER_DATABASES - FULL' -Confirm:$false
Remove-DbaAgentJob -SqlInstance $full_sql_name -Job 'DatabaseBackup - USER_DATABASES - LOG' -Confirm:$false


## Set windows server settings ##


##Alter the page file size##
$pagefile = Get-WmiObject Win32_ComputerSystem -EnableAllPrivileges
$pagefile.AutomaticManagedPagefile = $false
$pagefile.put() | Out-Null


$pagefileset = Get-WmiObject win32_pagefilesetting -Property * | Where-Object{$_.caption -like 'C:*'}
$pagefileset.InitialSize = 128
$pagefileset.MaximumSize = 512
$pagefileset.Put() | Out-Null


Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{name="d:\pagefile.sys";InitialSize = 512; MaximumSize = 32768}


$pagefile = Get-WmiObject Win32_ComputerSystem -EnableAllPrivileges
$pagefile.AutomaticManagedPagefile = $false
$pagefile.put() | Out-Null


$pagefileset = Get-WmiObject win32_pagefilesetting -Property * | Where-Object{$_.caption -like 'C:*'}
$pagefileset.InitialSize = 128
$pagefileset.MaximumSize = 512
$pagefileset.Put() | Out-Null


Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{name="d:\pagefile.sys";InitialSize = 512; MaximumSize = 32768}

        

Hvis jeg nu gerne vil have sp_whosactive på også.. og det vil jeg ofte gerne 🙃 så kan det let tilføjes med denne linje:

Install-DbaWhoIsActive -SqlInstance $full_sql_name -Database master -ErrorAction Continue        

Man kunne godt indsætte et tjek for at drevene er formateret ”rigtigt” (64K), det må blive i version 2 . 

❓ Hvad har jeg glemt, eller har du forslag til hvad jeg bør tilføje?  

🌍 dbatools.io har mange rigtig gode features og er efter min mening ret lige til at kaste sig over, det er veldokumenteret og efter et par forsøg skal det nok lykkedes 😊 -