I have a new script I'd like to share
<#
.SYNOPSIS
Audits a newly installed SQL Server and generates a polished HTML report.
.DESCRIPTION
Collects a broad set of SQL Server, OS, storage, security, database, backup,
Agent, HA/DR, and health metrics. Intended for post-install/post-build validation.
.NOTES
Author: OpenAI
Requirements:
- PowerShell 5.1+ or PowerShell 7+
- Permissions to connect to SQL Server and read DMVs/catalog views
- Optional: SqlServer PowerShell module for Invoke-Sqlcmd
Example:
.\Invoke-SqlServerNewInstallAudit.ps1 -SqlInstance "SERVER\INST1" -OutputPath "C:\Temp\SqlAudit.html"
#>
[CmdletBinding()]
param(
[Parameter(Mandatory)]
[string]$SqlInstance,
[string]$OutputPath = ".\SqlServer-NewInstall-Audit.html",
[int]$TopDbRows = 25,
[switch]$TrustServerCertificate
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
#region helpers
function Write-Log {
param([string]$Message)
Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] $Message"
}
function Add-Metric {
param(
[Parameter(Mandatory)][System.Collections.Generic.List[object]]$MetricList,
[Parameter(Mandatory)][string]$Category,
[Parameter(Mandatory)][string]$Metric,
[Parameter()][object]$Value,
[Parameter()][string]$Status = 'Info',
[Parameter()][string]$Notes = ''
)
$MetricList.Add([pscustomobject]@{
Category = $Category
Metric = $Metric
Value = if ($null -eq $Value) { '' } else { [string]$Value }
Status = $Status
Notes = $Notes
})
}
function Convert-BytesToNiceString {
param([Nullable[decimal]]$Bytes)
if ($null -eq $Bytes) { return '' }
$sizes = "B","KB","MB","GB","TB","PB"
$order = 0
$value = [decimal]$Bytes
while ($value -ge 1024 -and $order -lt ($sizes.Count - 1)) {
$order++
$value = $value / 1024
}
return "{0:N2} {1}" -f $value, $sizes[$order]
}
function New-SqlConnectionString {
param(
[string]$ServerInstance,
[switch]$TrustServerCertificate
)
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder["Data Source"] = $ServerInstance
$builder["Initial Catalog"] = "master"
$builder["Integrated Security"] = $true
$builder["Application Name"] = "SQL New Install Audit"
$builder["Connect Timeout"] = 15
if ($TrustServerCertificate) {
$builder["TrustServerCertificate"] = $true
}
return $builder.ConnectionString
}
function Invoke-SqlQuery {
param(
[string]$ServerInstance,
[string]$Query,
[switch]$TrustServerCertificate
)
if (Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue) {
try {
return Invoke-Sqlcmd -ServerInstance $ServerInstance -Database master -Query $Query -TrustServerCertificate:$TrustServerCertificate
}
catch {
Write-Verbose "Invoke-Sqlcmd failed; falling back to SqlClient. $_"
}
}
$connString = New-SqlConnectionString -ServerInstance $ServerInstance -TrustServerCertificate:$TrustServerCertificate
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
$cmd = $conn.CreateCommand()
$cmd.CommandTimeout = 120
$cmd.CommandText = $Query
$da = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
$dt = New-Object System.Data.DataTable
$null = $da.Fill($dt)
$conn.Close()
return $dt
}
function Try-GetValue {
param(
[object]$Row,
[string]$ColumnName
)
try {
return $Row.$ColumnName
}
catch {
return $null
}
}
function Get-SafeWmi {
param(
[string]$ClassName,
[string]$ComputerName = $env:COMPUTERNAME
)
try {
if (Get-Command Get-CimInstance -ErrorAction SilentlyContinue) {
return Get-CimInstance -ClassName $ClassName -ComputerName $ComputerName -ErrorAction Stop
} else {
return Get-WmiObject -Class $ClassName -ComputerName $ComputerName -ErrorAction Stop
}
}
catch {
return $null
}
}
function Get-HtmlEncoded {
param([string]$Text)
if ($null -eq $Text) { return '' }
return [System.Net.WebUtility]::HtmlEncode($Text)
}
function Convert-TableToHtml {
param(
[Parameter(Mandatory)][System.Collections.IEnumerable]$Data,
[string]$Title = '',
[string[]]$Columns
)
$sb = New-Object System.Text.StringBuilder
if ($Title) {
[void]$sb.AppendLine("<h2>$([System.Net.WebUtility]::HtmlEncode($Title))</h2>")
}
$rows = @($Data)
if (-not $rows -or $rows.Count -eq 0) {
[void]$sb.AppendLine("<div class='empty'>No data returned.</div>")
return $sb.ToString()
}
if (-not $Columns) {
$Columns = $rows[0].PSObject.Properties.Name
}
[void]$sb.AppendLine("<table>")
[void]$sb.AppendLine("<thead><tr>")
foreach ($c in $Columns) {
[void]$sb.AppendLine("<th>$([System.Net.WebUtility]::HtmlEncode($c))</th>")
}
[void]$sb.AppendLine("</tr></thead>")
[void]$sb.AppendLine("<tbody>")
foreach ($row in $rows) {
[void]$sb.AppendLine("<tr>")
foreach ($c in $Columns) {
$val = ''
try { $val = $row.$c } catch {}
$class = ''
if ($c -eq 'Status') {
switch -Regex ($val) {
'^Pass$' { $class = 'status-pass' }
'^Warn$' { $class = 'status-warn' }
'^Fail$' { $class = 'status-fail' }
default { $class = 'status-info' }
}
}
[void]$sb.AppendLine("<td class='$class'>$([System.Net.WebUtility]::HtmlEncode([string]$val))</td>")
}
[void]$sb.AppendLine("</tr>")
}
[void]$sb.AppendLine("</tbody></table>")
return $sb.ToString()
}
function Get-StatusFromRule {
param(
[string]$Metric,
[object]$Value
)
# Light rules for common install validation checks
switch -Regex ($Metric) {
'^SQL Server service status$' { if ($Value -eq 'Running') { 'Pass' } else { 'Fail' } ; break }
'^SQL Server Agent service status$' { if ($Value -eq 'Running') { 'Pass' } else { 'Warn' } ; break }
'^xp_cmdshell$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^clr enabled$' { 'Info'; break }
'^remote admin connections$' { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
'^cost threshold for parallelism$' { if ([int]$Value -ge 25) { 'Pass' } else { 'Warn' } ; break }
'^max degree of parallelism$' { 'Info'; break }
'^optimize for ad hoc workloads$' { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
'^backup compression default$' { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
'^default backup checksum$' { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
'^Database Mail XPs$' { 'Info'; break }
'^contained database authentication$' { 'Info'; break }
'^cross db ownership chaining$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^default trace enabled$' { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
'^priority boost$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^scan for startup procs$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^show advanced options$' { 'Info'; break }
'^Ole Automation Procedures$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^SMO and DMO XPs$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^ad hoc distributed queries$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^filestream access level$' { 'Info'; break }
'^lightweight pooling$' { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
'^default data path$' { 'Info'; break }
'^default log path$' { 'Info'; break }
'^instant file initialization enabled$' { if ($Value -eq 'Yes') { 'Pass' } else { 'Warn' } ; break }
'^Lock pages in memory enabled$' { 'Info'; break }
'^Pending restart$' { if ($Value -eq 'No') { 'Pass' } else { 'Warn' } ; break }
default { 'Info' }
}
}
#endregion helpers
Write-Log "Starting audit for $SqlInstance"
# Storage objects
$metrics = New-Object 'System.Collections.Generic.List[object]'
$dbSummary = @()
$fileSummary = @()
$jobSummary = @()
$loginSummary = @()
$backupSummary = @()
$driveSummary = @()
$errorSummary = @()
$waitSummary = @()
$tempdbSummary = @()
$agSummary = @()
#region OS/WMI
Write-Log "Collecting OS/WMI details"
$comp = Get-SafeWmi -ClassName Win32_ComputerSystem
$os = Get-SafeWmi -ClassName Win32_OperatingSystem
$bios = Get-SafeWmi -ClassName Win32_BIOS
$proc = @(Get-SafeWmi -ClassName Win32_Processor)
$svc = @(Get-SafeWmi -ClassName Win32_Service)
$disks = @(Get-SafeWmi -ClassName Win32_LogicalDisk | Where-Object { $_.DriveType -eq 3 })
if ($comp) {
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Computer name' -Value $comp.Name
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Domain' -Value $comp.Domain
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Manufacturer' -Value $comp.Manufacturer
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Model' -Value $comp.Model
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Physical memory total' -Value (Convert-BytesToNiceString $comp.TotalPhysicalMemory)
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Logical processors (WMI)' -Value $comp.NumberOfLogicalProcessors
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Processor sockets' -Value $comp.NumberOfProcessors
}
if ($os) {
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS caption' -Value $os.Caption
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS version' -Value $os.Version
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS build number' -Value $os.BuildNumber
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS architecture' -Value $os.OSArchitecture
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Last boot time' -Value $os.LastBootUpTime
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'System drive' -Value $os.SystemDrive
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Windows directory' -Value $os.WindowsDirectory
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Free physical memory' -Value (Convert-BytesToNiceString ([decimal]$os.FreePhysicalMemory * 1KB))
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Free virtual memory' -Value (Convert-BytesToNiceString ([decimal]$os.FreeVirtualMemory * 1KB))
}
if ($bios) {
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'BIOS version' -Value (($bios.SMBIOSBIOSVersion, $bios.Version -join ' | ').Trim(' |'))
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'BIOS serial' -Value $bios.SerialNumber
}
if ($proc) {
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU count (rows)' -Value $proc.Count
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU name' -Value (($proc | Select-Object -First 1).Name)
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU max clock MHz' -Value (($proc | Measure-Object -Property MaxClockSpeed -Maximum).Maximum)
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU cores total' -Value (($proc | Measure-Object -Property NumberOfCores -Sum).Sum)
Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU logical total' -Value (($proc | Measure-Object -Property NumberOfLogicalProcessors -Sum).Sum)
}
foreach ($d in $disks) {
$driveSummary += [pscustomobject]@{
Drive = $d.DeviceID
Label = $d.VolumeName
FileSystem = $d.FileSystem
Size = Convert-BytesToNiceString $d.Size
Free = Convert-BytesToNiceString $d.FreeSpace
FreePct = if ($d.Size -gt 0) { "{0:N1}" -f (($d.FreeSpace / $d.Size) * 100) } else { '' }
}
Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) filesystem" -Value $d.FileSystem
Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) label" -Value $d.VolumeName
Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) size" -Value (Convert-BytesToNiceString $d.Size)
Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) free" -Value (Convert-BytesToNiceString $d.FreeSpace)
Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) free percent" -Value (if ($d.Size -gt 0) { "{0:N1}%" -f (($d.FreeSpace / $d.Size) * 100) } else { '' })
}
#endregion OS/WMI
#region SQL queries
Write-Log "Collecting SQL details"
$serverPropsQuery = @"
SET NOCOUNT ON;
SELECT
@@SERVERNAME AS ServerName,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('ServerName') AS FullServerName,
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('IsClustered') AS IsClustered,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS PhysicalNetBIOS,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,
SERVERPROPERTY('EngineEdition') AS EngineEdition,
SERVERPROPERTY('HadrManagerStatus') AS HadrManagerStatus,
SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
SERVERPROPERTY('Collation') AS Collation,
SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel,
SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath,
SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
SERVERPROPERTY('LCID') AS LCID,
SERVERPROPERTY('SqlCharSet') AS SqlCharSet,
SERVERPROPERTY('SqlSortOrder') AS SqlSortOrder,
SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName;
"@
$configQuery = @"
SET NOCOUNT ON;
SELECT name, value, value_in_use, description
FROM sys.configurations
ORDER BY name;
"@
$sysInfoQuery = @"
SET NOCOUNT ON;
SELECT
cpu_count,
scheduler_count,
hyperthread_ratio,
numa_node_count,
softnuma_configuration_desc,
sockets,
cores_per_socket,
max_workers_count,
physical_memory_kb,
committed_kb,
committed_target_kb,
visible_target_kb,
sql_memory_model_desc,
stack_size_in_bytes,
virtual_machine_type_desc,
sqlserver_start_time
FROM sys.dm_os_sys_info;
"@
$memoryQuery = @"
SET NOCOUNT ON;
SELECT
total_physical_memory_kb,
available_physical_memory_kb,
total_page_file_kb,
available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
"@
$processMemQuery = @"
SET NOCOUNT ON;
SELECT
physical_memory_in_use_kb,
large_page_allocations_kb,
locked_page_allocations_kb,
virtual_address_space_reserved_kb,
virtual_address_space_committed_kb,
page_fault_count,
memory_utilization_percentage,
available_commit_limit_kb,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
"@
$servicesQuery = @"
SET NOCOUNT ON;
SELECT servicename, startup_type_desc, status_desc, last_startup_time, service_account, is_clustered, cluster_nodename, filename
FROM sys.dm_server_services
ORDER BY servicename;
"@
$dbQuery = @"
SET NOCOUNT ON;
SELECT
d.database_id,
d.name,
d.state_desc,
d.user_access_desc,
d.recovery_model_desc,
d.compatibility_level,
d.containment_desc,
d.collation_name,
d.is_auto_close_on,
d.is_auto_shrink_on,
d.is_auto_create_stats_on,
d.is_auto_update_stats_on,
d.is_auto_update_stats_async_on,
d.is_read_only,
d.is_encrypted,
d.is_trustworthy_on,
d.is_query_store_on,
d.snapshot_isolation_state_desc,
d.is_read_committed_snapshot_on,
d.log_reuse_wait_desc,
d.page_verify_option_desc,
d.target_recovery_time_in_seconds,
d.create_date,
SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END) * 8.0 / 1024 AS DataSizeMB,
SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size END) * 8.0 / 1024 AS LogSizeMB
FROM sys.databases d
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY
d.database_id,d.name,d.state_desc,d.user_access_desc,d.recovery_model_desc,d.compatibility_level,
d.containment_desc,d.collation_name,d.is_auto_close_on,d.is_auto_shrink_on,d.is_auto_create_stats_on,
d.is_auto_update_stats_on,d.is_auto_update_stats_async_on,d.is_read_only,d.is_encrypted,d.is_trustworthy_on,
d.is_query_store_on,d.snapshot_isolation_state_desc,d.is_read_committed_snapshot_on,d.log_reuse_wait_desc,
d.page_verify_option_desc,d.target_recovery_time_in_seconds,d.create_date
ORDER BY d.database_id;
"@
$fileQuery = @"
SET NOCOUNT ON;
SELECT
DB_NAME(mf.database_id) AS DatabaseName,
mf.file_id,
mf.type_desc,
mf.name AS LogicalName,
mf.physical_name,
mf.state_desc,
mf.size * 8.0 / 1024 AS SizeMB,
CASE WHEN mf.max_size = -1 THEN -1 ELSE mf.max_size * 8.0 / 1024 END AS MaxSizeMB,
mf.growth,
mf.is_percent_growth,
vs.volume_mount_point,
vs.logical_volume_name,
vs.file_system_type,
CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS decimal(18,2)) AS VolumeSizeGB,
CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS decimal(18,2)) AS VolumeFreeGB
FROM sys.master_files mf
OUTER APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY DB_NAME(mf.database_id), mf.type_desc, mf.file_id;
"@
$tempdbQuery = @"
SET NOCOUNT ON;
SELECT
name,
file_id,
type_desc,
physical_name,
size * 8.0 / 1024 AS SizeMB,
growth,
is_percent_growth
FROM tempdb.sys.database_files
ORDER BY file_id;
"@
$backupQuery = @"
SET NOCOUNT ON;
WITH b AS (
SELECT
database_name,
type,
MAX(backup_finish_date) AS LastBackupFinishDate
FROM msdb.dbo.backupset
GROUP BY database_name, type
)
SELECT
d.name,
d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' THEN b.LastBackupFinishDate END) AS LastFull,
MAX(CASE WHEN b.type = 'I' THEN b.LastBackupFinishDate END) AS LastDiff,
MAX(CASE WHEN b.type = 'L' THEN b.LastBackupFinishDate END) AS LastLog
FROM sys.databases d
LEFT JOIN b ON d.name = b.database_name
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;
"@
$jobQuery = @"
SET NOCOUNT ON;
SELECT
j.name,
j.enabled,
j.description,
SUSER_SNAME(j.owner_sid) AS OwnerName,
c.name AS CategoryName,
CASE WHEN js.job_id IS NULL THEN 0 ELSE 1 END AS HasSchedule
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT JOIN (
SELECT DISTINCT job_id FROM msdb.dbo.sysjobschedules
) js ON j.job_id = js.job_id
ORDER BY j.name;
"@
$jobHistoryQuery = @"
SET NOCOUNT ON;
;WITH last_run AS (
SELECT
j.name,
h.run_status,
h.run_date,
h.run_time,
ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY h.instance_id DESC) AS rn
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
AND h.step_id = 0
)
SELECT name, run_status, run_date, run_time
FROM last_run
WHERE rn = 1
ORDER BY name;
"@
$loginQuery = @"
SET NOCOUNT ON;
SELECT
name,
type_desc,
is_disabled,
default_database_name,
create_date,
modify_date
FROM sys.server_principals
WHERE type IN ('S','U','G','R','C','K')
ORDER BY type_desc, name;
"@
$sqlLoginPolicyQuery = @"
SET NOCOUNT ON;
SELECT
name,
is_policy_checked,
is_expiration_checked
FROM sys.sql_logins
ORDER BY name;
"@
$linkQuery = @"
SET NOCOUNT ON;
SELECT name, product, provider, data_source, is_linked, is_remote_login_enabled, is_rpc_out_enabled, modify_date
FROM sys.servers
ORDER BY server_id;
"@
$endpointQuery = @"
SET NOCOUNT ON;
SELECT name, type_desc, state_desc, is_admin_endpoint, protocol_desc
FROM sys.endpoints
ORDER BY name;
"@
$permQuery = @"
SET NOCOUNT ON;
SELECT
sp.name AS principal_name,
sp.type_desc,
perm.permission_name,
perm.state_desc
FROM sys.server_permissions perm
JOIN sys.server_principals sp
ON perm.grantee_principal_id = sp.principal_id
ORDER BY sp.name, perm.permission_name;
"@
$haQuery = @"
SET NOCOUNT ON;
SELECT
ag.name AS AGName,
ag.cluster_type_desc,
ag.automated_backup_preference_desc,
ar.replica_server_name,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.seeding_mode_desc,
ar.secondary_role_allow_connections_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
ON ag.group_id = ar.group_id
ORDER BY ag.name, ar.replica_server_name;
"@
$haDbQuery = @"
SET NOCOUNT ON;
SELECT
ag.name AS AGName,
adc.database_name,
drs.is_local,
drs.synchronization_state_desc,
drs.synchronization_health_desc,
drs.is_suspended,
drs.last_commit_time
FROM sys.availability_groups ag
JOIN sys.availability_databases_cluster adc
ON ag.group_id = adc.group_id
LEFT JOIN sys.dm_hadr_database_replica_states drs
ON adc.group_database_id = drs.group_database_id
ORDER BY ag.name, adc.database_name;
"@
$waitsQuery = @"
SET NOCOUNT ON;
SELECT TOP (15)
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
AND wait_type NOT IN (
'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','BROKER_TO_FLUSH','BROKER_TASK_STOP',
'BROKER_EVENTHANDLER','FT_IFTS_SCHEDULER_IDLE_WAIT','BROKER_RECEIVE_WAITFOR',
'ONDEMAND_TASK_QUEUE','DBMIRROR_EVENTS_QUEUE','DBMIRRORING_CMD','SQLTRACE_BUFFER_FLUSH',
'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE','CHECKPOINT_QUEUE','BROKER_TRANSMITTER',
'HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP'
)
ORDER BY wait_time_ms DESC;
"@
$errorLogCountQuery = @"
SET NOCOUNT ON;
DECLARE @ErrorLog TABLE (
ArchiveNo int,
[Date] datetime,
LogFileSizeBytes bigint
);
INSERT INTO @ErrorLog
EXEC master.dbo.xp_enumerrorlogs;
SELECT * FROM @ErrorLog ORDER BY ArchiveNo;
"@
$traceFlagsQuery = @"
DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;
"@
$dbccPageVerifyQuery = @"
SET NOCOUNT ON;
SELECT name, page_verify_option_desc
FROM sys.databases
ORDER BY name;
"@
$featureQuery = @"
SET NOCOUNT ON;
SELECT
SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported,
SERVERPROPERTY('IsPolyBaseInstalled') AS IsPolyBaseInstalled,
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;
"@
$resourceGovQuery = @"
SET NOCOUNT ON;
SELECT is_enabled, classifier_function_id
FROM sys.resource_governor_configuration;
"@
$serverAuditQuery = @"
SET NOCOUNT ON;
SELECT name, status_desc, audit_file_path, maxsize, max_rollover_files
FROM sys.server_audits
ORDER BY name;
"@
$credProxyMailQuery = @"
SET NOCOUNT ON;
SELECT 'Credential' AS ItemType, name, create_date, modify_date
FROM sys.credentials
UNION ALL
SELECT 'Proxy' AS ItemType, name, NULL, NULL
FROM msdb.dbo.sysproxies
UNION ALL
SELECT 'DBMailProfile' AS ItemType, name, NULL, NULL
FROM msdb.dbo.sysmail_profile
ORDER BY ItemType, name;
"@
$policyQuery = @"
SET NOCOUNT ON;
SELECT name, condition_id, root_condition_id, is_enabled, created_by, date_created
FROM msdb.dbo.syspolicy_policies
ORDER BY name;
"@
$operatorQuery = @"
SET NOCOUNT ON;
SELECT name, enabled, email_address, pager_address
FROM msdb.dbo.sysoperators
ORDER BY name;
"@
$queryStoreStateQuery = @"
SET NOCOUNT ON;
SELECT
name,
is_query_store_on,
CASE WHEN is_query_store_on = 1 THEN CAST(DATABASEPROPERTYEX(name,'Updateability') AS nvarchar(60)) ELSE NULL END AS Updateability
FROM sys.databases
ORDER BY name;
"@
$instanceProps = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $serverPropsQuery -TrustServerCertificate:$TrustServerCertificate
$configs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $configQuery -TrustServerCertificate:$TrustServerCertificate
$sysInfo = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $sysInfoQuery -TrustServerCertificate:$TrustServerCertificate
$osMem = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $memoryQuery -TrustServerCertificate:$TrustServerCertificate
$procMem = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $processMemQuery -TrustServerCertificate:$TrustServerCertificate
$services = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $servicesQuery -TrustServerCertificate:$TrustServerCertificate
$dbs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $dbQuery -TrustServerCertificate:$TrustServerCertificate
$files = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $fileQuery -TrustServerCertificate:$TrustServerCertificate
$tempdbFiles = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $tempdbQuery -TrustServerCertificate:$TrustServerCertificate
$backups = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $backupQuery -TrustServerCertificate:$TrustServerCertificate
$jobs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $jobQuery -TrustServerCertificate:$TrustServerCertificate
$jobHistory = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $jobHistoryQuery -TrustServerCertificate:$TrustServerCertificate
$logins = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $loginQuery -TrustServerCertificate:$TrustServerCertificate
$sqlLoginPol = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $sqlLoginPolicyQuery -TrustServerCertificate:$TrustServerCertificate
$linkedServers = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $linkQuery -TrustServerCertificate:$TrustServerCertificate
$endpoints = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $endpointQuery -TrustServerCertificate:$TrustServerCertificate
$permissions = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $permQuery -TrustServerCertificate:$TrustServerCertificate
$agReplicas = $null
$agDbs = $null
try { $agReplicas = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $haQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
try { $agDbs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $haDbQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
$waits = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $waitsQuery -TrustServerCertificate:$TrustServerCertificate
$errorLogs = $null
try { $errorLogs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $errorLogCountQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
$traceFlags = $null
try { $traceFlags = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $traceFlagsQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
$pageVerify = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $dbccPageVerifyQuery -TrustServerCertificate:$TrustServerCertificate
$features = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $featureQuery -TrustServerCertificate:$TrustServerCertificate
$rgConfig = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $resourceGovQuery -TrustServerCertificate:$TrustServerCertificate
$audits = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $serverAuditQuery -TrustServerCertificate:$TrustServerCertificate
$otherObjects = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $credProxyMailQuery -TrustServerCertificate:$TrustServerCertificate
$policies = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $policyQuery -TrustServerCertificate:$TrustServerCertificate
$operators = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $operatorQuery -TrustServerCertificate:$TrustServerCertificate
$qstore = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $queryStoreStateQuery -TrustServerCertificate:$TrustServerCertificate
#endregion SQL queries
#region summarize server properties
Write-Log "Building metric list"
$sp = $instanceProps | Select-Object -First 1
if ($sp) {
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ServerName' -Value $sp.ServerName
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'MachineName' -Value $sp.MachineName
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'FullServerName' -Value $sp.FullServerName
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'InstanceName' -Value $sp.InstanceName
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Edition' -Value $sp.Edition
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductVersion' -Value $sp.ProductVersion
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductLevel' -Value $sp.ProductLevel
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductUpdateLevel' -Value $sp.ProductUpdateLevel
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductUpdateReference' -Value $sp.ProductUpdateReference
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'EngineEdition' -Value $sp.EngineEdition
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'IsClustered' -Value $sp.IsClustered
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'PhysicalNetBIOS' -Value $sp.PhysicalNetBIOS
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Collation' -Value $sp.Collation
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Windows auth only' -Value $sp.IsIntegratedSecurityOnly
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'FilestreamConfiguredLevel' -Value $sp.FilestreamConfiguredLevel
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'FilestreamEffectiveLevel' -Value $sp.FilestreamEffectiveLevel
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Default data path' -Value $sp.DefaultDataPath
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Default log path' -Value $sp.DefaultLogPath
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Build CLR version' -Value $sp.BuildClrVersion
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'LCID' -Value $sp.LCID
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'SqlCharSet' -Value $sp.SqlCharSet
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'SqlSortOrder' -Value $sp.SqlSortOrder
Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'SqlSortOrderName' -Value $sp.SqlSortOrderName
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'IsHadrEnabled' -Value $sp.IsHadrEnabled
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'HadrManagerStatus' -Value $sp.HadrManagerStatus
}
$si = $sysInfo | Select-Object -First 1
if ($si) {
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'cpu_count' -Value $si.cpu_count
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'scheduler_count' -Value $si.scheduler_count
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'hyperthread_ratio' -Value $si.hyperthread_ratio
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'numa_node_count' -Value $si.numa_node_count
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'softnuma_configuration_desc' -Value $si.softnuma_configuration_desc
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'sockets' -Value $si.sockets
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'cores_per_socket' -Value $si.cores_per_socket
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'max_workers_count' -Value $si.max_workers_count
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'physical_memory_kb' -Value (Convert-BytesToNiceString ([decimal]$si.physical_memory_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'committed_kb' -Value (Convert-BytesToNiceString ([decimal]$si.committed_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'committed_target_kb' -Value (Convert-BytesToNiceString ([decimal]$si.committed_target_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'visible_target_kb' -Value (Convert-BytesToNiceString ([decimal]$si.visible_target_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'sql_memory_model_desc' -Value $si.sql_memory_model_desc
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'stack_size_in_bytes' -Value (Convert-BytesToNiceString $si.stack_size_in_bytes)
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'virtual_machine_type_desc' -Value $si.virtual_machine_type_desc
Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'sqlserver_start_time' -Value $si.sqlserver_start_time
}
$osm = $osMem | Select-Object -First 1
if ($osm) {
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS total physical memory' -Value (Convert-BytesToNiceString ([decimal]$osm.total_physical_memory_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS available physical memory' -Value (Convert-BytesToNiceString ([decimal]$osm.available_physical_memory_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS total page file' -Value (Convert-BytesToNiceString ([decimal]$osm.total_page_file_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS available page file' -Value (Convert-BytesToNiceString ([decimal]$osm.available_page_file_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'system_memory_state_desc' -Value $osm.system_memory_state_desc
}
$pm = $procMem | Select-Object -First 1
if ($pm) {
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL physical_memory_in_use' -Value (Convert-BytesToNiceString ([decimal]$pm.physical_memory_in_use_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL large_page_allocations' -Value (Convert-BytesToNiceString ([decimal]$pm.large_page_allocations_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL locked_page_allocations' -Value (Convert-BytesToNiceString ([decimal]$pm.locked_page_allocations_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL VA reserved' -Value (Convert-BytesToNiceString ([decimal]$pm.virtual_address_space_reserved_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL VA committed' -Value (Convert-BytesToNiceString ([decimal]$pm.virtual_address_space_committed_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL page_fault_count' -Value $pm.page_fault_count
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL memory_utilization_percentage' -Value ("{0}%" -f $pm.memory_utilization_percentage)
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL available_commit_limit' -Value (Convert-BytesToNiceString ([decimal]$pm.available_commit_limit_kb * 1KB))
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'process_physical_memory_low' -Value $pm.process_physical_memory_low
Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'process_virtual_memory_low' -Value $pm.process_virtual_memory_low
}
#endregion summarize server properties
#region services
foreach ($s in $services) {
$metricName = switch -Regex ($s.servicename) {
'SQL Server \(' { 'SQL Server service status'; break }
'^SQL Server Agent' { 'SQL Server Agent service status'; break }
default { "$($s.servicename) status" }
}
$status = Get-StatusFromRule -Metric $metricName -Value $s.status_desc
Add-Metric -MetricList $metrics -Category 'Services' -Metric $metricName -Value $s.status_desc -Status $status -Notes "Startup: $($s.startup_type_desc); Account: $($s.service_account)"
Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) startup type" -Value $s.startup_type_desc
Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) last startup" -Value $s.last_startup_time
Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) service account" -Value $s.service_account
Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) clustered" -Value $s.is_clustered
Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) cluster node" -Value $s.cluster_nodename
Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) binary" -Value $s.filename
}
#endregion services
#region configurations
foreach ($cfg in $configs) {
$metricName = $cfg.name
$status = Get-StatusFromRule -Metric $metricName -Value ([string]$cfg.value_in_use)
Add-Metric -MetricList $metrics -Category 'Configuration' -Metric $metricName -Value $cfg.value_in_use -Status $status -Notes "Configured value: $($cfg.value); $($cfg.description)"
}
# Derived config checks
$configHash = @{}
foreach ($c in $configs) { $configHash[$c.name] = $c }
$maxServerMemMB = if ($configHash.ContainsKey('max server memory (MB)')) { [int]$configHash['max server memory (MB)'].value_in_use } else { 0 }
$minServerMemMB = if ($configHash.ContainsKey('min server memory (MB)')) { [int]$configHash['min server memory (MB)'].value_in_use } else { 0 }
$maxDop = if ($configHash.ContainsKey('max degree of parallelism')) { [int]$configHash['max degree of parallelism'].value_in_use } else { 0 }
$ctfp = if ($configHash.ContainsKey('cost threshold for parallelism')) { [int]$configHash['cost threshold for parallelism'].value_in_use } else { 0 }
if ($comp -and $comp.TotalPhysicalMemory) {
$totalMb = [math]::Round($comp.TotalPhysicalMemory / 1MB)
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Max server memory review' -Value "$maxServerMemMB MB" -Status (
if ($maxServerMemMB -eq 2147483647 -or $maxServerMemMB -eq 0) { 'Warn' } else { 'Info' }
) -Notes "OS total memory: $totalMb MB"
}
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Min server memory review' -Value "$minServerMemMB MB" -Status 'Info'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'MAXDOP review' -Value $maxDop -Status 'Info'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'CTFP review' -Value $ctfp -Status (if ($ctfp -lt 25) { 'Warn' } else { 'Pass' })
#endregion configurations
#region security/logins
foreach ($l in $logins) {
$loginSummary += [pscustomobject]@{
Name = $l.name
Type = $l.type_desc
Disabled = $l.is_disabled
DefaultDatabase = $l.default_database_name
CreateDate = $l.create_date
ModifyDate = $l.modify_date
}
Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] type" -Value $l.type_desc
Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] disabled" -Value $l.is_disabled
Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] default DB" -Value $l.default_database_name
Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] create date" -Value $l.create_date
Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] modify date" -Value $l.modify_date
}
foreach ($sl in $sqlLoginPol) {
Add-Metric -MetricList $metrics -Category 'Security' -Metric "SQL Login [$($sl.name)] password policy checked" -Value $sl.is_policy_checked
Add-Metric -MetricList $metrics -Category 'Security' -Metric "SQL Login [$($sl.name)] expiration checked" -Value $sl.is_expiration_checked
}
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Login count total' -Value (@($logins).Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'SQL login count' -Value (@($logins | Where-Object type_desc -eq 'SQL_LOGIN').Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Windows login count' -Value (@($logins | Where-Object type_desc -eq 'WINDOWS_LOGIN').Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Windows group count' -Value (@($logins | Where-Object type_desc -eq 'WINDOWS_GROUP').Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Disabled login count' -Value (@($logins | Where-Object is_disabled -eq 1).Count)
foreach ($ls in $linkedServers) {
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] product" -Value $ls.product
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] provider" -Value $ls.provider
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] data source" -Value $ls.data_source
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] linked" -Value $ls.is_linked
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] remote login enabled" -Value $ls.is_remote_login_enabled
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] RPC out enabled" -Value $ls.is_rpc_out_enabled
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] modified" -Value $ls.modify_date
}
foreach ($ep in $endpoints) {
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] type" -Value $ep.type_desc
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] state" -Value $ep.state_desc
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] admin endpoint" -Value $ep.is_admin_endpoint
Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] protocol" -Value $ep.protocol_desc
}
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Server-level explicit permissions count' -Value (@($permissions).Count)
#endregion security/logins
#region databases
foreach ($db in $dbs) {
$dbSummary += [pscustomobject]@{
Database = $db.name
State = $db.state_desc
RecoveryModel = $db.recovery_model_desc
CompatibilityLevel = $db.compatibility_level
DataSizeMB = [math]::Round([decimal]$db.DataSizeMB,2)
LogSizeMB = [math]::Round([decimal]$db.LogSizeMB,2)
AutoClose = $db.is_auto_close_on
AutoShrink = $db.is_auto_shrink_on
AutoCreateStats = $db.is_auto_create_stats_on
AutoUpdateStats = $db.is_auto_update_stats_on
AutoUpdateStatsAsync= $db.is_auto_update_stats_async_on
ReadOnly = $db.is_read_only
Encrypted = $db.is_encrypted
Trustworthy = $db.is_trustworthy_on
QueryStore = $db.is_query_store_on
RCSI = $db.is_read_committed_snapshot_on
SnapshotIsolation = $db.snapshot_isolation_state_desc
PageVerify = $db.page_verify_option_desc
TargetRecoverySec = $db.target_recovery_time_in_seconds
LogReuseWait = $db.log_reuse_wait_desc
CreateDate = $db.create_date
}
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] state" -Value $db.state_desc -Status (if ($db.state_desc -eq 'ONLINE') { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] recovery model" -Value $db.recovery_model_desc
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] compatibility level" -Value $db.compatibility_level
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] containment" -Value $db.containment_desc
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] collation" -Value $db.collation_name
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_close" -Value $db.is_auto_close_on -Status (if ($db.is_auto_close_on -eq 0) { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_shrink" -Value $db.is_auto_shrink_on -Status (if ($db.is_auto_shrink_on -eq 0) { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_create_stats" -Value $db.is_auto_create_stats_on
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_update_stats" -Value $db.is_auto_update_stats_on
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_update_stats_async" -Value $db.is_auto_update_stats_async_on
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] read_only" -Value $db.is_read_only
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] encrypted" -Value $db.is_encrypted
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] trustworthy" -Value $db.is_trustworthy_on -Status (if ($db.is_trustworthy_on -eq 0) { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] query_store_on" -Value $db.is_query_store_on
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] snapshot isolation" -Value $db.snapshot_isolation_state_desc
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] RCSI" -Value $db.is_read_committed_snapshot_on
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] page verify" -Value $db.page_verify_option_desc -Status (if ($db.page_verify_option_desc -eq 'CHECKSUM') { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] target recovery sec" -Value $db.target_recovery_time_in_seconds
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] log reuse wait" -Value $db.log_reuse_wait_desc
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] data size" -Value ("{0:N2} MB" -f [decimal]$db.DataSizeMB)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] log size" -Value ("{0:N2} MB" -f [decimal]$db.LogSizeMB)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] create date" -Value $db.create_date
}
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count total' -Value (@($dbs).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count online' -Value (@($dbs | Where-Object state_desc -eq 'ONLINE').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count offline' -Value (@($dbs | Where-Object state_desc -ne 'ONLINE').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count FULL recovery' -Value (@($dbs | Where-Object recovery_model_desc -eq 'FULL').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count SIMPLE recovery' -Value (@($dbs | Where-Object recovery_model_desc -eq 'SIMPLE').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count BULK_LOGGED recovery' -Value (@($dbs | Where-Object recovery_model_desc -eq 'BULK_LOGGED').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with AUTO_CLOSE ON' -Value (@($dbs | Where-Object is_auto_close_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with AUTO_SHRINK ON' -Value (@($dbs | Where-Object is_auto_shrink_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with TRUSTWORTHY ON' -Value (@($dbs | Where-Object is_trustworthy_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases encrypted' -Value (@($dbs | Where-Object is_encrypted -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with Query Store ON' -Value (@($dbs | Where-Object is_query_store_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with RCSI ON' -Value (@($dbs | Where-Object is_read_committed_snapshot_on -eq 1).Count)
#endregion databases
#region files/tempdb
foreach ($f in $files) {
$fileSummary += [pscustomobject]@{
Database = $f.DatabaseName
FileId = $f.file_id
Type = $f.type_desc
LogicalName = $f.LogicalName
PhysicalName = $f.physical_name
State = $f.state_desc
SizeMB = [math]::Round([decimal]$f.SizeMB,2)
MaxSizeMB = $f.MaxSizeMB
Growth = $f.growth
PercentGrowth= $f.is_percent_growth
MountPoint = $f.volume_mount_point
VolumeLabel = $f.logical_volume_name
FileSystem = $f.file_system_type
VolumeSizeGB = $f.VolumeSizeGB
VolumeFreeGB = $f.VolumeFreeGB
}
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] type" -Value $f.type_desc
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] state" -Value $f.state_desc
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] size" -Value ("{0:N2} MB" -f [decimal]$f.SizeMB)
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] max size" -Value (if ([decimal]$f.MaxSizeMB -lt 0) { 'Unlimited' } else { "{0:N2} MB" -f [decimal]$f.MaxSizeMB })
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] growth raw" -Value $f.growth
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] percent growth" -Value $f.is_percent_growth -Status (if ($f.is_percent_growth -eq 0) { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] physical path" -Value $f.physical_name
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] mount point" -Value $f.volume_mount_point
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] volume label" -Value $f.logical_volume_name
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] filesystem" -Value $f.file_system_type
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] volume size" -Value ("{0:N2} GB" -f [decimal]$f.VolumeSizeGB)
Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] volume free" -Value ("{0:N2} GB" -f [decimal]$f.VolumeFreeGB)
}
foreach ($tf in $tempdbFiles) {
$tempdbSummary += [pscustomobject]@{
Name = $tf.name
FileId = $tf.file_id
Type = $tf.type_desc
PhysicalName = $tf.physical_name
SizeMB = [math]::Round([decimal]$tf.SizeMB,2)
Growth = $tf.growth
PercentGrowth = $tf.is_percent_growth
}
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] type" -Value $tf.type_desc
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] path" -Value $tf.physical_name
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] size" -Value ("{0:N2} MB" -f [decimal]$tf.SizeMB)
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] growth raw" -Value $tf.growth
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] percent growth" -Value $tf.is_percent_growth -Status (if ($tf.is_percent_growth -eq 0) { 'Pass' } else { 'Warn' })
}
$tempdbDataFileCount = @($tempdbFiles | Where-Object type_desc -eq 'ROWS').Count
$tempdbLogFileCount = @($tempdbFiles | Where-Object type_desc -eq 'LOG').Count
$tempdbDataSizes = @($tempdbFiles | Where-Object type_desc -eq 'ROWS' | Select-Object -ExpandProperty SizeMB)
$tempdbSameSize = $false
if ($tempdbDataSizes.Count -gt 1) {
$tempdbSameSize = (($tempdbDataSizes | Select-Object -Unique).Count -eq 1)
}
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric 'TempDB data file count' -Value $tempdbDataFileCount
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric 'TempDB log file count' -Value $tempdbLogFileCount
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric 'TempDB data files equal size' -Value $(if ($tempdbSameSize) { 'Yes' } else { 'No' }) -Status $(if ($tempdbSameSize -or $tempdbDataFileCount -le 1) { 'Pass' } else { 'Warn' })
#endregion files/tempdb
#region backups
foreach ($b in $backups) {
$backupSummary += [pscustomobject]@{
Database = $b.name
RecoveryModel = $b.recovery_model_desc
LastFull = $b.LastFull
LastDiff = $b.LastDiff
LastLog = $b.LastLog
}
Add-Metric -MetricList $metrics -Category 'Backups' -Metric "Backup [$($b.name)] last full" -Value $b.LastFull -Status (if ($b.LastFull) { 'Pass' } else { 'Warn' })
Add-Metric -MetricList $metrics -Category 'Backups' -Metric "Backup [$($b.name)] last diff" -Value $b.LastDiff
Add-Metric -MetricList $metrics -Category 'Backups' -Metric "Backup [$($b.name)] last log" -Value $b.LastLog -Status (if ($b.recovery_model_desc -eq 'FULL' -and -not $b.LastLog -and $b.name -notin @('master','model','msdb','tempdb')) { 'Warn' } else { 'Info' })
}
Add-Metric -MetricList $metrics -Category 'Backups' -Metric 'Databases with full backup history' -Value (@($backups | Where-Object LastFull).Count)
Add-Metric -MetricList $metrics -Category 'Backups' -Metric 'Databases missing full backup history' -Value (@($backups | Where-Object { -not $_.LastFull }).Count)
Add-Metric -MetricList $metrics -Category 'Backups' -Metric 'FULL recovery DBs missing log backup history' -Value (@($backups | Where-Object { $_.recovery_model_desc -eq 'FULL' -and -not $_.LastLog -and $_.name -notin @('master','model','msdb','tempdb') }).Count)
#endregion backups
#region agent/jobs
foreach ($j in $jobs) {
$hist = $jobHistory | Where-Object name -eq $j.name | Select-Object -First 1
$lastRunStatus = switch ($hist.run_status) {
0 { 'Failed' }
1 { 'Succeeded' }
2 { 'Retry' }
3 { 'Canceled' }
4 { 'In Progress' }
default { 'Never Run' }
}
$jobSummary += [pscustomobject]@{
JobName = $j.name
Enabled = $j.enabled
Owner = $j.OwnerName
Category = $j.CategoryName
HasSchedule = $j.HasSchedule
LastRun = if ($hist.run_date) { "{0} {1:000000}" -f $hist.run_date, [int]$hist.run_time } else { '' }
LastStatus = $lastRunStatus
Description = $j.description
}
Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] enabled" -Value $j.enabled
Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] owner" -Value $j.OwnerName
Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] category" -Value $j.CategoryName
Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] has schedule" -Value $j.HasSchedule
Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] last status" -Value $lastRunStatus -Status (if ($lastRunStatus -eq 'Failed') { 'Warn' } elseif ($lastRunStatus -eq 'Succeeded' -or $lastRunStatus -eq 'Never Run') { 'Info' } else { 'Info' })
}
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Job count total' -Value (@($jobs).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Enabled job count' -Value (@($jobs | Where-Object enabled -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Scheduled job count' -Value (@($jobs | Where-Object HasSchedule -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Operator count' -Value (@($operators).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Proxy count' -Value (@($otherObjects | Where-Object ItemType -eq 'Proxy').Count)
#endregion agent/jobs
#region feature/platform objects
$ft = $features | Select-Object -First 1
if ($ft) {
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsXTPSupported' -Value $ft.IsXTPSupported
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsPolyBaseInstalled' -Value $ft.IsPolyBaseInstalled
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsAdvancedAnalyticsInstalled' -Value $ft.IsAdvancedAnalyticsInstalled
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsFullTextInstalled' -Value $ft.IsFullTextInstalled
}
$rg = $rgConfig | Select-Object -First 1
if ($rg) {
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Resource Governor enabled' -Value $rg.is_enabled
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Resource Governor classifier_function_id' -Value $rg.classifier_function_id
}
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Server audit count' -Value (@($audits).Count)
foreach ($a in $audits) {
Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] status" -Value $a.status_desc
Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] path" -Value $a.audit_file_path
Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] maxsize" -Value $a.maxsize
Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] max_rollover_files" -Value $a.max_rollover_files
}
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Credential count' -Value (@($otherObjects | Where-Object ItemType -eq 'Credential').Count)
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Database Mail profile count' -Value (@($otherObjects | Where-Object ItemType -eq 'DBMailProfile').Count)
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'PBM policy count' -Value (@($policies).Count)
#endregion feature/platform objects
#region AG/HA
if ($agReplicas) {
foreach ($ag in $agReplicas) {
$agSummary += [pscustomobject]@{
AGName = $ag.AGName
ClusterType = $ag.cluster_type_desc
BackupPreference = $ag.automated_backup_preference_desc
ReplicaServer = $ag.replica_server_name
AvailabilityMode = $ag.availability_mode_desc
FailoverMode = $ag.failover_mode_desc
SeedingMode = $ag.seeding_mode_desc
SecondaryConnections = $ag.secondary_role_allow_connections_desc
}
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] cluster type" -Value $ag.cluster_type_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] backup preference" -Value $ag.automated_backup_preference_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] availability mode" -Value $ag.availability_mode_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] failover mode" -Value $ag.failover_mode_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] seeding mode" -Value $ag.seeding_mode_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] secondary allow connections" -Value $ag.secondary_role_allow_connections_desc
}
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'AG replica row count' -Value (@($agReplicas).Count)
} else {
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'AG replica row count' -Value 0
}
if ($agDbs) {
foreach ($agd in $agDbs) {
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] is local" -Value $agd.is_local
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] sync state" -Value $agd.synchronization_state_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] sync health" -Value $agd.synchronization_health_desc
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] suspended" -Value $agd.is_suspended
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] last commit" -Value $agd.last_commit_time
}
Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'AG database row count' -Value (@($agDbs).Count)
}
#endregion AG/HA
#region waits/errors/trace flags/page verify/qstore
foreach ($w in $waits) {
$waitSummary += [pscustomobject]@{
WaitType = $w.wait_type
WaitingTasks = $w.waiting_tasks_count
WaitTimeMs = $w.wait_time_ms
MaxWaitTimeMs = $w.max_wait_time_ms
SignalWaitTimeMs = $w.signal_wait_time_ms
}
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] waiting tasks" -Value $w.waiting_tasks_count
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] wait_time_ms" -Value $w.wait_time_ms
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] max_wait_time_ms" -Value $w.max_wait_time_ms
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] signal_wait_time_ms" -Value $w.signal_wait_time_ms
}
if ($errorLogs) {
Add-Metric -MetricList $metrics -Category 'Health' -Metric 'SQL error log archive count' -Value (@($errorLogs).Count)
foreach ($e in $errorLogs) {
$errorSummary += [pscustomobject]@{
ArchiveNo = $e.ArchiveNo
Date = $e.Date
LogFileSizeBytes = $e.LogFileSizeBytes
}
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Error log archive [$($e.ArchiveNo)] date" -Value $e.Date
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Error log archive [$($e.ArchiveNo)] size bytes" -Value $e.LogFileSizeBytes
}
}
if ($traceFlags) {
foreach ($tf in $traceFlags) {
$traceNum = Try-GetValue -Row $tf -ColumnName 'TraceFlag'
$traceStat = Try-GetValue -Row $tf -ColumnName 'Status'
$traceGlob = Try-GetValue -Row $tf -ColumnName 'Global'
$traceSess = Try-GetValue -Row $tf -ColumnName 'Session'
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Trace flag [$traceNum] status" -Value $traceStat
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Trace flag [$traceNum] global" -Value $traceGlob
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Trace flag [$traceNum] session" -Value $traceSess
}
} else {
Add-Metric -MetricList $metrics -Category 'Health' -Metric 'Trace flags returned' -Value 0
}
foreach ($pv in $pageVerify) {
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Page verify [$($pv.name)]" -Value $pv.page_verify_option_desc -Status (if ($pv.page_verify_option_desc -eq 'CHECKSUM') { 'Pass' } else { 'Warn' })
}
foreach ($qs in $qstore) {
Add-Metric -MetricList $metrics -Category 'Health' -Metric "Query Store [$($qs.name)] enabled" -Value $qs.is_query_store_on
}
#endregion waits/errors/trace flags/page verify/qstore
#region extra derived checks
# Instant File Initialization / LPIM heuristic from service account privilege inspection is not straightforward in raw PowerShell.
# Placeholder metrics based on SQL process memory behavior and common validation notes.
$ifiEnabled = 'Unknown'
try {
# crude heuristic: IFI commonly seen when data file growths are fast, but not deterministically queryable here
# leave as manual review note
$ifiEnabled = 'Manual verification recommended'
}
catch {}
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'instant file initialization enabled' -Value 'Manual verification recommended' -Status 'Info' -Notes 'Check Perform Volume Maintenance Tasks right for SQL Server service account.'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Lock pages in memory enabled' -Value 'Manual verification recommended' -Status 'Info' -Notes 'Check local security policy and locked_page_allocations.'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Pending restart' -Value 'Manual verification recommended' -Status 'Info'
# Count/pass-fail style summaries
$warnCount = @($metrics | Where-Object Status -eq 'Warn').Count
$failCount = @($metrics | Where-Object Status -eq 'Fail').Count
$passCount = @($metrics | Where-Object Status -eq 'Pass').Count
$infoCount = @($metrics | Where-Object Status -eq 'Info').Count
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Total metrics collected' -Value $metrics.Count
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Pass metric count' -Value $passCount
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Warn metric count' -Value $warnCount
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Fail metric count' -Value $failCount
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Info metric count' -Value $infoCount
#endregion extra derived checks
# Re-evaluate summary after adding summary rows
$warnCount = @($metrics | Where-Object Status -eq 'Warn').Count
$failCount = @($metrics | Where-Object Status -eq 'Fail').Count
$passCount = @($metrics | Where-Object Status -eq 'Pass').Count
$infoCount = @($metrics | Where-Object Status -eq 'Info').Count
#region HTML generation
Write-Log "Generating HTML"
$generatedOn = Get-Date
$metricsByCategory = $metrics | Group-Object Category | Sort-Object Name
$topWarnings = $metrics |
Where-Object { $_.Status -in @('Warn','Fail') } |
Sort-Object Category, Metric |
Select-Object -First 50
$summaryCards = @(
[pscustomobject]@{ Label = 'Total Metrics'; Value = $metrics.Count; Css = 'card-neutral' },
[pscustomobject]@{ Label = 'Pass'; Value = $passCount; Css = 'card-pass' },
[pscustomobject]@{ Label = 'Warnings'; Value = $warnCount; Css = 'card-warn' },
[pscustomobject]@{ Label = 'Failures'; Value = $failCount; Css = 'card-fail' },
[pscustomobject]@{ Label = 'Databases'; Value = @($dbs).Count; Css = 'card-neutral' },
[pscustomobject]@{ Label = 'Jobs'; Value = @($jobs).Count; Css = 'card-neutral' }
)
$css = @"
<style>
body {
font-family: Segoe UI, Arial, sans-serif;
margin: 0;
background: #f4f7fb;
color: #1f2937;
}
.header {
background: linear-gradient(135deg, #0f172a, #1d4ed8);
color: white;
padding: 28px 32px;
}
.header h1 {
margin: 0 0 8px 0;
font-size: 30px;
}
.header .sub {
opacity: 0.95;
font-size: 14px;
}
.container {
padding: 24px 28px 40px 28px;
}
.card-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(160px, 1fr));
gap: 14px;
margin-bottom: 24px;
}
.card {
border-radius: 14px;
padding: 18px;
box-shadow: 0 6px 18px rgba(15, 23, 42, 0.08);
background: white;
border-left: 6px solid #64748b;
}
.card .label {
font-size: 12px;
text-transform: uppercase;
letter-spacing: .06em;
color: #475569;
}
.card .value {
font-size: 28px;
font-weight: 700;
margin-top: 8px;
}
.card-pass { border-left-color: #16a34a; }
.card-warn { border-left-color: #f59e0b; }
.card-fail { border-left-color: #dc2626; }
.card-neutral { border-left-color: #2563eb; }
.section {
background: white;
border-radius: 16px;
padding: 20px 20px 8px 20px;
margin-bottom: 22px;
box-shadow: 0 6px 18px rgba(15, 23, 42, 0.06);
}
.section h2 {
margin-top: 0;
font-size: 20px;
color: #0f172a;
}
.section h3 {
margin-top: 18px;
color: #1e293b;
}
table {
width: 100%;
border-collapse: collapse;
margin: 10px 0 18px 0;
font-size: 13px;
}
th, td {
border-bottom: 1px solid #e5e7eb;
padding: 8px 10px;
text-align: left;
vertical-align: top;
}
th {
background: #eff6ff;
color: #1e3a8a;
position: sticky;
top: 0;
}
tr:hover td {
background: #f8fafc;
}
.status-pass {
color: #166534;
font-weight: 600;
}
.status-warn {
color: #92400e;
font-weight: 600;
}
.status-fail {
color: #991b1b;
font-weight: 700;
}
.status-info {
color: #334155;
}
.kv {
display: grid;
grid-template-columns: 220px 1fr;
gap: 8px 14px;
font-size: 14px;
margin-top: 10px;
}
.kv .k {
font-weight: 600;
color: #334155;
}
.empty {
padding: 14px;
border: 1px dashed #cbd5e1;
border-radius: 10px;
color: #64748b;
background: #f8fafc;
}
.small {
font-size: 12px;
color: #64748b;
}
.footer {
text-align: center;
font-size: 12px;
color: #64748b;
padding: 10px 0 20px 0;
}
.badge {
display: inline-block;
padding: 4px 8px;
border-radius: 999px;
background: #e2e8f0;
color: #334155;
font-size: 12px;
margin-right: 8px;
}
</style>
"@
$sb = New-Object System.Text.StringBuilder
[void]$sb.AppendLine("<html><head><meta charset='utf-8'><title>SQL Server New Install Audit - $([System.Net.WebUtility]::HtmlEncode($SqlInstance))</title>$css</head><body>")
[void]$sb.AppendLine("<div class='header'>")
[void]$sb.AppendLine("<h1>SQL Server New Install Audit</h1>")
[void]$sb.AppendLine("<div class='sub'>Instance: <strong>$([System.Net.WebUtility]::HtmlEncode($SqlInstance))</strong> | Generated: <strong>$generatedOn</strong></div>")
[void]$sb.AppendLine("</div>")
[void]$sb.AppendLine("<div class='container'>")
# cards
[void]$sb.AppendLine("<div class='card-grid'>")
foreach ($card in $summaryCards) {
[void]$sb.AppendLine("<div class='card $($card.Css)'><div class='label'>$($card.Label)</div><div class='value'>$($card.Value)</div></div>")
}
[void]$sb.AppendLine("</div>")
# overview
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>Overview</h2>")
[void]$sb.AppendLine("<div class='kv'>")
[void]$sb.AppendLine("<div class='k'>SQL Instance</div><div>$([System.Net.WebUtility]::HtmlEncode($SqlInstance))</div>")
[void]$sb.AppendLine("<div class='k'>Edition</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.Edition))</div>")
[void]$sb.AppendLine("<div class='k'>Version</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.ProductVersion))</div>")
[void]$sb.AppendLine("<div class='k'>Patch Level</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.ProductLevel)) / $([System.Net.WebUtility]::HtmlEncode([string]$sp.ProductUpdateLevel))</div>")
[void]$sb.AppendLine("<div class='k'>Clustered</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.IsClustered))</div>")
[void]$sb.AppendLine("<div class='k'>Always On Enabled</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.IsHadrEnabled))</div>")
[void]$sb.AppendLine("<div class='k'>OS</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$os.Caption)) $([System.Net.WebUtility]::HtmlEncode([string]$os.Version))</div>")
[void]$sb.AppendLine("<div class='k'>Physical Memory</div><div>$([System.Net.WebUtility]::HtmlEncode((Convert-BytesToNiceString $comp.TotalPhysicalMemory)))</div>")
[void]$sb.AppendLine("<div class='k'>CPU</div><div>$([System.Net.WebUtility]::HtmlEncode((($proc | Select-Object -First 1).Name)))</div>")
[void]$sb.AppendLine("<div class='k'>SQL Start Time</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$si.sqlserver_start_time))</div>")
[void]$sb.AppendLine("</div>")
[void]$sb.AppendLine("</div>")
# findings
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>Top Findings</h2>")
if ($topWarnings.Count -gt 0) {
[void]$sb.AppendLine((Convert-TableToHtml -Data $topWarnings -Columns @('Category','Metric','Value','Status','Notes')))
} else {
[void]$sb.AppendLine("<div class='empty'>No warnings or failures were detected by the built-in light rules.</div>")
}
[void]$sb.AppendLine("</div>")
# compact summaries
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>Key Summaries</h2>")
[void]$sb.AppendLine((Convert-TableToHtml -Data ($dbSummary | Select-Object -First $TopDbRows) -Title 'Databases' -Columns @('Database','State','RecoveryModel','CompatibilityLevel','DataSizeMB','LogSizeMB','AutoClose','AutoShrink','Encrypted','Trustworthy','QueryStore','RCSI','PageVerify')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($fileSummary | Select-Object -First 50) -Title 'Files' -Columns @('Database','Type','LogicalName','SizeMB','MaxSizeMB','PercentGrowth','MountPoint','VolumeFreeGB','PhysicalName')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $backupSummary -Title 'Backups' -Columns @('Database','RecoveryModel','LastFull','LastDiff','LastLog')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($jobSummary | Select-Object -First 50) -Title 'SQL Agent Jobs' -Columns @('JobName','Enabled','Owner','Category','HasSchedule','LastStatus','LastRun')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $loginSummary -Title 'Logins' -Columns @('Name','Type','Disabled','DefaultDatabase','CreateDate')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $driveSummary -Title 'Windows Volumes' -Columns @('Drive','Label','FileSystem','Size','Free','FreePct')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $tempdbSummary -Title 'TempDB Files' -Columns @('Name','Type','SizeMB','Growth','PercentGrowth','PhysicalName')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $waitSummary -Title 'Top Wait Stats' -Columns @('WaitType','WaitingTasks','WaitTimeMs','MaxWaitTimeMs','SignalWaitTimeMs')))
if ($agSummary.Count -gt 0) {
[void]$sb.AppendLine((Convert-TableToHtml -Data $agSummary -Title 'Availability Groups' -Columns @('AGName','ClusterType','BackupPreference','ReplicaServer','AvailabilityMode','FailoverMode','SeedingMode','SecondaryConnections')))
}
[void]$sb.AppendLine("</div>")
# detailed sections by category
foreach ($group in $metricsByCategory) {
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>$([System.Net.WebUtility]::HtmlEncode($group.Name))</h2>")
[void]$sb.AppendLine("<div class='small'><span class='badge'>$($group.Count) metrics</span></div>")
[void]$sb.AppendLine((Convert-TableToHtml -Data ($group.Group | Sort-Object Metric) -Columns @('Metric','Value','Status','Notes')))
[void]$sb.AppendLine("</div>")
}
[void]$sb.AppendLine("<div class='footer'>Generated by Invoke-SqlServerNewInstallAudit.ps1</div>")
[void]$sb.AppendLine("</div></body></html>")
# Write output
$outDir = Split-Path -Path $OutputPath -Parent
if ($outDir -and -not (Test-Path $outDir)) {
New-Item -Path $outDir -ItemType Directory -Force | Out-Null
}
$sb.ToString() | Set-Content -Path $OutputPath -Encoding UTF8
Write-Log "Audit complete. HTML report written to: $OutputPath"
#endregion HTML generation
Top comments (0)