sqmSQLTool › Commands › Get-sqmSQLInstanceCheck

Get-sqmSQLInstanceCheck Security

Performs a comprehensive best-practice audit of a SQL Server instance covering MAXDOP, Max Server Memory, Cost Threshold for Parallelism, xp_cmdshell, SA account, sysadmin count, CLR, SQL XPs, TempDB configuration, collation, and SQL Server version. Returns one result row per check with Status OK / Warning / Failed / Info / Error.

Execution Flow

START dbatools available? throw ERROR Connect-DbaInstance -SqlInstance $SqlInstance process: 10 checks — each appends [PSCustomObject]@{ Check, CurrentValue, Recommended, Status, Message } to $results ① MAXDOP: ConfigValue vs. core count → recommended 2..min(cores,16); Status=OK/Warning/Failed ② Max Server Memory: ConfigValue vs. PhysicalMemory; thresholds 85%/90%/95%; unconfigured=2147483647→Warning ③ Cost Threshold for Parallelism: ConfigValue ≥ 50 → OK, else Failed ④ xp_cmdshell: Disabled→OK, Enabled→Warning (security risk) ⑤ SA Account: Get-DbaLogin; name≠'sa' OR IsDisabled→OK, else Failed ⑥ Sysadmin Accounts: Get-DbaLogin | Where IsSysAdmin; count ≤2→OK, ≤4→Warning, >4→Warning ⑦ CLR: IsSqlClrEnabled; Disabled→OK, Enabled→Warning ⑧ SQL XPs: Database Mail XPs, ad hoc distributed queries, remote admin connections, Agent XPs, OLE Automation → Info ⑨ TempDB: file count vs. min(cores,8); equal sizes; Status=OK/Warning (-Detailed: autogrow, backup dir, db collation) ⑩ SQL Server Version: match '2008|2005|2012' → Warning (outdated) ⑪ Instance Collation: report collation string → Info; -Detailed: check user DB collation mismatch -Detailed adds: Backup Directory existence, Autogrow settings (percent vs MB, max 1024 MB), Database collation mismatch vs. instance collation err Return $results [PSCustomObject[]] DONE

Synopsis

Connects via dbatools and runs up to 11 best-practice checks (13 with -Detailed). Each check produces a [PSCustomObject] with a human-readable Message and a Status of OK, Warning, Failed, Info, or Error. The Max Server Memory check uses the same logic as Test-sqmMaxMemory (85%/90%/95% thresholds). MAXDOP recommendation scales with core count: up to 4 cores → core count; 5–8 → 4; 9–16 → 8; >16 → 16.

Requires dbatools. No file output — results are returned as objects only. Pipe to Format-Table or Out-GridView for quick review. Use -Detailed to add autogrow, backup directory, and per-database collation checks.

Check Summary

/ / / / / / / / / /
CheckThreshold / RecommendationStatus values
MAXDOP2..min(cores,16); =0 → Warning; =1 → FailedOKWarningFailed
Max Server Memory85%–95% of physical RAM; default value (2147483647) → WarningOKWarning
Cost Threshold for Parallelism≥ 50OKFailed
xp_cmdshellDisabledOKWarning
SA AccountRenamed and/or disabledOKFailed
Sysadmin Accounts≤ 2 preferredOKWarning
CLRDisabled unless requiredOKWarning
SQL XPs (5 settings)Enabled only if neededInfo
TempDB ConfigurationFile count = min(cores,8); equal sizesOKWarning
SQL Server VersionNot 2005 / 2008 / 2012OKWarning
Instance CollationEnterprise standardInfo

Syntax

Get-sqmSQLInstanceCheck
    [-SqlInstance <String>]       # default $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    [-Detailed]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredential—SQL connection credential.
-DetailedSwitchfalseEnable additional checks: autogrow settings per user DB, backup directory existence, per-database collation vs. instance collation.
-EnableExceptionSwitchfalseThrow terminating exceptions instead of appending an Error row.

Return Value (per row)

PropertyDescription
CheckCheck name (e.g. MAXDOP, SA Account).
CurrentValueObserved value on the instance.
RecommendedBest-practice recommendation string.
StatusOK / Warning / Failed / Info / Error.
MessageHuman-readable explanation.

Examples

# Standard check on local instance
Get-sqmSQLInstanceCheck | Format-Table -AutoSize

# Detailed check on named instance
Get-sqmSQLInstanceCheck -SqlInstance "SQL01\PROD" -Detailed

# Show only failed/warning checks
Get-sqmSQLInstanceCheck | Where-Object { $_.Status -in 'Failed','Warning' }