Automate Runbooks with T-SQL/PowerShell, Capture Evidence, and Make Operations Idempotent

Learning outcomes

The final step is to turn operational intent into a rerunnable runbook. A reliable runbook is not “a PowerShell file that worked once.” It establishes preconditions, limits concurrent execution, makes the smallest needed change, records structured evidence, validates postconditions, exposes failure to the scheduler, and can be safely rerun after partial success. The scheduler—Agent, Task Scheduler, cron, CI/CD, automation platform—is deliberately outside the core logic.

1. Idempotency means repeated execution converges on the same desired state

Consider a runbook whose goal is “ensure WorkQueue has the approved status/created-time index.” A non-idempotent implementation blindly runs CREATE INDEX every time; the second run fails because the index already exists. A worse script catches that error and reports success, hiding unrelated failures with the same broad catch. An idempotent implementation first inspects state, changes only what is missing, and verifies the result.

USE master;GOIF DB_ID(N'ServiceHubOpsLab') IS NULLBEGIN    CREATE DATABASE ServiceHubOpsLab;END;GOALTER DATABASE ServiceHubOpsLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubOpsLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubOpsLab;GOIF SCHEMA_ID(N'lab21') IS NULL EXEC(N'CREATE SCHEMA lab21 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab21.RunAudit', N'U') IS NULLBEGIN  CREATE TABLE lab21.RunAudit  (    run_id bigint IDENTITY PRIMARY KEY,    run_name sysname NOT NULL,    started_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    finished_at datetime2(0) NULL,    outcome varchar(16) NOT NULL DEFAULT 'STARTED',    detail nvarchar(1000) NULL  );END;GOIF OBJECT_ID(N'lab21.WorkQueue', N'U') IS NULLBEGIN  CREATE TABLE lab21.WorkQueue  (    work_id bigint IDENTITY PRIMARY KEY,    status varchar(16) NOT NULL,    created_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    processed_at datetime2(0) NULL,    payload nvarchar(200) NULL  );  INSERT lab21.WorkQueue(status,payload)  SELECT TOP (5000)    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5 = 0 THEN 'READY' ELSE 'DONE' END,    CONCAT(N'work-',ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;END;GOUSE ServiceHubOpsLab;GOCREATE OR ALTER PROCEDURE lab21.usp_EnsureWorkQueueIndexASBEGIN  SET NOCOUNT ON;  SET XACT_ABORT ON;  DECLARE @lock_result int, @run_id bigint;  EXEC @lock_result = sys.sp_getapplock    @Resource=N'lab21:EnsureWorkQueueIndex',    @LockMode=N'Exclusive',    @LockOwner=N'Session',    @LockTimeout=0;  IF @lock_result < 0    THROW 51100, 'Another copy of this runbook is already running.', 1;  INSERT lab21.RunAudit(run_name,detail)    VALUES(N'EnsureWorkQueueIndex',N'precondition: lock acquired');  SET @run_id=SCOPE_IDENTITY();  BEGIN TRY    IF OBJECT_ID(N'lab21.WorkQueue',N'U') IS NULL      THROW 51101, 'Required table lab21.WorkQueue does not exist.', 1;    IF NOT EXISTS    (      SELECT 1 FROM sys.indexes      WHERE object_id=OBJECT_ID(N'lab21.WorkQueue')        AND name=N'IX_WorkQueue_StatusCreated'    )    BEGIN      CREATE INDEX IX_WorkQueue_StatusCreated        ON lab21.WorkQueue(status,created_at)        INCLUDE(processed_at);    END;    IF NOT EXISTS    (      SELECT 1 FROM sys.indexes      WHERE object_id=OBJECT_ID(N'lab21.WorkQueue')        AND name=N'IX_WorkQueue_StatusCreated'    )      THROW 51102, 'Postcondition failed: approved index is absent.', 1;    UPDATE lab21.RunAudit      SET finished_at=SYSUTCDATETIME(),outcome='SUCCEEDED',          detail=N'postcondition: index present'    WHERE run_id=@run_id;  END TRY  BEGIN CATCH    UPDATE lab21.RunAudit      SET finished_at=SYSUTCDATETIME(),outcome='FAILED',detail=ERROR_MESSAGE()    WHERE run_id=@run_id;    EXEC sys.sp_releaseapplock      @Resource=N'lab21:EnsureWorkQueueIndex',@LockOwner=N'Session';    THROW;  END CATCH;  EXEC sys.sp_releaseapplock    @Resource=N'lab21:EnsureWorkQueueIndex',@LockOwner=N'Session';END;GOEXEC lab21.usp_EnsureWorkQueueIndex;EXEC lab21.usp_EnsureWorkQueueIndex; -- safe second run: no-op + verifySELECT TOP (10) * FROM lab21.RunAudit ORDER BY run_id DESC;GO

sp_getapplock is not a distributed lock across unrelated SQL instances. It scopes concurrency inside the SQL Server lock manager/database context for this runbook. For cross-system orchestration, choose a coordination mechanism whose failure model matches the topology.

2. Preconditions and postconditions turn partial failure into visible state

The procedure checks that the table exists before changing it and then checks that the index exists afterward. If CREATE INDEX succeeds but the session disconnects before the audit update, a rerun sees the index and converges safely. That is exactly why idempotency matters: failure may occur between any two steps.

For more consequential changes, capture current values before changing them so rollback is concrete. Configuration runbooks should also distinguish dynamic settings from restart-required settings, database/session/query scope, HA primary/secondary role, and read-only states. A runbook that changes the wrong replica or silently waits forever on a lock is not safe automation.

SELECT  DB_NAME() AS database_name,  OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,  OBJECT_NAME(i.object_id) AS table_name,  i.name AS index_name,  i.type_desc,  i.is_disabled,  SYSUTCDATETIME() AS collected_utcFROM sys.indexes AS iWHERE i.object_id=OBJECT_ID(N'lab21.WorkQueue')  AND i.name=N'IX_WorkQueue_StatusCreated';GO

Store evidence in a table, log sink, ticket, artifact store, or monitoring system that survives beyond the scheduler’s short history window. Include a correlation/run ID so database evidence can be joined to PowerShell/CI logs.

3. PowerShell should orchestrate the runbook, not duplicate database logic

Microsoft’s current PowerShell module is SqlServer; legacy SQLPS remains for backward compatibility but is no longer the module to choose for new automation. Install the module from PowerShell Gallery and record the tested version in your deployment metadata. The wrapper should not embed plaintext credentials. Prefer integrated authentication, managed identity/Entra where supported, a secrets manager, or a credential mechanism approved for the target environment.

$ErrorActionPreference = 'Stop'Import-Module SqlServer$module = Get-Module SqlServerWrite-Host ("SqlServer module version: {0}" -f $module.Version)$server = $env:SERVICEHUB_SQLSERVERif ([string]::IsNullOrWhiteSpace($server)) {    throw 'Set SERVICEHUB_SQLSERVER to the target instance name.'}$query = @"SET NOCOUNT ON;EXEC ServiceHubOpsLab.lab21.usp_EnsureWorkQueueIndex;SELECT TOP (1) run_id,run_name,started_at,finished_at,outcome,detailFROM ServiceHubOpsLab.lab21.RunAuditWHERE run_name=N'EnsureWorkQueueIndex'ORDER BY run_id DESC;"@$result = Invoke-Sqlcmd -ServerInstance $server -Database master -Query $query -AbortOnError$result | Format-Table -AutoSizeif ($result.outcome -ne 'SUCCEEDED') {    throw "Runbook postcondition was not successful: $($result.detail)"}

This script logs the loaded module version so an evidence packet can reproduce the dependency. In a controlled automation repository, pin the exact tested module version during deployment rather than silently accepting an untested newer version. The community dbatools project can be very useful for estate automation, but it is not a Microsoft module and is not required by this course. If adopted, treat it as an external dependency: pin/test a version, review release notes and security, and document the owner.

4. Deliberately wrong: swallow errors and always exit zero

A common automation anti-pattern is try { ... } catch { Write-Warning $_ } followed by normal process exit. The scheduler marks the run successful even though the change failed. Another is to run sqlcmd without an option that causes SQL errors to produce a nonzero shell exit. The repair is to propagate failure: T-SQL uses THROW; PowerShell uses terminating errors and a nonzero exit; CI/Agent checks process status; the runbook separately verifies business state.

5. Runbook contract, cleanup, and bridge to observability

A production runbook should have a short contract header: owner; purpose; target scope; required engine build/edition/platform; required permissions; dependencies and pinned versions; concurrency behavior; preconditions; action; postconditions; evidence location; timeout/cancellation behavior; rollback; retry policy; secret source; and scheduler. It should also specify whether rerunning after partial failure is safe.

For destructive or irreversible actions, idempotency is not enough. A repeated DROP might be “idempotent” in the narrow sense while still being unsafe. Risk controls—backup, approval, maintenance window, role/topology checks and rollback—remain necessary.

USE master;GOIF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name=N'ServiceHub - Process Ready Batch')  EXEC msdb.dbo.sp_delete_job @job_name=N'ServiceHub - Process Ready Batch';IF EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name=N'ServiceHub Ch21 Daily 0200')  EXEC msdb.dbo.sp_delete_schedule @schedule_name=N'ServiceHub Ch21 Daily 0200';GOIF DB_ID(N'ServiceHubOpsLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubOpsLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubOpsLab;END;GO-- This cleanup removes only Chapter 21 lab objects/job/database.

Chapter 22 begins where this runbook ends. Structured operational evidence is useful only if you can correlate it with live requests, sessions, connections, transactions, locks, waits, plans, Extended Events, performance counters and incident time. The next chapter builds that observability stack.

Authoritative references

• Install SQL Server PowerShell module
• SQL Server PowerShell overview
• Invoke-Sqlcmd
• sp_getapplock
• SQL Server Agent overview
• SQL Server 2025 editions and supported features