DBA Scripts: Pre-Install and Post-Install Checks for SQL Server

You are standing up a new SQL Server instance, or you have inherited one and want to know what state it is really in. These 2 read-only PowerShell scripts sit either side of setup: pre-install-check.ps1 tells you whether the machine is ready before you run setup.exe, and post-install-validation.ps1 tells you what the installer left at a default once it finishes. Both print PASS, WARN or FAIL per check with a verdict at the end, and neither changes anything, so run them as often as you like.

Before You Run Setup: pre-install-check.ps1

Run it on the machine that will host the instance, from an elevated shell, with -SqlVersion and the planned -DataDir and -LogDir. It checks admin elevation, the OS build against the minimum for that SQL Server version, pending reboot state, RAM, CPU count, .NET Framework and PowerShell versions, free space on the install, data and log drives, whether TCP port 1433 is already taken, existing instances, and the active firewall profile.

<#
.SYNOPSIS
Run pre-installation checks before deploying SQL Server.

.DESCRIPTION
Validates the machine is ready for SQL Server installation. Reports PASS / WARN / FAIL
for each check. Does not make any changes to the system.

.PARAMETER InstanceName
Target instance name to check for existing installs. Default: MSSQLSERVER.

.PARAMETER InstallDir
Planned SQL Server binary directory — checked for disk space.

.PARAMETER DataDir
Planned data directory — checked for disk space.

.PARAMETER LogDir
Planned log directory — checked for disk space.

.PARAMETER SqlVersion
SQL Server version being installed (2016|2017|2019|2022). Default: 2022.

.EXAMPLE
.\admin\installation\pre-install-check.ps1
.\admin\installation\pre-install-check.ps1 -SqlVersion 2019 -DataDir D:\SQLData

.NOTES
RiskLevel   : SAFE - reads only; writes nothing to the machine or the instance
#>
param(
    [string]$InstanceName = 'MSSQLSERVER',
    [string]$InstallDir   = 'C:\Program Files\Microsoft SQL Server',
    [string]$DataDir      = 'C:\SQLData',
    [string]$LogDir       = 'C:\SQLLogs',
    [ValidateSet('2016','2017','2019','2022')]
    [string]$SqlVersion   = '2022'
)

$ErrorActionPreference = 'SilentlyContinue'

$pass  = 0; $warn = 0; $fail = 0
$results = [System.Collections.Generic.List[PSObject]]::new()

function Add-Check {
    param([string]$Category, [string]$Check, [string]$Status, [string]$Detail)
    $color = switch ($Status) { 'PASS'{'Green'} 'WARN'{'Yellow'} 'FAIL'{'Red'} default{'White'} }
    $results.Add([PSCustomObject]@{ Category=$Category; Check=$Check; Status=$Status; Detail=$Detail })
    Write-Host ("  [{0,-4}] {1,-38} {2}" -f $Status, $Check, $Detail) -ForegroundColor $color
    switch ($Status) { 'PASS'{$script:pass++} 'WARN'{$script:warn++} 'FAIL'{$script:fail++} }
}

Write-Host ""
Write-Host "  SQL Server $SqlVersion Pre-Install Checks" -ForegroundColor Cyan
Write-Host ("  " + [string]::new('-',60)) -ForegroundColor DarkCyan
Write-Host ""

# ── Admin elevation ───────────────────────────────────────────────────────────
$isAdmin = ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole(
               [Security.Principal.WindowsBuiltInRole]::Administrator)
Add-Check 'System' 'Running as Administrator' `
    $(if ($isAdmin) {'PASS'} else {'FAIL'}) `
    $(if ($isAdmin) {'Elevated'} else {'Re-run as Administrator'})

# ── OS version ────────────────────────────────────────────────────────────────
$os      = Get-CimInstance Win32_OperatingSystem
$osBuild = [int]$os.BuildNumber
$osName  = $os.Caption

$minBuild = switch ($SqlVersion) {
    '2022' { 17763 }  # Windows Server 2019
    '2019' { 14393 }  # Windows Server 2016
    '2017' { 14393 }  # Windows Server 2016
    '2016' { 9600  }  # Windows Server 2012 R2
}
$osStatus = if ($osBuild -ge $minBuild) {'PASS'} else {'FAIL'}
Add-Check 'System' 'OS version' $osStatus "$osName (Build $osBuild)"

# ── Pending reboot ────────────────────────────────────────────────────────────
$pendingReboot = $false
$rebootKeys = @(
    'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending',
    'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired',
    'HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations'
)
foreach ($key in $rebootKeys) {
    if (Test-Path $key) { $pendingReboot = $true; break }
}
Add-Check 'System' 'No pending reboot' `
    $(if ($pendingReboot) {'WARN'} else {'PASS'}) `
    $(if ($pendingReboot) {'Pending reboot detected - install may fail'} else {'Clean'})

