Deploy SQL Server With This One Script (dbatools)

In this and the next two blog posts I will be bringing diverse dbatools commands into scripts that can handle a complete deployment, do a checkup of major health and configuration metrics, and do a true up of a pre-existing instance. This post will cover the complete deployment, which if you have been reading the audit series will be much more than just the SQL install of last post. This time we are aiming for the whole thing. Install, update, configure host, configure SQL, Deploy maintenance. Everything  I can think of!!

Boilerplate

I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on this blog post by Jonathan Kehayias. I deleted everything off of the third lab host so that it could be a clean target for this post. You are probably familiar with dbatools at this point, but if not now is the perfect time to familiarize yourself with the install and use of the module. On your test environment you can open PowerShell and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post. More on how I set up my testing environment can be found in my first post of this series. 

Install-Module Dbatools
Import-Module Dbatools

SQL Server Deployment Script Scope and Purpose

There are many modifications that are possible with this script. I will not be covering all of the possibilities, but you will have access to this script and can modify it to fit your environment. This is what the script can do:

• Configure the Host:
  • Modify the allocation of the drives on a target host to 64KB
  • Set PowerPlan to High Performance
  • Make the directory of the script (ideally the extracted zip folder I am sharing in this post) a network share to enable SQL install.
• Install SQL Server + Restart
• Update SQL Server +Restart
• Configure SQL Server
  • SPConfigure (MaxDOP, Ad Hoc, Backup Compression, Backup Checksum, Cost threshold, remote admin, max memory)
  • Configure TempDB files.
  • Configure IFI
  • Enable trace flag 3226 as a startup parameter
  • Set the error log retention
  • Enable Errors for message id 823,824,825 and severities 16-25
  • Deploy an Admin Database
  • Deploy Ola maintenance with scheduled jobs and backup target.
  • Deploy other community tools (First Responder Toolkit, Whoisactive, SP_CheckBackup, SP_CheckSecurity, Sp_CheckTempdb)

All of this is scripted, saved and shared to you in a zip folder. In my test environment all of the steps above are finished in less than 20 minutes. I ran a second test against this same host by adding a named instance to this install and it took about the same amount of time. I do not know how long you typically spend installing and configuring SQL, but if your process is manual, I will wager you are spending upwards of an hour for each. Reading through this process as well as testing and modifying this script will save you so much time and teach you about DBAtools as well as PowerShell. I want you to take this script and modify it to fit your environment’s needs and make your life easier. The possibilities and variations are virtually limitless, all you need is time and a test environment!

My test output – the first set of warnings come from Update-DbaInstance, the second from install-DbaFirstResponderKit. They are benign but were resolved by updating my dbatools version from 2.1.31 to 2.7.2.

The remainder of this blog post will just be walking though the options offered and understanding the script sections.

Deployment Script Region 1: Variable Definitions

This first portion of the script will be the variables required for the script to run effectively, as well as simplify the input across all of the various commands we will be running. The Dbatools team has put in effort to reuse argument names across their commands, which makes it easy for me to decide on variable names. I will walk through each variable and the purpose, warnings, and effects. All switches work on a 0 is disabled, 1 is enabled logic.

