DBA Tooling · 2026
The DBA toolbox
87 functions. One module. From backup to automatic AlwaysOn repair.
Uwe Janke · Senior SQL Server DBA · dtcSoftware
# AlwaysOn repair - fully automatic
PS> Repair-sqmAlwaysOnDatabases -SqlInstance 'SQL01' -Auto
[OK] Finance - removed, reseeded, joined AG
[OK] Controlling - removed, reseeded, joined AG
# AG-aware backup - preferred node only
PS> Invoke-sqmUserDatabaseBackup -SqlInstance 'SQL01' -All -CheckPreferredReplica
# Health check + security
PS> Get-sqmDatabaseHealth -SqlInstance 'SQL01' | Format-Table
PS> Invoke-sqmSaObfuscation -SqlInstance 'SQL01'
# Inventory - generate an HTML report
PS> Invoke-sqmInstanceInventory -SqlInstance 'SQL01' -OutputPath 'C:\Reports'
Report saved: C:\Reports\SQL01_Inventory_20260520.html
Motivation
Manual intervention: remove DB, clean up the secondary, backup, restore, add back to AG. 30-90 minutes. At 3am.
The AG preferred replica isn't checked. Primary backs up instead of secondary. An I/O spike in production.
Who has sysadmin? Which DBs have no backup? Which instance is on which patch level? - Manual queries, always incomplete.
Auditors ask concrete questions: when was the last CHECKDB? Is SA still active? Is there a login audit? - No central report.
Set up Extended Events by hand, parse the trace file. By the time the analysis is done, the problem is forgotten.
Every DBA has their own scripts. No standard, no traceability, no Pester tests.
Business Value
One module - no tool zoo
automatic instead of manual
for an overnight AG failure
| Situation | Before | With sqmSQLTool |
|---|---|---|
| AG DB out of sync | Manual repair, 30-90 min. | ✓ Automatic repair job (hourly) |
| Backup audit | An Excel list, out of date | ✓ Get-sqmDatabaseHealth - a report per click |
| Deadlock analysis | Extended Events by hand | ✓ Get-sqmDeadlockReport - instantly |
| SA account active | Often overlooked | ✓ Invoke-sqmSaObfuscation - 1 command |
| Patch level | Ad-hoc query | ✓ Invoke-sqmPatchAnalysis - every instance |
Overview
87 functions - 6 topic areas. Each area complete on its own.
Set-sqmConfig -Language 'en-US' or 'de-DE'.Repair, sync, failover, seeding, AG add/remove, repair job - the full AG lifecycle
Backup with AG-aware logic, the backup exclude table, integrity checks, restore, Ola jobs
Database health, wait stats, blocking, autogrowth, VLF count, long-running queries, missing indexes
SA obfuscation, login audit, sysadmin report, AD account status, SPN report, certificates
PerfBaseline, PerfCounters, index fragmentation, missing indexes, Query Store, wait statistics
Instance inventory, database documentation, server configuration compare, patch analysis
Eye-Catcher · AlwaysOn & HA
An AG database loses sync - the tool detects it, repairs it fully automatically, and writes to the Windows Event Log.
Checks the SynchronizationState of every AG database. Anything other than HEALTHY or SYNCHRONIZED is a candidate.
Invoke-sqmSqlAlwaysOnAutoseeding on every replica - a prerequisite for autoseed repair.
Remove-DbaAgDatabase - clean, via dbatools.
Delete the DB on every secondary - no orphaned state.
Sets it to FULL if it drifted - otherwise the add fails.
Add-DbaAgDatabase -SeedingMode Automatic - no backup/restore needed.
EventId 1000/1001/1002 in the Windows Application Log. Invoke-sqmLogging for the file log.
# Call it manually:
Repair-sqmAlwaysOnDatabases
# Set it up as an hourly Agent job:
New-sqmAlwaysOnRepairJob -Schedule "FREQ=HOURLY;INTERVAL=1"
# Test with no changes:
Repair-sqmAlwaysOnDatabases -WhatIf
# Output:
# SqlInstance DatabaseName AvailabilityGroup Status
# ----------- ------------ ----------------- ------
# SQLNODE01 SalesDB AG_PROD_01 RepairSuccess
# SQLNODE01 HRDB AG_PROD_01 RepairSuccessEye-Catcher · Backup & Integrity
Invoke-sqmUserDatabaseBackup knows AlwaysOn. It checks sys.fn_hadr_backup_is_preferred_replica() and runs only on the right node.
Reads master.dbo.sqm_BackupExclude - databases that are deliberately excluded (Sync-sqmBackupExcludeTable maintains the table).
A backup report via Database Mail - by default only on failure or abort. -MailOnSuccess for every run.
Reads BackupDirectory automatically from server properties. No hardcoded path in the job.
# Default: every user DB, AG-aware
Invoke-sqmUserDatabaseBackup -All -CheckPreferredReplica
# With the exclude table and a mail report
Invoke-sqmUserDatabaseBackup -All `
-CheckPreferredReplica `
-UseExcludeTable `
-MailTo "dba-team@company.com"
# Set up as a backup maintenance job
New-sqmBackupMaintenanceJob `
-SqlInstance "SQLNODE01" `
-CheckPreferredReplica `
-MailTo "dba-team@company.com"Deep Dive · Health & Diagnostics
Get-sqmDatabaseHealth checks 8 dimensions per database. Result: a TXT report + CSV. Usable immediately.
| Check | Threshold |
|---|---|
| Recovery model | Warning if not FULL |
| Last CHECKDB | Warning if older than 14 days (configurable) |
| Last backup (full) | Red if never / older than configured |
| Last backup (diff/log) | Warning if none exists |
| Autogrowth events | Last 30 days (configurable) |
| VLF count | Warning from 200 (configurable) |
| Database size | Informational |
| DB status | Red if not online |
# A single instance
Get-sqmDatabaseHealth
# Multiple instances
Get-sqmDatabaseHealth `
-SqlInstance "SQL01","SQL02" `
-MaxCheckDbAgeDays 7 `
-MaxVlfCount 100 `
-OutputPath "D:\Reports"
# Output (excerpt):
# Database Status LastFull VLFs CheckDB AutoGrowth
# -------- ------ -------- ---- ------- ----------
# SalesDB ok 2026-05-19 48 ok 3 Events
# HRDB WARN 2026-05-01 312 ok 0 Events
# ArchiveDB ERROR never 89 14d ago 0 EventsDeep Dive · Security & Compliance
Auditors ask specific questions. sqmSQLTool delivers specific answers - and fixes weaknesses automatically.
Invoke-sqmSaObfuscation - renames it and sets a random 32-character password. Requires: an AD group already set as sysadmin. Returns the new name + password as an object.
Invoke-sqmLoginAudit - every login, roles, last login time. Detects orphaned, disabled, and inactive accounts. Exportable to CSV.
Get-sqmSysadminAccounts - an instant overview of which accounts have sysadmin rights. For audit evidence.
| Audit question | Function | Effort |
|---|---|---|
| Is SA still active? | Invoke-sqmSaObfuscation | 1 command |
| Who has sysadmin? | Get-sqmSysadminAccounts | 1 command |
| Last CHECKDB? | Get-sqmDatabaseHealth | Automatic report |
| Inactive logins? | Invoke-sqmLoginAudit | CSV export |
| SPNs correct? | Get-sqmSpnReport | AD vs. expected comparison |
| Certificates expiring? | Get-sqmCertificateReport | Warning at <30 days |
Deep Dive · Performance
No more manual DMV digging. sqmSQLTool delivers prioritized recommendations.
Get-sqmWaitStatistics - top wait types, weighted. Instantly clear whether the problem is I/O, CPU, locking, or memory.
Get-sqmMissingIndexes - from sys.dm_db_missing_index_details, prioritized by impact score. With a CREATE INDEX statement.
Get-sqmLongRunningQueries - current + historical long-runners from DMVs, with a query text excerpt.
Invoke-sqmPerfBaseline - collects performance counters over a period, saves as a CSV baseline for comparison.
# Step 1: what's waiting right now?
Get-sqmWaitStatistics | Select-Object -First 5
# WaitType WaitMs Percentage
# -------- ------ ----------
# PAGEIOLATCH_SH 45823 38.2%
# LCK_M_S 12401 10.3%
# CXPACKET 9822 8.2%
# Step 2: which indexes are missing?
Get-sqmMissingIndexes -SqlInstance "SQL01" |
Sort-Object ImpactScore -Descending |
Select-Object -First 3
# Step 3: record a baseline (15 min.)
Invoke-sqmPerfBaseline -Duration 15 `
-OutputPath "D:\Baseline"Deep Dive · Inventory & Docs
Invoke-sqmInstanceInventory fully documents an instance: version, DBs, logins, jobs, AlwaysOn. TXT + CSV automatically.
Version, edition, patch level, collation, max memory, CPU count, sp_configure values.
Name, status, recovery model, size, last backup, owner, collation - every user DB.
Every login with type, status, server roles - ready for an audit immediately.
Every job with status, owner, schedule, last run, and result.
AGs, replicas, listener, sync status - only if configured.
# Document a single instance
Invoke-sqmInstanceInventory
# Multiple instances (fleet)
'SQL01','SQL02','SQL03' |
Invoke-sqmInstanceInventory `
-ContinueOnError `
-OutputPath "\\srv\DBA-Reports\Inventory"
# Patch level of every instance
Invoke-sqmPatchAnalysis `
-SqlInstance 'SQL01','SQL02','SQL03'
# Output:
# Instance Version CU LatestCU Status
# -------- ------- -- -------- ------
# SQL01 2022 CU14 CU16 WARN
# SQL02 2022 CU16 CU16 ok
# SQL03 2019 RTM CU27 ERRORQuality & Testability
sqmSQLTool isn't a script collection. It's a PowerShell module with unit tests, structured logging, and consistent conventions.
A clean split between the public API (87 functions) and internal helpers. Invoke-sqmLogging, Invoke-sqmHelpers are internal only.
Tests under /tests/Unit/ for every critical function. dbatools calls are mocked - no real SQL Server needed.
Invoke-sqmLogging: file + event log + verbose. Every function logs consistently. A central OutputPath via Set-sqmConfig.
Install.cmd / Install.ps1: auto-detects admin, AllUsers or CurrentUser. Robocopy without Zone.Identifier. Update.ps1 for upgrades.
| Property | Value |
|---|---|
| Public functions | 87 |
| Pester test files | ~15 |
| PowerShell version | 5.1 (PS 7 compatible) |
| Dependencies | dbatools ≥ 2.0 |
| Logging | File + Windows Event Log |
| Configuration | Set-sqmConfig / Get-sqmConfig |
| Install scope | Auto-detects admin → AllUsers |
Toolchain
sqmSQLTool is the operational backbone. Every other tool builds on it.
PostInstall calls: Invoke-sqmNtfsSetup, Invoke-sqmSaObfuscation, Invoke-sqmMonitoringKey, Install-sqmOlaMaintenanceSolution, New-sqmOlaMaintenanceJobs
Endpoint configuration, monitoring key, SA check after AG setup
The foundation for every SQL operation. sqmSQLTool is a company-specific layer built on top of dbatools.
Repair-sqmAlwaysOnDatabases writes EventId 1000/1001/1002 - visible in a SIEM/Splunk
Boundaries
| Task | sqmSQLTool? |
|---|---|
| AlwaysOn auto-repair | ✓ Yes - Repair-sqmAlwaysOnDatabases |
| AG-aware backup | ✓ Yes - Invoke-sqmUserDatabaseBackup |
| Instance inventory | ✓ Yes - Invoke-sqmInstanceInventory |
| SA hardening | ✓ Yes - Invoke-sqmSaObfuscation |
| Install SQL Server | - No → SQLSetupTool |
| Set up AlwaysOn | - No → AlwaysOnSetup |
| Migrate databases | - No → SQLMigration |
| Deploy SSRS reports | - No → SSRSDeploymentTool |
| Deadlock collection GUI | - No → DeadlockCollector |
Reference
The commands a DBA needs daily or weekly.
| Task | Command |
|---|---|
| Check AG health | Get-sqmAgHealthReport |
| Check blocking | Get-sqmBlockingReport |
| Long queries | Get-sqmLongRunningQueries |
| Disk space | Get-sqmDiskSpaceReport |
| Instance check | Get-sqmSQLInstanceCheck |
| AG repair | Repair-sqmAlwaysOnDatabases |
| User DB backup | Invoke-sqmUserDatabaseBackup -All |
| Task | Command |
|---|---|
| DB health report | Get-sqmDatabaseHealth |
| Patch level | Invoke-sqmPatchAnalysis |
| Missing indexes | Get-sqmMissingIndexes |
| VLF/autogrowth | Get-sqmAutoGrowthReport |
| Login audit | Invoke-sqmLoginAudit |
| Sysadmin check | Get-sqmSysadminAccounts |
| Inventory | Invoke-sqmInstanceInventory |
Summary
Repair-sqmAlwaysOnDatabases + New-sqmAlwaysOnRepairJob. An AG DB failure gets repaired automatically by an Agent job.
sys.fn_hadr_backup_is_preferred_replica checked. No more double backups on primary and secondary.
SA obfuscation, login audit, sysadmin report, CHECKDB status - every compliance question answered.
Pipeline-capable functions: inventory, patch level, health report for N instances at once.
87 functions, Pester tests, consistent logging - a module, not a script collection.
# Install from a share (recommended)
Install.cmd # auto-detects admin -> AllUsers
# Manually as admin (AllUsers)
.\Install.ps1 -Scope AllUsers
# Manually, CurrentUser
.\Install.ps1 -Scope CurrentUser
# Set the configuration
Set-sqmConfig -OutputPath "D:\DBA-Reports"| GitHub | github.com/JankeUwe/sqmSQLTool |
| Website | www.powershelldba.de |
| Import the module | Import-Module sqmSQLTool |