# ── RAM ───────────────────────────────────────────────────────────────────────
$ramGB = [math]::Round((Get-CimInstance Win32_ComputerSystem).TotalPhysicalMemory / 1GB, 1)
$ramStatus = if ($ramGB -ge 8) {'PASS'} elseif ($ramGB -ge 4) {'WARN'} else {'FAIL'}
Add-Check 'Hardware' 'RAM' $ramStatus "$ramGB GB $(if ($ramGB -lt 8) {'(8 GB recommended)'})"

# ── CPU count ─────────────────────────────────────────────────────────────────
$cpus = (Get-CimInstance Win32_ComputerSystem).NumberOfLogicalProcessors
Add-Check 'Hardware' 'Logical CPUs' 'PASS' "$cpus processors"

# ── .NET Framework ────────────────────────────────────────────────────────────
$netKey = Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full' -ErrorAction SilentlyContinue
$netVer = $netKey.Release
$netStatus = if ($netVer -ge 461808) {'PASS'} elseif ($netVer -ge 394802) {'WARN'} else {'FAIL'}
$netLabel  = if ($netVer -ge 528040) {'4.8+'} elseif ($netVer -ge 461808) {'4.7.2'} elseif ($netVer -ge 394802) {'4.6.2'} else {"$netVer (4.7.2+ required)"}
Add-Check 'Prerequisites' '.NET Framework' $netStatus $netLabel

# ── PowerShell version ────────────────────────────────────────────────────────
$psVer = $PSVersionTable.PSVersion
$psStatus = if ($psVer.Major -ge 5) {'PASS'} else {'WARN'}
Add-Check 'Prerequisites' 'PowerShell version' $psStatus "$($psVer.Major).$($psVer.Minor)"

# ── Disk space ────────────────────────────────────────────────────────────────
$diskChecks = @(
    @{ Path=$InstallDir; Label='Install dir';  MinGB=20 },
    @{ Path=$DataDir;    Label='Data dir';     MinGB=50 },
    @{ Path=$LogDir;     Label='Log dir';      MinGB=20 }
)
foreach ($dc in $diskChecks) {
    $drive   = Split-Path -Qualifier $dc.Path
    $psDrive = Get-PSDrive ($drive.TrimEnd(':')) -ErrorAction SilentlyContinue
    if ($psDrive) {
        $freeGB = [math]::Round($psDrive.Free / 1GB, 1)
        $status = if ($freeGB -ge $dc.MinGB) {'PASS'} elseif ($freeGB -ge ($dc.MinGB / 2)) {'WARN'} else {'FAIL'}
        Add-Check 'Disk' $dc.Label $status "$freeGB GB free on $drive (need $($dc.MinGB) GB)"
    } else {
        Add-Check 'Disk' $dc.Label 'WARN' "Drive $drive not found - will be created"
    }
}

# ── TCP port 1433 availability ────────────────────────────────────────────────
$port1433InUse = $false
try {
    $tcpConn = Get-NetTCPConnection -LocalPort 1433 -State Listen -ErrorAction SilentlyContinue
    if ($tcpConn) { $port1433InUse = $true }
} catch {
    Write-Error $_
    throw
}
Add-Check 'Network' 'TCP port 1433' `
    $(if ($port1433InUse) {'WARN'} else {'PASS'}) `
    $(if ($port1433InUse) {'Already in use - check existing SQL instance'} else {'Available'})

# ── Existing SQL Server instances ─────────────────────────────────────────────
$regInstances = @()
$regPath = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
if (Test-Path $regPath) {
    $regInstances = Get-ItemProperty $regPath |
        Get-Member -MemberType NoteProperty |
        Where-Object { $_.Name -notmatch '^PS' } |
        Select-Object -ExpandProperty Name
}

if ($regInstances.Count -eq 0) {
    Add-Check 'SQL Server' 'Existing instances' 'PASS' 'None found'
} elseif ($regInstances -contains $InstanceName) {
    Add-Check 'SQL Server' 'Existing instances' 'FAIL' "Instance '$InstanceName' already exists: $($regInstances -join ', ')"
} else {
    Add-Check 'SQL Server' 'Existing instances' 'WARN' "Other instances present: $($regInstances -join ', ')"
}

