DEV Community

Uwe Janke
Uwe Janke

Posted on

Getting Started with sqmSQLTool: SQL Server Administration Made Easy

#powershell #sqlserver #dba #automation

Learn how to automate SQL Server administration tasks with sqmSQLTool, a powerful PowerShell module built on dbatools.

The Problem: Manual SQL Server Administration is Painful

As a SQL Server DBA, you know the drill:

  • Checking database health across multiple servers manually ❌
  • Running the same health checks and reports repeatedly ❌
  • Scripting everything from scratch for each environment ❌
  • No standardized way to monitor AlwaysOn Availability Groups ❌

What if there was a single tool that could automate 90% of your daily DBA tasks?

Enter sqmSQLTool — a comprehensive PowerShell module with 100+ pre-built functions for SQL Server administration.

What is sqmSQLTool?

sqmSQLTool is a professional-grade PowerShell module built on top of the excellent dbatools library. It extends dbatools with specialized functions for:

✅ Health Monitoring & Reporting

  • Database health checks
  • Blocking & deadlock analysis
  • Performance counter tracking
  • Index fragmentation reports

✅ AlwaysOn / High Availability

  • AG health status
  • Automatic failover management
  • Database replication automation
  • Node synchronization

✅ Security & Compliance

  • Sysadmin account auditing
  • Certificate management
  • Login audit trails
  • SPN configuration

✅ Backup & Restore

  • Backup integrity verification
  • Automated restore procedures
  • Backup scheduling

✅ And 90+ more functions...

Installation (2 Minutes)

Option 1: From GitHub (Recommended — Always Latest) 

# Clone the repository
git clone https://github.com/JankeUwe/sqmSQLTool.git
cd sqmSQLTool

# Run the installer
.\Install.ps1
Enter fullscreen mode Exit fullscreen mode

Why GitHub? You get the latest code immediately. PowerShell Gallery can lag behind releases.

Option 2: From PowerShell Gallery (Simplified) 

# Install for current user
Install-Module -Name sqmSQLTool -Scope CurrentUser

# Or for all users (requires admin)
Install-Module -Name sqmSQLTool -Scope AllUsers

# Import the module
Import-Module sqmSQLTool
Enter fullscreen mode Exit fullscreen mode

Note: PSGallery updates may lag. For the latest features and fixes, use GitHub.

Verify Installation 

# List all available commands
Get-Command -Module sqmSQLTool | Select-Object Name

# Should show 100+ functions
Enter fullscreen mode Exit fullscreen mode

Your First 5 Minutes: Quick Wins

1️⃣ Check Database Health 

Get-sqmDatabaseHealth -SqlInstance "YOUR_SERVER"
Enter fullscreen mode Exit fullscreen mode

Output: Instant report showing:

  • Database sizes
  • Free space
  • Autogrowth status
  • Recovery model
  • Last backup times
  • Health warnings (if any)

2️⃣ Check Disk Space 

Get-sqmDiskSpaceReport -SqlInstance "YOUR_SERVER"
Enter fullscreen mode Exit fullscreen mode

Output:

  • All disk drives on the server
  • Used vs. available space
  • Percentage full
  • Warnings if disk is >80% full

3️⃣ Monitor AlwaysOn AG Status 

Get-sqmAgHealthReport -SqlInstance "YOUR_AG_PRIMARY"
Enter fullscreen mode Exit fullscreen mode

Output:

  • All replicas in the AG
  • Synchronization state
  • Failover readiness
  • Log send/redo queue

4️⃣ Find Missing Indexes 

Get-sqmMissingIndexes -SqlInstance "YOUR_SERVER"
Enter fullscreen mode Exit fullscreen mode

Output:

  • Indexes that queries are requesting
  • Performance improvement estimates
  • SQL to create the index

Real-World Example: Daily Health Check Script

Here's a complete script you could run daily:

# Daily SQL Server Health Check
$servers = @("SQL01", "SQL02", "SQL03")