#Region - Variables
$sw0 = [system.diagnostics.stopwatch]::startNew()
Clear-Host

    $ComputerName               = 'labsql3'
    $instancename               = '' <#Leave blank for a default instance, only put the name of the named instance here - NOT the Hostname\Instancename. 
        Note -                      This process assumes you aren't naming a named instance "DEFAULT", if you do plan on naming your instance 'Default' you will need to do
                                    some surgery on your side. The main issue this would cause with this process is that folder naming of a default instance is 'X:\DEFAULT\SQLdata' 
                                    etc. The named instance with the name of 'DEFAULT' would attempt to use the same paths. Avoid it if you can, fix it if you must. #>

    <# ================ DRIVE ALLOCATION WARNING - READ CAREFULLY =====================#>
    $CheckdriveAllocation       = 1 <# 1 means the process will check the allocation of the drives on the target comupter, 0 will ignore drive allocations.
                                    This process will check the drive allocation of the drives using the drive letter at the beginning of the path values in the $datapath, $logpath, and 
                                    $Temppath variables unless it is C. #>
                                    <# The variable "AutoReformatDrives" will do exactly its name - if the check drive allocation process finds disks using the drive letters from the three 
                                    variables described above that do not have an allocation size of 65536 it will attempt to REFORMAT them remotely. Drive formatting is a destructive process, 
                                    so please ensure the target is either a brand new host without any important data on it, or you are 100% certain there is nothing important on the drives.#>
    $AutoReformatDrives         = 1 # IF THIS IS "1" IT WILL ATTEMPT TO REFORMAT ANY FOUND DRIVES NOT AT 64KB ALLOCATION - THIS IS A DESTRUCTIVE PROCESS OF ANY DATA CURRENTLY ON THOSE DRIVES - BE CERTAIN!
    <# ================================================================================ #>

    $InstallSQL                 = 1
                                # These are the default paths in the instance, they will be created if they don't exist, and the backup path will be used for Ola backup jobs if you choose to install Ola.
        $Datapath               = "D:\$instancename\SQLData\"
        $Logpath                = "L:\$instancename\SQLLogs\"
        $Temppath               = "T:\$instancename\Tempdb\"
        $BackupPath             = '\\Labshare\SQLBackups\' 
        $AdminAccounts          = 'LAB\DA', "LAB\Administrator", 'LAB\SQLService' #accounts that will be added to the SQL Sysadmin role
        $SQLversion             = 2022 # changing this will do nothing unless you also change the install media.
    
    <# ================ Autoclear Directories WARNING - READ CAREFULLY =====================#>
    $AutoClearDirectories       = 1 <#  This process will use the paths above to pre-clear ALL files that exist in the data, log, tempdb and instance root directories recursively. 
                                    Instance root will be the same as the data path except SQLDATA will be replaced by SQLROOT. This was useful for my testing this script where 
                                    uninstalling sql does not get rid of mdf and ldf files for all databases, and helps reduce confusion on repeated installs. BE CERTAIN BEFORE USING 
                                    THIS AS IT IS DESTRUCTIVE! #>
    <# ================================================================================ #>


    $AutoCreateShare            = 1 <# The folder you downloaded with this script as well as the sql 2022 developer install media needs to be a network share so that the target computer 
                                    can access it. if this Variabel is set to 1, this process will automatically create a share using the folder this script is in with read access for everyone, enabling the process to be a bit 
                                    quicker. Set this variable to 0 if you want to create or use an existing share and move/ use the files already there.#>
        $ManualPathtoInstall    = '' <#leave blank unless you are creating the share manually. If so, this needs to be the network path to the setup.exe 
                                    (ex: '\\sharename\sqlextracted\setup.exe') for 2022 unless you also changed the sql version variable above. #>
        $ManualPathtoUpdate     = '' #leave blank unless you are creating the share manually. If so, this just needs to be the network path where the update files are loacated or can be downloaded to.

    $UpdateSQL                  = 1 # Set to 1 to update the instance after install, 0 to skip updating.

# Configurations
    $SetPowerPlan               = 1 # 1 automatically sets the host to high performance.
    $SetMaxDop                  = 1 # 1 automatically sets the maxdop to the recommended value based on the microsoft recommendations.
    $SetOptimizeForAdHoc        = 1 # 1 automatically sets the optimize for ad hoc workloads option.
    $SetBackupCompression       = 1 # 1 automatically sets the backup compression option.
    $SetBackupChecksum          = 1 # 1 automatically sets the backup checksum option.
    $SetCostThreshold           = 1 # 1 automatically sets the cost threshold for parallelism option to 50.
    $SetRemoteAdmin             = 1 # 1 automatically sets the Remote Admin Connections option.
    $SetMaxMemory               = 1 # 1 automatically sets the max memory option to the recommended value.
    $SetTempDBConfiguration     = 1 # 1 automatically configures the tempdb settings.
    $trace3226                  = 1 # 1 to Enable trace flag 3226 as a startup parameter
    $SetErrorlog                = 1 #set the errorlog to the $ErrorlogCount value
        $ErrorlogCount          = 52 # Set the maximum number of error log files to keep (6-99)
    $EnableAlerts               = 1 #This will enable alerts for errors 823,824,825 and issues with severity 16-25. Putting them in the error log for advanced issue identification and troubleshooting.

#Maintenance and Tools
    $ToolsAdminDatabase         = 'DB_admin'
    $DeployToolsAdminDB         = 1 #"1" Will create the database identified in $ToolsAdminDatabase if it doesn't exist

    $DeployOlaMaintenance       = 1 # 0 = False, 1 = True (set to 1 to update Ola Maintenance Solution)
        $OlaDatabase            = $ToolsAdminDatabase #the database where Ola Maintenance Solution store procedures will stored updated
                                <# Note - Ola Jobs are set to automatically install, with a weekly full, daily diff, 15 minute log backups, backups go to the $BackupPath above, cleanup
                                time of 336 hours (two weeks), logtotable enabled on the $OlaDatabase. If you want to change any of this you will need to go down to line 525 and modify the parts you want changed! #>

    $deployFirstResponder       = 1 #1 will deploy first responder toolkit to the new instance.
        $FirstResponderDatabase = 'master' # the database where First Responder Kit stored procedures will be installed or updated.
        $RemoveSQLVersionsTable = 1 # 0 = False, 1 = True (set to 1 to drop the dbo.SQLServerVersions that is automatically created in master as part of the update.)

    $deployWhoisactive          = 1 #1 will deploy whoisactive to the new instance.
        $whoIsActiveDatabase    = 'master' #the database where WhoisActive stored procedures will be installed or updated.

    #StraightPathTools
    $SPtoolsDeploymentDatabase  = 'master'
    $Deploy_SP_CheckBackup      = 1 # https://github.com/Straight-Path-Solutions/sp_CheckBackup
    $Deploy_SP_CheckSecurity    = 1 # https://github.com/Straight-Path-Solutions/sp_CheckSecurity
    $Deploy_SP_CheckTempDB      = 1 # https://github.com/Straight-Path-Solutions/sp_CheckTempdb


