Configuration

Invoke-sqmSetupReport

Generates a professional dark-theme HTML setup report for a SQL Server instance — covering critical issues, security posture, infrastructure, configuration, and all user databases.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: No
Output: String (file path)

Execution Flow

START dbatools available? No throw error Yes Connect-DbaInstance CRITICAL ISSUES SA Account (SID check) Backup Jobs status MaxMemory (85–95%) SECURITY Sysadmins (role query) Logins + server roles CLR / xp_cmdshell INFRASTRUCTURE CIM Service Accounts Get-sqmSpnReport Invoke-sqmSplunk… CONFIGURATION + DATABASES MAXDOP / CTP / TempDB Get-DbaDatabase (user) _Build-ModernReportHtml → Out-File DONE
No SupportsShouldProcess — this function is read-only except for writing the HTML output file. It delegates HTML generation entirely to the private helper _Build-ModernReportHtml.

Parameters

ParameterTypeRequiredDescription
-SqlInstance String Optional Target SQL Server instance. Defaults to $env:COMPUTERNAME.
-SqlCredential PSCredential Optional SQL authentication credentials. Omit for Windows authentication.
-OutputPath String Optional Directory for the HTML report file. Falls back to Get-sqmConfig 'OutputPath', then C:\System\WinSrvLog\MSSQL.
-PassThru Switch Optional Return the full path of the generated HTML file.
-NoOpen Switch Optional Suppress automatic opening of the report in the browser.

Report Sections

SectionChecks performed
CRITICAL ISSUES SA account (name + disabled state via Well-Known SID S-1-5-32-544), Backup Agent Jobs (enabled count), Max Server Memory (85–95% of physical RAM tolerance)
SECURITY Sysadmin members via sys.server_role_members, logins with server roles via IS_SRVROLEMEMBER(), CLR enabled flag, xp_cmdshell enabled flag, BUILTIN\Administrators sysadmin warning
INFRASTRUCTURE SQL Server + SQL Agent service accounts via CIM Win32_Service, SPN status via Get-sqmSpnReport, Splunk status via Invoke-sqmSplunkConfiguration -Mode Test
CONFIGURATION MAXDOP (vs. CPU count recommendation), Cost Threshold for Parallelism (recommended ≥ 50), TempDB file count (vs. ideal = min(CPUs, 8))
DATABASES All user databases: Name, Recovery Model, Owner (DBO), Last Full Backup age in days

Return Value

Returns [string] — the full path to the generated HTML report file — only when -PassThru is specified. The report is also opened in the default browser unless -NoOpen is set, and copied to the central share via Copy-sqmToCentralPath.

Examples

Example 1 — Local instance (opens in browser)

Invoke-sqmSetupReport

Example 2 — Specific instance, return path

$file = Invoke-sqmSetupReport -SqlInstance "SQL01\PROD" -PassThru
Write-Host "Report: $file"

Example 3 — Custom output path, no browser

Invoke-sqmSetupReport -SqlInstance "SQL02" -OutputPath "D:\Reports\SQL" -NoOpen -PassThru

Example 4 — SQL authentication

$cred = Get-Credential
Invoke-sqmSetupReport -SqlInstance "SQL03" -SqlCredential $cred