# ── Windows Firewall ──────────────────────────────────────────────────────────
try {
    $fwProfile = (Get-NetFirewallProfile -ErrorAction Stop | Where-Object Enabled -eq $true | Select-Object -First 1).Name
    if ($fwProfile) {
        Add-Check 'Network' 'Windows Firewall' 'WARN' "Active profile: $fwProfile - ensure port 1433 rule is added post-install"
    } else {
        Add-Check 'Network' 'Windows Firewall' 'PASS' 'No active firewall profiles'
    }
} catch {
    Add-Check 'Network' 'Windows Firewall' 'WARN' 'Could not check firewall state'
}

# ── Summary ───────────────────────────────────────────────────────────────────
Write-Host ""
Write-Host ("  " + [string]::new('=',60)) -ForegroundColor DarkCyan
$summaryColor = if ($fail -gt 0) {'Red'} elseif ($warn -gt 0) {'Yellow'} else {'Green'}
$verdict      = if ($fail -gt 0) {'NOT READY - fix FAIL items before proceeding'} `
                elseif ($warn -gt 0) {'READY WITH WARNINGS - review WARN items'} `
                else {'READY - all checks passed'}
Write-Host "  $verdict" -ForegroundColor $summaryColor
Write-Host "  PASS: $pass   WARN: $warn   FAIL: $fail" -ForegroundColor $summaryColor
Write-Host ""

Real Output From This Lab Machine

This machine already runs a default instance, which is exactly the situation the script is there to catch.

Every FAIL here is genuine: a default instance already exists, the drive has well under the recommended free space, and the shell was not elevated. That is the signal a pre-install check should give.

What Each Check Means

After Setup Finishes: post-install-validation.ps1

Run it from the server, or from anywhere with the SqlServer module, against the new instance. It checks the engine and Agent services, connects and reads the build, reads the sp_configure values that matter on day 1, counts TempDB data files, confirms the connection came in over TCP, and reports the SA account and the authentication mode. Pass -ExpectedMaxMemoryGB and -ExpectedMaxDOP to validate against your build standard instead of against the defaults.

<#
.SYNOPSIS
Validate a SQL Server installation is configured correctly.

.DESCRIPTION
Connects to a SQL Server instance and checks services, connectivity, version,
configuration settings, TempDB layout, and security posture.
Outputs PASS / WARN / FAIL per check with a summary.

.PARAMETER ServerInstance
Instance to validate. Default: . (local default instance).

.PARAMETER InstanceName
Windows service name suffix (e.g. MSSQLSERVER or SQL2022). Default: MSSQLSERVER.

.PARAMETER ExpectedMaxMemoryGB
Expected max server memory setting. If 0, validates it is not at the unlimited default.

.PARAMETER ExpectedMaxDOP
Expected MaxDOP. If 0, validates it is not at default (0).

.EXAMPLE
.\admin\installation\post-install-validation.ps1
.\admin\installation\post-install-validation.ps1 -ServerInstance PROD01\SQL2022 -InstanceName SQL2022

.NOTES
RiskLevel   : SAFE - read-only queries; changes no configuration
#>
param(
    [string]$ServerInstance      = '.',
    [string]$InstanceName        = 'MSSQLSERVER',
    [int]$ExpectedMaxMemoryGB    = 0,
    [int]$ExpectedMaxDOP         = 0
)

$ErrorActionPreference = 'SilentlyContinue'

if ($ServerInstance -eq '.' -and $env:DBASCRIPTS_SERVER) { $ServerInstance = $env:DBASCRIPTS_SERVER }

$pass = 0; $warn = 0; $fail = 0

function Add-Check {
    param([string]$Category, [string]$Check, [string]$Status, [string]$Detail)
    $color = switch ($Status) { 'PASS'{'Green'} 'WARN'{'Yellow'} 'FAIL'{'Red'} default{'White'} }
    Write-Host ("  [{0,-4}] {1,-40} {2}" -f $Status, $Check, $Detail) -ForegroundColor $color
    switch ($Status) { 'PASS'{$script:pass++} 'WARN'{$script:warn++} 'FAIL'{$script:fail++} }
}

Write-Host ""
Write-Host "  Post-Install Validation - $ServerInstance" -ForegroundColor Cyan
Write-Host ("  " + [string]::new('-', 62)) -ForegroundColor DarkCyan
Write-Host ""

# ── Services ──────────────────────────────────────────────────────────────────
$svcName    = if ($InstanceName -eq 'MSSQLSERVER') { 'MSSQLSERVER' } else { "MSSQL`$$InstanceName" }
$agtName    = if ($InstanceName -eq 'MSSQLSERVER') { 'SQLSERVERAGENT' } else { "SQLAgent`$$InstanceName" }

$sqlSvc = Get-Service -Name $svcName -ErrorAction SilentlyContinue
$agtSvc = Get-Service -Name $agtName -ErrorAction SilentlyContinue