foreach ($server in $servers) {
    Write-Host "=== Health Check: $server ===" -ForegroundColor Cyan

    # Database health
    $dbHealth = Get-sqmDatabaseHealth -SqlInstance $server
    Write-Host "Databases: $($dbHealth.Count)" -ForegroundColor Green

    # Disk space
    $diskSpace = Get-sqmDiskSpaceReport -SqlInstance $server
    $fullDisks = $diskSpace | Where-Object { $_.PercentUsed -gt 80 }
    if ($fullDisks) {
        Write-Host "⚠️ ALERT: Disks >80% full!" -ForegroundColor Red
    } else {
        Write-Host "Disk Space: OK" -ForegroundColor Green
    }

    # AG Status (if applicable)
    $agHealth = Get-sqmAgHealthReport -SqlInstance $server -ErrorAction SilentlyContinue
    if ($agHealth) {
        Write-Host "AlwaysOn: Synchronized" -ForegroundColor Green
    }

    Write-Host ""
}
Enter fullscreen mode Exit fullscreen mode

Why Use sqmSQLTool Instead of Manual Scripts?

Database Health Check

  • Manual: 30+ lines of T-SQL + PowerShell
  • With sqmSQLTool: 1 line: Get-sqmDatabaseHealth
  • Saves: ~2 hours per check

Disk Space Report

  • Manual: Write WMI queries + format output
  • With sqmSQLTool: 1 line: Get-sqmDiskSpaceReport
  • Saves: ~1 hour per report

AG Failover

  • Manual: Complex T-SQL + error handling
  • With sqmSQLTool: 1 line: Invoke-sqmFailover
  • Saves: ~30 minutes per failover

Backup Integrity

  • Manual: SQL Server Maintenance Plan or custom script
  • With sqmSQLTool: 1 line: Test-sqmBackupIntegrity
  • Saves: ~1.5 hours per verification

Bottom line: ~100+ hours saved per year per DBA 📊

Getting Help & Documentation

Built-In Help 

# Get help for any function
Get-Help Get-sqmDatabaseHealth -Full

# Open online documentation
Get-Help Get-sqmDatabaseHealth -Online
Enter fullscreen mode Exit fullscreen mode

Community & Support

  • GitHub Issues: Report bugs or request features
  • GitHub Discussions: Ask questions, share tips
  • PowerShell Gallery: View package details and reviews
  • Website: www.powershelldba.de

What's Next?

Explore the Full Function Library

The module has 100+ functions. Here are some popular ones:

# See ALL available functions
Get-Command -Module sqmSQLTool | Select-Object Name, Synopsis | Format-Table -AutoSize
Enter fullscreen mode Exit fullscreen mode

Popular functions:

  • Get-sqmBlockingReport — Find blocking queries
  • Get-sqmDeadlockReport — Analyze deadlocks
  • Get-sqmWaitStatistics — Performance diagnostics
  • Get-sqmSysadminAccounts — Security audit
  • Invoke-sqmRestoreDatabase — Automated restore
  • Invoke-sqmPerfBaseline — Performance baseline

Automate Your Daily Tasks

Create a scheduled PowerShell task that runs your health checks every morning:

# Pseudo-code for scheduled task
$script = @"
Import-Module sqmSQLTool
Get-sqmDatabaseHealth -SqlInstance "PROD-SQL-01" | Export-Csv "C:\Reports\health_$(Get-Date -f 'yyyy-MM-dd').csv"
Get-sqmDiskSpaceReport -SqlInstance "PROD-SQL-01" | Export-Csv "C:\Reports\diskspace_$(Get-Date -f 'yyyy-MM-dd').csv"
"@

# Schedule with Windows Task Scheduler
Enter fullscreen mode Exit fullscreen mode

One More Thing: Star the Project! ⭐

If sqmSQLTool saves you time, please star the repository on GitHub:

👉 github.com/JankeUwe/sqmSQLTool

Stars help the project gain visibility and attract more contributors!

Summary

sqmSQLTool is your SQL Server administration Swiss Army knife:

✅ 100+ pre-built functions
✅ Built on proven dbatools library
✅ Free & open source (MIT License)
✅ Active development & community support
✅ Saves ~100+ hours per year

Get started now:

Install-Module sqmSQLTool
Get-sqmDatabaseHealth -SqlInstance "YOUR_SERVER"
Enter fullscreen mode Exit fullscreen mode

Questions? Visit the GitHub Discussions or check the PowerShell Gallery page.

About the Author

sqmSQLTool is developed by Uwe Janke, a Senior SQL Server DBA with 30+ years of experience in database administration and automation. Built for real-world enterprise environments.

Follow for updates:

Happy SQL Server administrating! 🚀

Top comments (0)