#endregion

Everything after this first section is automated. I will spend some time explaining what is happening but not as much as above just to keep this blog post a reasonable length rather than trying to write a book.

Prompted Variables, Derived Variables, Tests, and Prep

The variables here are prompted if needed or derived from the explicit inputs from above. The only reason you would need to change anything here is if you plan on using a named instance with the name ‘Default’, or if you wanted to add more tests after modifying the script to fit your environment, or want to add more preparation scripts like the AutoClearDirectories process. Everything in here should be relatively straight forward in its purpose. Feel free to leave a comment and I will clarify anything that isn’t as clear as I’d hoped!

#Region - Prompted Variables, Derived Variables, Tests, and Prep
    $sw1 = [system.diagnostics.stopwatch]::startNew()
    Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -register

    # Color splats for host messages
        $goodsplat = @{
            foregroundcolor = 'Green'
        }
        $badsplat = @{
            foregroundcolor = 'DarkRed'
            backgroundcolor = 'White'
        }
        $warningsplat = @{
            foregroundcolor = 'DarkYellow'
        }

    # Initial Tests
        IF(!(([System.Security.Principal.WindowsPrincipal]::new([System.Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)))
                {Write-Host "ISSUE: This script must be executed in an Administrator Powershell window." @badsplat; RETURN}


        IF ($ENV:COMPUTERNAME -ieq $ComputerName) {
            Write-host "ISSUE: The target computer name cannot be the same as the computer running this script. Please change the `$ComputerName variable to a remote computer and try again." @badsplat
            RETURN
        }

       

    # Creating the connection SPLAT
    IF ($instancename.length -gt 0 )    { 
        $sqlsplat = @{
            SqlInstance = "$ComputerName\$instancename"
        }
        $SqlInstance = "$ComputerName\$instancename"
        $updateinst =  $instancename
    } ELSE {
        $sqlsplat = @{
            SqlInstance = $ComputerName
        }
        $SqlInstance = $ComputerName
        $updateinst = $computername
    }   

    #Adding credentials to the sql conneciton splat if the install sql variable is set to 1
    # Prompted
        IF ($InstallSQL -eq 1) { 
            $serviceAccount      = $Host.UI.PromptForCredential("Engine & Agent Service Account", "Please enter the domain credentials for the SQL Engine and SQL Agent.", "Lab\SQLService", "")
            $Cred                = $Host.UI.PromptForCredential("Domain Account with permissions to run this process", "Please enter the domain credentials this process to run successfully on the remote target and with access to the network share.", "LAB\DA", "")

            $SQLSplat.sqlcredential = $Cred
        } 




    # Derived Variables
        $DerInstancePath         = $Datapath.replace('SQLData','SQLRoot').replace('\\','\DEFAULT\')
        $DerLogPath              = $Logpath.replace('\\','\DEFAULT\')
        $DerTempPath             = $Temppath.replace('\\','\DEFAULT\')
        $DerDatapath             = $Datapath.replace('\\','\DEFAULT\')



        #collecting the path for the folder holding this script for referencing other resources in the folder.
            $ScriptPath = Switch ($Host.name){
                "Visual Studio Code Host" { split-path $psEditor.GetEditorContext().CurrentFile.Path }
                "Windows PowerShell ISE Host" {  Split-Path -Path $psISE.CurrentFile.FullPath }
                "ConsoleHost" { $PSScriptRoot }
            }

        $transcriptpath = "$ScriptPath\Transcripts\DeploymentLog_$(get-date -f MM-dd-yy)_$(get-date -f "HH.mm").log"
        Start-Transcript -path $transcriptpath 
        Write-host "PROCESS: Process Start - $(get-date -f 'MM-dd-yyyy HH:mm')" @goodsplat

        IF ($AutoCreateShare -eq 1) {
            # Creating a share that 'Everyone' can read so that the target computer can access the SQL Installer files during the SQL install portion, as well as the update directory during the Instance update.
            $s = New-SmbShare -Name "Automated SQL Deployment Share" -Path $ScriptPath -FullAccess "Administrators" -ReadAccess "Everyone" -Temporary | Select-Object -Property path -ExpandProperty path
            $share = $("\\$($env:COMPUTERNAME)" + $s.Substring(2)) 
            }



    #Prep
        IF ($AutoClearDirectories -eq 1) {
            Invoke-Command -ComputerName $ComputerName -ScriptBlock {
                IF(test-path $using:DerInstancePath) { Get-childitem -Path $using:DerInstancePath\*  | Remove-Item -Recurse -Confirm:$false}
                IF(test-path $using:DerLogPath)      { Get-childitem -Path $using:DerLogPath\*       | Remove-Item -Recurse -Confirm:$false}
                IF(test-path $using:DerTempPath)     { Get-childitem -Path $using:DerTempPath\*      | Remove-Item -Recurse -Confirm:$false}
                IF(test-path $using:DerDatapath)     { Get-childitem -Path $using:DerDatapath\*      | Remove-Item -Recurse -Confirm:$false}

            }
        }

    #follow up tests
     IF ($instancename.IndexOf('\') -ne -1) {
            Write-host "ISSUE: `$instancename variable has a '\' in it. Do not put 'HostName\InstanceName' as the value of that variable, only put Instancename" @badsplat
            RETURN
        }

        IF ($null -eq $s -AND $autocreateshare -eq 1 -AND $InstallSQL -eq 1) {
            Write-Host "ISSUE: The share was not automatically created and SQL is set to install - no installation media is referencable and it will fail. Please resolve this before trying again." @badsplat
            RETURN
        }

        IF ($ManualPathtoInstall.Length -eq 0 -AND $autocreateshare -eq 0 -AND $InstallSQL -eq 1) {
            Write-Host "ISSUE: The `$ManualPathtoInstall variable is empty and SQL is set to install, and auto create share is disabled. - no installation media is referencable and it will fail. Please resolve this before trying again." @badsplat
            RETURN
        }


    $sw1.stop()
    Write-host "PROCESS: Variables, Derived Variables, Prompted Variables, Tests, and Prep Steps complete. Elapsed Time: $($sw1.Elapsed.minutes)min $($sw1.Elapsed.seconds)sec" @goodsplat
#endregion

Drive Allocation

This process will test the allocation of the drives identified in the $DataPath, $LogPath, and $TempPath variables by selecting the first letter of the string, unless it is C. The process will check to see if the allocation is 64KB. IF the drive is not set to 64KB  AND you set the $AutoReformatDrives value to 1, it will attempt to format the drives with the correct allocation. This is a destructive process so be certain before using the switch!

#Region - Drive allocation (Test-DbaDiskAllocation)
    $sw2 = [system.diagnostics.stopwatch]::startNew()

        IF ($CheckdriveAllocation -eq 1) {
        Write-host "PROCESS: Starting Drive Allocation Check - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat

            TRY  {
                $d         = "$($Datapath.Substring(0,1)):\", "$($logpath.Substring(0,1)):\", "$($Temppath.Substring(0,1)):\"
                $drives = $d | Select-Object -Unique | Where-Object { $_ -notlike "*C*"}
                
                $DrivesNeedAttn = Test-DbaDiskAllocation -computername $ComputerName | Where-object { $_.name -in $drives -and $_.isbestpractice -eq $False } | Select-Object -Property server, name, isbestpractice

                IF ($DrivesNeedAttn.count -gt 0) {
                    $DrivesNeedAttn | foreach-object {
                        Write-host "FINDING: Drive [$($_.name)] on Host [$($_.Server)] is not currently allocated to 64KB, please reformat this drive with 64KB allocation before installing SQL Server." @badsplat
                    }
                    IF($AutoReformatDrives -eq 1) {
                        Write-Host "PROCESS: Drive Auto Reformat is enabled. Reformatting the identified drives before continuing." @goodsplat
                            $DrivesNeedAttn | Foreach-object {
                            $target = $_.name.replace(":\",'')
                            Invoke-Command -ComputerName $ComputerName -ScriptBlock {
                                Format-Volume -DriveLetter $using:target -FileSystem NTFS -Full -AllocationUnitSize 65536 -Force
                            }
                        }
                    } ELSE { Write-host "PROCESS: Stopping - please resolve the Drive Allocation issue manually or disable the check before trying again." @badsplat; RETURN }
                } ELSE { Write-host "FINDING: No drive allocation issues found" @goodsplat }
            } CATCH { Write-HOST "ISSUE: Check Allocation process had an error - Stopping the process for troubleshooting." @badsplat; RETURN }
        } ELSE {Write-host "PROCESS: Drive Allocation check has been disabled." @warningsplat } 

    $sw2.stop()
    Write-host "PROCESS: Drive Allocation complete. Elapsed Time: $($sw2.Elapsed.minutes)min $($sw2.Elapsed.seconds)sec" @goodsplat
#endregion

There are four outputs based on your inputs, shown below:

Option 1:

$CheckDriveAllocation = 0

Option 2:

$CheckDriveAllocation = 1

$AutoReformatDrives   = 0

Option 3:

$CheckDriveAllocation = 1

$AutoReformatDrives   = 1

Option 4:

Applies to variable inputs 2 and 3 when no drive issues are found.

Deployment Script Region 2: SQL Server Installation

We are bringing back the command from the last post as the next step. I am removing a lot of the configuration options that were specific to my testing environment in favor of some other Dbatools commands that can assist in configuring the host and instance dynamically. I removed configurations for Max Memory, MaxDOP, TempDB, as well as the integrated update as part of the installation.  We will use separate dbatools commands for those later. Those who were here for the last post will see that we now have a switch to change the install-dbainstance command based on whether the InstanceName variable is blank or not, as well as logic to check and pause the deployment process if it fails for some reason.

#Region - SQL Server Installation (Install-Dbainstance)
    $sw3 = [system.diagnostics.stopwatch]::startNew()

        IF ($InstallSQL = 1) {
            Write-host "PROCESS: Starting SQL install - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat
            Try {
                # Modify the ISO path based on whether the user is using the auto created share or a custom location.
                IF ($AutoCreateShare = 1) { $isopath = "$share\SQL Files\setup.exe"} 
                ELSE { $isopath = $ManualPathtoInstall }

                $config = @{
                    UpdateEnabled                   = 'False' #auto update sql as part of installation
                    USEMICROSOFTUPDATE              = 'False' #use MS updater to keep SQL Server up to date.
                    AGTSVCSTARTUPTYPE               = "Automatic" #automatic sql agent startup
                    TCPENABLED                      = "1" # Specify 0 to disable or 1 to enable the TCP/IP protocol. 
                    }

                $splat = @{
                    Credential                      = $cred
                    SQLinstance                     = $ComputerName <#  This 'SQLInstance' argument is not clearly named, it is meant to recieve the hostname that sql will 
                                                                        be installed on, not the name of the SQL Server Instance as it is in other Dbatools commands.
                                                                        Instancename handles the name of the instance and I manage it below.#>
                    Version                         = $SQLversion
                    Feature                         = 'Engine'
                    AuthenticationMode              = 'Mixed'
                    Path                            = $isopath
                    InstancePath                    = $DerInstancePath
                    Datapath                        = $DerDataPath
                    Logpath                         = $DerLogPath
                    Temppath                        = $DerTempPath
                    BackupPath                      = $BackupPath
                    AdminAccount                    = $AdminAccounts
                    SQLCollation                    = 'SQL_Latin1_General_CP1_CI_AS'
                    EngineCredential                = $ServiceAccount
                    AgentCredential                 = $ServiceAccount
                    PerformVolumeMaintenanceTasks   = $True
                    Restart                         = $True
                    Configuration                   = $config
                    Confirm                         = $false
                }

                # Modify the run for named instance installs.
                IF ($instancename.length -eq 0) 
                    { $instresult = Install-DbaInstance @Splat} 
                ELSE 
                    { $instresult = Install-DbaInstance @Splat -InstanceName $instancename}

                IF ($instresult.successful -eq $false) {
                    THROW $instresult.exitmessage
                } ELSEIF ($instresult.restarted -eq $False) {
                    Write-host "PROCESS: Install Complete - Restarting Computer." @goodsplat
                    Restart-Computer -ComputerName $ComputerName -force -wait
                }

            } CATCH { Write-Host "ISSUE: SQL Install had an error - Stopping the process for troubleshooting." @badsplat; RETURN}
        } ELSE { Write-Host "PROCESS: SQL Install was disabled." @warningsplat }

    $sw3.stop()
    Write-host "PROCESS: SQL Install and restart complete. Elapsed Time: $($sw3.Elapsed.minutes)min $($sw3.Elapsed.seconds)sec" @goodsplat
#endregion

Deployment Script Region 3: Update SQL Server

Not much is different here to what I shared in the previous post. There are more whizbangs in terms of timers and process/ logging logic, as well as logic to ensure you are only updatign the newly installed instance rather than all Instances on the host, but otherwise this should be familiar if you read my update-dbainstance post.

#Region - Update SQL Server
    $sw4 = [system.diagnostics.stopwatch]::startNew()

    IF ($UpdateSQL -eq 1) {
        Write-host "PROCESS: Starting SQL update - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat
        Try {
            IF ($AutoCreateShare = 1) { $updatepath = "$share\SQL Updates\"} 
            ELSE { $updatepath = $ManualPathtoUpdate }


            $splat = @{
                ComputerName = $ComputerName
                instancename = $updateinst
                Restart      = $true
                Path         = $updatepath
                Confirm      = $false
                Credential   = $cred
                Download     = $True
            }

            $result = Update-DbaInstance @Splat

            If ($result.successful -eq $false) {
                THROW "Update failed"
            } ELSEIF ($result.restarted -eq $false) {
                Write-host "PROCESS: Update Complete - Restarting Computer." @goodsplat
                Restart-Computer -ComputerName $ComputerName -force -wait
            }

        } CATCH { Write-Host "ISSUE: SQL Update had an error - Stopping the process for troubleshooting." @badsplat; RETURN}
    } ELSE { Write-Host "PROCESS: SQL Update was disabled." @warningsplat }

    $sw4.stop()
    Write-host "PROCESS: SQL Update complete. Elapsed Time: $($sw4.Elapsed.minutes)min $($sw4.Elapsed.seconds)sec" @goodsplat
#endregion

Deployment Script Region 4: SQL Server Configurations

I found that after updating the SQL instance and restarting there is a brief upgrade period I kept running into. I added a three-minute pause in the script to let the SQL instance finish the update before attempting to connect for the configuration changes. There are a lot of new scripts in this section, but none are terribly complex. Look at the documentation for each, using what I give here it should be relatively straightforward to add or subtract to this section.

#Region - Configurations
    Write-host "PROCESS: Pausing processing for 3 minutes post restart before attempting to connect for configurations and maintenance (Post SQL update upgrade mode)." @goodsplat
    Start-sleep -seconds 180

    $sw5 = [system.diagnostics.stopwatch]::startNew()

    Write-host "PROCESS: Starting SQL Configurations - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat

        IF($SetPowerPlan -eq 1) {
            TRY{ 
                Set-DbaPowerPlan -ComputerName $ComputerName -Credential $cred -Confirm:$false | Out-Null
                Write-Host "PROCESS: Power Plan has been set to High Performance." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Power Plan had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Power Plan was disabled." @warningsplat }

        IF($SetMaxDop -eq 1) {
            TRY {
                Test-DbaMaxDop @sqlsplat | Set-DbaMaxDop | Out-Null
                Write-Host "PROCESS: MaxDOP has been set to the recommended value." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting MaxDOP had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set MaxDOP was disabled." @warningsplat }

        IF($SetOptimizeForAdHoc -eq 1) {
            TRY {
                Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Optimize For Ad Hoc Workloads'} | Set-DbaSpConfigure -Value 1 | Out-Null
                Write-Host "PROCESS: Optimize for Ad Hoc Workloads has been enabled." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Optimize for Ad Hoc Workloads had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Optimize for Ad Hoc Workloads was disabled." @warningsplat }

        IF($SetBackupCompression -eq 1) {
            TRY {
                Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Backup Compression Default'} | Set-DbaSpConfigure -Value 1 | Out-Null
                Write-Host "PROCESS: Backup Compression has been enabled." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Backup Compression had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Backup Compression was disabled." @warningsplat }

        IF($SetBackupChecksum -eq 1) {
            TRY {
                Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Backup Checksum Default'} | Set-DbaSpConfigure -Value 1 | Out-Null
                Write-Host "PROCESS: Backup Checksum has been enabled." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Backup Checksum had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Backup Checksum was disabled." @warningsplat }

        IF($SetCostThreshold -eq 1) {
            TRY {
                Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Cost Threshold For Parallelism'} | Set-DbaSpConfigure -Value 50 | Out-Null
                Write-Host "PROCESS: Cost Threshold for Parallelism has been set to 50." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Cost Threshold for Parallelism had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Cost Threshold for Parallelism was disabled." @warningsplat }

        IF($SetRemoteAdmin -eq 1) {
            TRY {
                Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Remote Admin Connections'} | Set-DbaSpConfigure -Value 1 | Out-Null
                Write-Host "PROCESS: Remote Admin Connections has been enabled." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Remote Admin Connections had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Remote Admin Connections was disabled." @warningsplat }

        IF($SetMaxMemory -eq 1) {
            Try {
                Set-DbaMaxMemory @sqlsplat | Out-Null
                Write-Host "PROCESS: Max Memory has been set to the recommended value." @goodsplat
            } Catch { Write-Host "ISSUE: Setting Max Memory had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Max Memory was disabled." @warningsplat }

        IF($SetTempDBConfiguration -eq 1) {
            TRY {
                Set-DbaTempDbConfig @sqlsplat -Datafilesize 1000 | Out-Null
                Write-Host "PROCESS: TempDB configuration has been set to the recommended filecount with a default size." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting TempDB configuration had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set TempDB configuration was disabled." @warningsplat }

        IF($trace3226 -eq 1) {
            TRY {
                Enable-DbaTraceFlag @sqlsplat -TraceFlag 3226 | Out-Null
                Write-Host "PROCESS: Trace Flag 3226 has been added as a startup parameter." @goodsplat
            } CATCH { Write-Host "ISSUE: Adding Trace Flag 3226 had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Trace Flag 3226 was disabled." @warningsplat }

        IF($SetErrorlog -eq 1) {
            TRY {
                Set-DbaErrorLogConfig @sqlsplat -logcount $ErrorlogCount | Out-Null
                Write-Host "PROCESS: Error log file count has been set to $ErrorlogCount." @goodsplat
            } CATCH { Write-Host "ISSUE: Setting Error log file count had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Set Error log file count was disabled." @warningsplat }

        IF($enableAlerts -eq 1) {
            TRY {
                Invoke-DbaQuery @sqlsplat -query "
                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 16 Error', 
                            @message_id=0, 
                            @severity=16, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 17 Error', 
                            @message_id=0, 
                            @severity=17, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 18 Error', 
                            @message_id=0, 
                            @severity=18, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 19 Error', 
                            @message_id=0, 
                            @severity=19, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 20 Error', 
                            @message_id=0, 
                            @severity=20, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error', 
                            @message_id=0, 
                            @severity=21, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 22 Error', 
                            @message_id=0, 
                            @severity=22, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 23 Error', 
                            @message_id=0, 
                            @severity=23, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 24 Error', 
                            @message_id=0, 
                            @severity=24, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Severity 25 Error', 
                            @message_id=0, 
                            @severity=25, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    EXEC msdb.dbo.sp_add_alert @name=N'Error 823', 
                            @message_id=823, 
                            @severity=0, 
                            @enabled=1, 
                            @delay_between_responses=60, 
                            @include_event_description_in=1,
                            @category_name=N'[Uncategorized]';

                    EXEC msdb.dbo.sp_add_alert @name=N'Error 824', 
                            @message_id=824, 
                            @severity=0, 
                            @enabled=1, 
                            @delay_between_responses=60, 
                            @include_event_description_in=1,
                            @category_name=N'[Uncategorized]'; 

                    EXEC msdb.dbo.sp_add_alert @name=N'Error 825', 
                            @message_id=825, 
                            @severity=0, 
                            @enabled=1, 
                            @delay_between_responses=0, 
                            @include_event_description_in=1;

                    GO
                "
                Write-Host "PROCESS: Alerts for Errors 823, 824, 825 and Severity 16-25 have been enabled." @goodsplat
            } CATCH { Write-Host "ISSUE: Enabling Alerts had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Enable Alerts was disabled." @warningsplat }
        

    $sw5.stop()
    Write-host "PROCESS: Configurations complete. Elapsed Time: $($sw5.Elapsed.minutes)min $($sw5.Elapsed.seconds)sec" @goodsplat
#endregion

Deployment Script Region 5: SQL Server Maintenance & Tools

This last section is where we deploy the management database if it is marked, install ola, and deploy other community tools to the instance. There is a little more going on here than in configurations, but hopefully it makes sense as you look through it. Ola has the most arguments and if you want a more comprehensive look, hop over to my post about that. Otherwise, our very own sp_Check tools (Security, Backup, TempDB) have the most going here, and that is just a download from the GitHub repo and then run that stored procedure create command against the instance.

#Region - Maintenance & Tools
    $sw6 = [system.diagnostics.stopwatch]::startNew()

    Write-host "PROCESS: Starting Maintenance and tools - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat


        IF ($DeployToolsAdminDB -eq 1) {
            TRY {
                New-DbaDatabase @sqlsplat -Name $ToolsAdminDatabase | Out-Null
                Set-DbaDbOwner @sqlsplat | Out-Null
                Write-Host "PROCESS: [$ToolsAdminDatabase] Database has been deployed." @goodsplat
            } CATCH { Write-Host "ISSUE: Deploying [$ToolsAdminDatabase] Database had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Deploy [$ToolsAdminDatabase] was disabled." @warningsplat }

        IF ($DeployOlaMaintenance -eq 1) {
            TRY {
                $SPLAT = @{
                    Sqlinstance         = $SqlInstance
                    Database            = $OlaDatabase
                    BackupLocation      = $BackupPath
                    Cleanuptime         = 336
                    Logtotable          = $True
                    Installjobs 	    = $True
                    AutoScheduleJobs    = ‘WeeklyFull’
                    SqlCredential       = $cred
                    Force               = $True
                }

                Install-DbaMaintenanceSolution @splat | Out-Null

                Write-Host "PROCESS: Ola Hallengren Maintenance Solution has been deployed in [$OlaDatabase]." @goodsplat
            } CATCH { Write-Host "ISSUE: Deploying/ Updating Ola Hallengren Maintenance Solution had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Deploy Ola Maintenance was disabled." @warningsplat }

        IF ($deployFirstResponder -eq 1) {
            TRY {
                Install-DbaFirstResponderKit @sqlsplat -Database $FirstResponderDatabase  -Force | Out-Null
                Write-Host "PROCESS: First Responder Kit has been deployed in [$FirstResponderDatabase]." @goodsplat
            } CATCH { Write-Host "ISSUE: Deploying/ Updating First Responder Kit had an error." @badsplat }

            IF ($RemoveSQLVersionsTable -eq 1) {
                TRY {
                    Invoke-DbaQuery @sqlsplat -Database $FirstResponderDatabase  -Query "
                        IF OBJECT_ID('dbo.SQLServerVersions') IS NOT NULL DROP TABLE dbo.SQLServerVersions" | Out-Null
                    Write-Host "PROCESS: dbo.SQLServerVersions table has been removed from [$FirstResponderDatabase]." @goodsplat
                } CATCH { Write-Host "ISSUE: Removing dbo.SQLServerVersions table had an error." @badsplat }
            } ELSE { Write-Host "PROCESS: Remove dbo.SQLServerVersions From [$FirstResponderDatabase] was disabled." @warningsplat }
        } ELSE { Write-Host "PROCESS: Deploy First Responder Kit was disabled." @warningsplat }

        IF ($deployWhoisactive -eq 1) {
            TRY {
                Install-DbaWhoIsActive @sqlsplat -Database $whoIsActiveDatabase  -Force | Out-Null
                Write-Host "PROCESS: WhoIsActive has been deployed in [$whoIsActiveDatabase]." @goodsplat
            } CATCH { Write-Host "ISSUE: Deploying WhoIsActive had an error." @badsplat }
        } ELSE { Write-Host "PROCESS: Deploy WhoIsActive was disabled." @warningsplat }

        IF($Deploy_SP_CheckSecurity -eq 1) {
            TRY {
                $path     = "$ScriptPath\Supporting Files\sp_checksecurity.sql"
                Invoke-WebRequest -Uri https://raw.githubusercontent.com/Straight-Path-Solutions/sp_CheckSecurity/main/sp_CheckSecurity.sql -OutFile $path
                IF ((Get-ChildItem $path) -eq 0) {
                    Write-Host "sp_checksecurity failed to download, please manually download it and put it in $path" @badsplat
                }
                Invoke-DbaQuery @sqlsplat -File $path -Database $SPtoolsDeploymentDatabase | Out-Null
                Write-Host "PROCESS: SP_CheckSecurity has been deployed in [$SPtoolsDeploymentDatabase]." @goodsplat
            } CATCH { Write-host "ISSUE: SP_CheckSecurity had an error" @badsplat }
        } ELSE { Write-Host "PROCESS: Deploy SP_CheckSecurity was disabled." @warningsplat }

        IF($Deploy_SP_CheckBackup   -eq 1) {
            TRY {
                $path     = "$ScriptPath\Supporting Files\sp_checkbackup.sql"
                Invoke-WebRequest -Uri https://raw.githubusercontent.com/Straight-Path-Solutions/sp_CheckBackup/main/sp_CheckBackup.sql -OutFile $path
                IF ((Get-ChildItem $path) -eq 0) {
                    Write-Host "sp_checkbackup failed to download, please manually download it and put it in $path" @badsplat
                }
                Invoke-DbaQuery @sqlsplat -File $path -Database $SPtoolsDeploymentDatabase | Out-Null
                Write-Host "PROCESS: SP_CheckBackup has been deployed in [$SPtoolsDeploymentDatabase]." @goodsplat
            } CATCH { Write-host "ISSUE: SP_CheckBackup had an error" @badsplat }
        } ELSE { Write-Host "PROCESS: Deploy SP_CheckBackup was disabled." @warningsplat }

        IF($Deploy_SP_CheckTempDB   -eq 1) {
            TRY {
                $path     = "$ScriptPath\Supporting Files\sp_checktempdb.sql"
                Invoke-WebRequest -Uri https://raw.githubusercontent.com/Straight-Path-Solutions/sp_CheckTempdb/main/sp_CheckTempdb.sql -OutFile $path
                IF ((Get-ChildItem $path) -eq 0) {
                    Write-Host "sp_checktempdb failed to download, please manually download it and put it in $path" @badsplat
                }
                Invoke-DbaQuery @sqlsplat -File $path -Database $SPtoolsDeploymentDatabase | Out-Null
                Write-Host "PROCESS: SP_CheckTempDB has been deployed in [$SPtoolsDeploymentDatabase]." @goodsplat
            } CATCH { Write-host "ISSUE: SP_CheckTempDB had an error" @badsplat } 
        } ELSE { Write-Host "PROCESS: Deploy SP_CheckTempDB was disabled." @warningsplat }

    $sw6.stop()
    Write-host "PROCESS: Maintenance and Tools complete. Elapsed Time: $($sw6.Elapsed.minutes)min $($sw6.Elapsed.seconds)sec" @goodsplat
#endregion

After this section all that I do is clean up the AutoCreatedShare if it was enabled and then stop the transcript. I enjoy using transcripts because it means I can run the process headless on a one-off schedule or without needing to follow up for a significant period of time without fear of losing the output I needed to review. Here is my first example output transcript.

All messages would be captured including verbose and errors, so it is very useful when learning automation!

SQL Server Deployment Script Instructions

I created a zip folder with this script, a few folders to capture various parts of the script in one location, all you need to do is copy the extracted contents of a sql 2022 developer edition iso file to the “SQL Files” folder. The Idea is you will download the zip folder and extract it and run the script from there. It will use the extracted iso contents in “SQL Files” to install SQL, download the latest 2022 update and put it in “SQL Updates”, put the sp_check files in the “Supporting files” folder, and put all transcripts in the “Transcripts” folder. I figured that would be the lowest barrier to entry without telling you to find and supply 4 locations for the script to save things to or read from. If you’d prefer to use custom locations for each of these, you will need to do a bit of surgery to find and replace the path values I use and substitute your own.

Here is the link to the Zip folder on Github:

Conclusion

My goal with this is to show you a bit more of the magic that PowerShell and dbatools can bring to your processes. So much can be done with PowerShell, and dbatools amplifies the possibilities for DBA’s to simplify so many aspects of the regular tasks and concerns we have. This script took a while to create, but now it is in your hands. Use it to learn, take it apart and use smaller portions, make it a small piece of an even bigger script. So many possibilities. The biggest benefit in my mind is the time freed up if you are currently responsible for regularly installing SQL Server. How much time can you get back? Time that can be used for new things, or improving old things like this script does. Take some time with this script, learn, benefit, and save yourself some time!