Add-Check 'Services' 'SQL Server service running' `
    $(if ($sqlSvc -and $sqlSvc.Status -eq 'Running') {'PASS'} else {'FAIL'}) `
    $(if ($sqlSvc) { $sqlSvc.Status } else { "Service '$svcName' not found" })

Add-Check 'Services' 'SQL Agent service running' `
    $(if ($agtSvc -and $agtSvc.Status -eq 'Running') {'PASS'} elseif ($agtSvc) {'WARN'} else {'WARN'}) `
    $(if ($agtSvc) { $agtSvc.Status } else { "Service '$agtName' not found" })

Add-Check 'Services' 'SQL Agent startup type' `
    $(if ($agtSvc -and $agtSvc.StartType -eq 'Automatic') {'PASS'} else {'WARN'}) `
    $(if ($agtSvc) { $agtSvc.StartType } else { 'unknown' })

# ── Connectivity ──────────────────────────────────────────────────────────────
$connected = $false
try {
    $versionRow = Invoke-Sqlcmd -ServerInstance $ServerInstance `
                      -Query "SELECT @@VERSION AS v, SERVERPROPERTY('ProductVersion') AS pv, SERVERPROPERTY('ProductLevel') AS pl" `
                      -QueryTimeout 15 -TrustServerCertificate -ErrorAction Stop
    $connected = $true
    Add-Check 'Connectivity' 'SQL Server connection' 'PASS' "v$($versionRow.pv) $($versionRow.pl)"
} catch {
    Add-Check 'Connectivity' 'SQL Server connection' 'FAIL' $_.Exception.Message
}

