liveBook · Manning

SQL health check

As explained in Practical Automation with PowerShell: Effective scripting from the console to the cloud

A SQL health check is an automated scripted procedure that assesses the configuration or operational health of a SQL database environment and can notify when an undesired state is detected.

A SQL health check is an automated PowerShell procedure that connects to a SQL instance, verifies that databases use the SIMPLE recovery model, and emails a notification when a database has an undesired configuration. section 4.0

Build the automation around four operational concerns:

  • Retrieve SQL connection data from a secret vault rather than embedding credentials in the script. section 4.0 section 4.4.1
  • Run a dbatools diagnostic query against the selected SQL instance and inspect its Results output. section 4.4.1
  • Filter database properties for recovery models that are not SIMPLE. section 4.0
  • Send an email through SendGrid when the check finds a failure. section 4.0 section 4.4.1

Validate the dependencies before combining them

Retrieve SQL credentials and the server name with Get-Secret, then call Invoke-DbaDiagnosticQuery with the Database Properties query. Separately retrieve the SendGrid address and API key, send a test message with Send-EmailMessage, and proceed only after both tests work. section 4.4.1

How Practical Automation with PowerShell: Effective scripting from the console to the cloud covers it

How deep it goes
Implements secret retrieval, connection tests, diagnostic querying, result filtering, and notification, while deferring the full email implementation.
Examples
Uses Database Properties diagnostics, SIMPLE recovery-model filtering, SecretStore and KeePass vaults, and a SendGrid test email.
What it assumes
Requires a testable SQL instance, dbatools and Mailozaurr modules, SendGrid access, and KeePass or another configured secret vault.
What you can do after
Operate a parameterized PowerShell health check that validates SQL configuration and sends failure notifications without storing secrets in code.
How it connects
Connects PowerShell with SQL Server, dbatools, SecretManagement, KeePass, SendGrid, and Mailozaurr.
Cover of Practical Automation with PowerShell: Effective scripting from the console to the cloud
liveBookmanning.com
Read Practical Automation with PowerShell: Effective scripting from the console to the cloud on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Practical Automation with PowerShell: Effective scripting from the console to the cloud. The section links point to the parts of the book it draws on.