if ($connected) {

# ── Configuration ─────────────────────────────────────────────────────────────
$configs = Invoke-Sqlcmd -ServerInstance $ServerInstance `
               -Query "SELECT name, value_in_use FROM sys.configurations WHERE name IN ('max server memory (MB)','max degree of parallelism','cost threshold for parallelism','backup compression default','optimize for ad hoc workloads','remote admin connections')" `
               -TrustServerCertificate -ErrorAction SilentlyContinue
$cfg = @{}
foreach ($row in $configs) { $cfg[$row.name] = [int]$row.value_in_use }

$maxMemMB = $cfg['max server memory (MB)']
if ($ExpectedMaxMemoryGB -gt 0) {
    $expectedMB = $ExpectedMaxMemoryGB * 1024
    Add-Check 'Config' 'Max server memory' `
        $(if ($maxMemMB -eq $expectedMB) {'PASS'} else {'WARN'}) `
        "$($maxMemMB)MB $(if ($maxMemMB -eq $expectedMB) {'matches expected'} else {"(expected $($expectedMB)MB)"})"
} else {
    Add-Check 'Config' 'Max server memory' `
        $(if ($maxMemMB -lt 2147483647) {'PASS'} else {'WARN'}) `
        "$($maxMemMB)MB $(if ($maxMemMB -ge 2147483647) {'- not configured (SQL default = unlimited)'})"
}

$maxdop = $cfg['max degree of parallelism']
if ($ExpectedMaxDOP -gt 0) {
    Add-Check 'Config' 'MaxDOP' `
        $(if ($maxdop -eq $ExpectedMaxDOP) {'PASS'} else {'WARN'}) `
        "$maxdop $(if ($maxdop -ne $ExpectedMaxDOP) {"(expected $ExpectedMaxDOP)"})"
} else {
    Add-Check 'Config' 'MaxDOP' `
        $(if ($maxdop -gt 0) {'PASS'} else {'WARN'}) `
        $(if ($maxdop -eq 0) {'0 - not configured (unlimited parallelism)'} else { $maxdop })
}

$ctp = $cfg['cost threshold for parallelism']
Add-Check 'Config' 'Cost threshold for parallelism' `
    $(if ($ctp -ge 25) {'PASS'} elseif ($ctp -ge 5) {'WARN'} else {'WARN'}) `
    "$ctp $(if ($ctp -le 5) {'- SQL default; 25-50 recommended'})"

Add-Check 'Config' 'Backup compression' `
    $(if ($cfg['backup compression default'] -eq 1) {'PASS'} else {'WARN'}) `
    $(if ($cfg['backup compression default'] -eq 1) {'Enabled'} else {'Disabled - enable for smaller backups'})

Add-Check 'Config' 'Optimize for ad hoc workloads' `
    $(if ($cfg['optimize for ad hoc workloads'] -eq 1) {'PASS'} else {'WARN'}) `
    $(if ($cfg['optimize for ad hoc workloads'] -eq 1) {'Enabled'} else {'Disabled - enable to reduce plan cache bloat'})

# ── TempDB ────────────────────────────────────────────────────────────────────
$tempdbFiles = Invoke-Sqlcmd -ServerInstance $ServerInstance `
                   -Query "SELECT COUNT(*) AS n FROM tempdb.sys.database_files WHERE type = 0" `
                   -TrustServerCertificate -ErrorAction SilentlyContinue
$fileCount = [int]$tempdbFiles.n
$logicalCPUs = (Get-CimInstance Win32_ComputerSystem -ErrorAction SilentlyContinue).NumberOfLogicalProcessors
$recommendedFiles = [math]::Min($logicalCPUs, 8)
Add-Check 'TempDB' 'TempDB data file count' `
    $(if ($fileCount -ge $recommendedFiles) {'PASS'} elseif ($fileCount -ge 2) {'WARN'} else {'WARN'}) `
    "$fileCount files (recommended: $recommendedFiles for this CPU count)"

# ── Network ───────────────────────────────────────────────────────────────────
$tcpCheck = Invoke-Sqlcmd -ServerInstance $ServerInstance `
                -Query "SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID" `
                -TrustServerCertificate -ErrorAction SilentlyContinue
$tcpPort = $tcpCheck.local_tcp_port
Add-Check 'Network' 'TCP/IP connection' `
    $(if ($tcpPort) {'PASS'} else {'WARN'}) `
    $(if ($tcpPort) {"Port $tcpPort"} else {'Could not confirm TCP port'})

# ── Security ──────────────────────────────────────────────────────────────────
$saRow = Invoke-Sqlcmd -ServerInstance $ServerInstance `
             -Query "SELECT is_disabled FROM sys.server_principals WHERE name = 'sa'" `
             -TrustServerCertificate -ErrorAction SilentlyContinue
if ($saRow) {
    Add-Check 'Security' 'SA account disabled' `
        $(if ($saRow.is_disabled -eq 1) {'PASS'} else {'WARN'}) `
        $(if ($saRow.is_disabled -eq 1) {'SA is disabled'} else {'SA is ENABLED - disable if not needed'})
}

$authMode = Invoke-Sqlcmd -ServerInstance $ServerInstance `
                -Query "SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WinOnly" `
                -TrustServerCertificate -ErrorAction SilentlyContinue
Add-Check 'Security' 'Authentication mode' 'PASS' `
    $(if ($authMode.WinOnly -eq 1) {'Windows auth only'} else {'Mixed mode (Windows + SQL)'})

} else {
    Write-Host "  Cannot connect - skipping configuration, TempDB, network, and security checks." -ForegroundColor Yellow
}

# ── Summary ───────────────────────────────────────────────────────────────────
Write-Host ""
Write-Host ("  " + [string]::new('=', 62)) -ForegroundColor DarkCyan
$summaryColor = if ($fail -gt 0) {'Red'} elseif ($warn -gt 0) {'Yellow'} else {'Green'}
$verdict = if ($fail -gt 0) {'ISSUES FOUND - review FAIL items'} `
           elseif ($warn -gt 0) {'PASSED WITH WARNINGS - review WARN items'} `
           else {'ALL CHECKS PASSED'}
Write-Host "  $verdict" -ForegroundColor $summaryColor
Write-Host "  PASS: $pass   WARN: $warn   FAIL: $fail" -ForegroundColor $summaryColor
Write-Host ""

Real Output From This Lab Machine

6 genuine WARN findings on a real, working instance. Every one of them is fixable with Configure SQL Server, which applies exactly this class of setting.

What Each Check Means

How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

# On the machine that will host the instance, elevated:
.\powershell\installation\pre-install-check.ps1 -SqlVersion 2022 -DataDir D:\SQLData -LogDir L:\SQLLogs

# After setup, local default instance:
.\powershell\installation\post-install-validation.ps1

# A named instance on another server:
.\powershell\installation\post-install-validation.ps1 -ServerInstance PROD01\SQL2022 -InstanceName SQL2022

These scripts live in the repo at:

• powershell/installation/pre-install-check.ps1
• powershell/installation/post-install-validation.ps1

Microsoft’s reference covers sp_configure, sys.dm_exec_connections and sys.configurations in full.

Frequently Asked Questions

Summary

2 read-only bookend checks, one before an install and one after. Run against a real, already-installed lab machine, pre-install-check.ps1 correctly flagged 5 genuine blockers (disk space, an existing instance, a shell that was not elevated) and post-install-validation.ps1 found 6 genuine configuration gaps on the working instance, every one of them fixable with the companion Configure SQL Server script.

Related Scripts