The "Hero DBA" Bottleneck: When Good PowerShell Scripts Trap Great EngineersThe "Hero DBA" Bottleneck: When Good PowerShell Scripts Trap Great Engineers

When you're the only person on your team who knows which switches to pass to your PowerShell scripts, the work ends up waiting for you. The new SQL Server VMs infrastructure handed over on Tuesday sit untouched until you're back from vacation, because nobody else feels comfortable running what you built.

In this post, I'll look at how separating the knowledge that lives in your head (which servers are different, and why) from the code that does the work lets your team run your automation safely. Ansible puts that knowledge in version-controlled inventory files (group_vars/ and host_vars/) and the code in idempotent playbooks, so a teammate can preview every change with --check --diff and run it without calling you.

How Good Engineering Turns You Into the Bottleneck

You probably built your toolkit the same way I built mine: starting with T-SQL and SSMS, moving to PowerShell, and adopting dbatools. Over the years you wrote functions for every recurring job, from configuring new builds and standardizing sp_configure settings to rotating error logs, checking backups, and fixing orphaned users after a restore. You know where every edge case lives because you hit each one in production.

When infrastructure hands you two new VMs on Tuesday morning, you run your script with the right flags and have both production-ready by lunch. Management notices, and teammates start saying "just ask Luke" (or whatever your name is).

That feels good at first, until the pattern sets in and every new build starts waiting on your calendar. Every "quick change" to production server settings gets routed to you because you're the only one who knows the script won't do something unexpected, and before long you're checking Teams on vacation and planning PTO around release schedules.

I've been in that exact spot, where the scripts worked great as long as I was the one running them.

Being the hero is a compliment with a cost attached. If your team can't run your automation without you, you've really just increased how fast you can perform a task manually. Sometimes that's good enough for now, but it shouldn't be the end goal (unless you're the only DBA).

The Operational Ceiling: Three Hidden Dependencies

If you hand the script that applies your standard instance settings to a capable mid-level DBA and ask them to run it against next week's new servers while you're out, most will hesitate—and for good reason. Wouldn't you? You don't want to be the person responsible for a messed up SQL Server instance.

1. It Only Works on Your Machine

Your script assumes things about the machine it runs on:

• A specific version of dbatools (and SqlServer) installed in your user scope.
• PowerShell 7 in one place and Windows PowerShell 5.1 somewhere else, with a function that only behaves in one of them.
• A dot-sourced helper like . "$PSScriptRoot\helpers\Write-DbaLog.ps1" that lives in your local repo clone, plus a credential cached in your profile.

Because none of that is written down, a teammate running the script from a jump box hits a module error on line 12 and stops right there rather than risk breaking something.

2. Switches Only You Remember

Here's a realistic parameter block from a settings script I'd have been proud of ten years ago:

param (
    [Parameter(Mandatory)]
    [string]$SqlInstance,

    [ValidateSet('Dev', 'QA', 'Prod')]
    [string]$Environment = 'Prod',

    [switch]$IsDataWarehouse,
    [switch]$SkipErrorLogConfig,
    [int]$MaxDopOverride,
    [string]$BackupShare = '\\nas01\sqlbackups'
)

Import-Module dbatools
. "$PSScriptRoot\helpers\Write-DbaLog.ps1"

$maxDop = if ($MaxDopOverride) { $MaxDopOverride } elseif ($IsDataWarehouse) { 8 } else { 4 }
$ctfp   = if ($IsDataWarehouse) { 50 } else { 25 }

Set-DbaMaxDop -SqlInstance $SqlInstance -MaxDop $maxDop
Set-DbaSpConfigure -SqlInstance $SqlInstance -Name CostThresholdForParallelism -Value $ctfp

It's clean code. Now look at the command a teammate has to type from memory for the warehouse server:

.\Invoke-SqlBaseline.ps1 -SqlInstance sql-dw-01 -Environment Prod -IsDataWarehouse -BackupShare '\\nas02\dw_backups'

Forget -IsDataWarehouse and the script sets the warehouse to MAXDOP 4. Reporting queries that normally do better parallelized across 8 cores vs 4 just get slower the next morning, and nobody connects it to the script run.

The script doesn't say which servers are warehouses, which ones back up to a different share, or which one needs a longer timeout because it sits across a slow WAN link. You carry that around in your head, so nobody else wants to run it.

3. No Safe Way to Preview Changes

Before running a change against production, a careful engineer wants one answer: what exactly is this about to change?

With a script, the honest answer is "read all 400 lines and trust that every cmdlet respects -WhatIf." Many action cmdlets don't compare current state before they act, so even -WhatIf reports a change when the server is already compliant. (I covered why in Why Ansible When I Have dbatools and PowerShell and how to fix it for custom code in Writing Idempotent PowerShell for SQL Server in Ansible.)

Rather than reading 400 lines of PowerShell line by line, the junior DBA simply waits and calls you, because the script is only safe in the hands of the person who wrote it.

From Personal Toolkit to Team Standard

The way out is to stop packing everything into one file. An Ansible project splits your script into layers, and each layer answers one question a teammate would otherwise ask you:

That last row is the one that changes your job. You stop being the person who runs the script and become the person who reviews changes to it.

### Personal Script Toolkit | One expert operator | warn
- Server roles and exceptions are passed as switches you remember
- Depends on modules, helpers, and credentials on your workstation
- Previewing a change means reading the script line by line
- Changes to the standard happen whenever you edit the file
---
### Ansible Playbook + Inventory | Any trained teammate | ok
- Server roles and exceptions live in `group_vars/` and `host_vars/`
- Runs from a shared control node with pinned collection versions
- `--check --diff` previews every change before anything is touched
- Changes to the standard go through a pull request you review

Side-by-Side: Moving Tribal Knowledge Into Inventory

Let's rebuild the warehouse example. The goal: a teammate runs the playbook against any server, including the warehouse, without passing a single configuration switch.

Step 1: Group servers by what makes them different

[sql_oltp]
sql-prod-01.corp.local
sql-prod-02.corp.local

[sql_dw]
sql-dw-01.corp.local

[production:children]
sql_oltp
sql_dw

The -IsDataWarehouse switch just became group membership. Nobody has to remember it, because the server's role is written down once.

Step 2: Put the standard in group_vars/ and the exceptions next to it

# Corporate baseline for every production SQL Server instance.
sql_instance_name: MSSQLSERVER
sql_maxdop: 4
sql_cost_threshold: 25
sql_max_error_logs: 30

# Data warehouse workloads run large parallel scans for nightly reporting.
# Wider parallelism and a higher threshold keep small queries serial.
sql_maxdop: 8
sql_cost_threshold: 50

Because sql_dw is a child of production, its values override the parent group. Every warehouse server gets MAXDOP 8 automatically.

Per-server quirks go in host_vars/, with the reason written beside them:

# Lives in the secondary data center across the WAN link.
# Default WinRM timeouts drop the connection during long DSC runs.
ansible_winrm_operation_timeout_sec: 120
ansible_winrm_read_timeout_sec: 150

That comment is the thing that used to exist only in your head. Now it's in Git, with your name on the commit and a date.

Step 3: Write the playbook once, with no server-specific logic

---
- name: Enforce SQL Server production baseline
  hosts: production
  gather_facts: false

  tasks:
    - name: Ensure MAXDOP matches the standard for this server's role
      ansible.windows.win_dsc:
        resource_name: SqlMaxDop
        Ensure: Present
        InstanceName: "{{ sql_instance_name }}"
        DynamicAlloc: false
        MaxDop: "{{ sql_maxdop }}"

    - name: Ensure cost threshold for parallelism matches the standard
      ansible.windows.win_dsc:
        resource_name: SqlConfiguration
        InstanceName: "{{ sql_instance_name }}"
        OptionName: "cost threshold for parallelism"
        OptionValue: "{{ sql_cost_threshold }}"

    - name: Ensure error log retention matches the standard
      ansible.windows.win_powershell:
        script: "{{ lookup('file', 'files/set_num_error_logs.ps1') }}"
        parameters:
          SqlInstanceName: "{{ sql_instance_name }}"
          MaxErrorLogs: "{{ sql_max_error_logs }}"

Look at what's missing: no if ($IsDataWarehouse), no -MaxDopOverride, no hardcoded server names. The playbook doesn't know or care which servers are warehouses. Inventory decides.

The error log task reuses the state-aware set_num_error_logs.ps1 script from Writing Idempotent PowerShell for SQL Server in Ansible, so it reports honestly in --check mode too.

Step 4: What your teammate runs while you're on PTO

It's Tuesday, and you're out. Infrastructure hands over sql-dw-02.corp.local, a new warehouse server the reporting team needs by Friday. SQL Server is installed with setup defaults and nothing else.

Your teammate makes exactly one decision: which group does this server belong to? They add one line to inventory and open a pull request:

[sql_dw]
sql-dw-01.corp.local
sql-dw-02.corp.local

Then they run the same playbook everyone runs. Click ▶ Replay to watch it:

$ ansible-playbook -i inventory/hosts.ini baseline.yml --limit sql-dw-02.corp.local --check --diff
TASK [Ensure MAXDOP matches the standard for this server's role] ***************
[ WARN ] changed: [sql-dw-02.corp.local]
TASK [Ensure cost threshold for parallelism matches the standard] **************
[ WARN ] changed: [sql-dw-02.corp.local]
TASK [Ensure error log retention matches the standard] *************************
-    "NumberOfLogFiles": 6
+    "NumberOfLogFiles": 30
[ WARN ] changed: [sql-dw-02.corp.local]
$ ansible-playbook -i inventory/hosts.ini baseline.yml --limit sql-dw-02.corp.local
PLAY RECAP *********************************************************************
[ RESULT ] sql-dw-02.corp.local : ok=3 changed=3 unreachable=0 failed=0
$ ansible-playbook -i inventory/hosts.ini baseline.yml --limit sql-dw-02.corp.local --check
PLAY RECAP *********************************************************************
[ RESULT ] sql-dw-02.corp.local : ok=3 changed=0 unreachable=0 failed=0

Three commands, all identical except for flags:

1. Preview. --check --diff shows all three settings would change on the fresh install, including the error log retention moving from 6 to 30. Nothing has been touched yet, and the teammate knows exactly what will change.
2. Apply. The same command without --check configures the server. Because it's in sql_dw, it gets MAXDOP 8 and a cost threshold of 50. Your teammate never typed either number. Inventory supplied them.
3. Verify. A final --check run reports changed=0. The server matches the standard before the reporting team ever connects to it, and the pull request shows who added it and when.

Compare that to the script version: forget -IsDataWarehouse, and the new warehouse goes live with the OLTP settings. Nobody finds out until the first month-end reporting run crawls.

What Changes for You

Moving what you know out of your head and into Git lets you spend your time on higher-value engineering work instead of running the same scripts over and over:

• You review instead of run. When a teammate needs a new exception (a server that needs MAXDOP 2, a new backup share), they open a pull request against host_vars/. You read a four-line diff and approve it. The change and the reason live in Git forever.
• Your knowledge survives you. Every "this one's different because..." conversation becomes a comment in a YAML file instead of a Teams message you'll have to repeat next quarter.
• You get your time back. The routine work that used to wait for your calendar runs without you. Your attention goes to the problems that actually need expert level SQL Server experience: performance, architecture, migrations, and the next platform standard.

Your dbatools scripts don't go anywhere. Migrations, backup restore drills, and live triage still belong in a terminal with an expert at the keyboard. The difference is that routine work like applying standard settings, performing installations, and fixing drift stops depending on that expert (you) being available.

Your Next Step

Pick one script this week and run this three-step check:

1. Choose the script teammates ask you to run most often rather than your most complex one.
2. List every parameter and every if branch that depends on which server it's running against. Each one is a variable that belongs in group_vars/ or host_vars/, not in the command line.
3. Hand the playbook to a teammate and watch them run --check --diff. Keep your hands off the keyboard. Every question they ask you is a missing comment or variable in inventory.

Once your standard settings live in inventory and run in check mode, the next question is obvious: what happens when a server drifts away from them three weeks later, and how do you prove compliance to the Change Advisory Board? That's the next post in this series: Configuration Drift & The Change Advisory Board: Auditing at Scale.

For the bigger picture, download the free guide below. It covers the 4-stage maturity model for Database Platform automation and a practical plan for taking your estate from manual builds to continuous desired state.

Prefer to watch the walkthrough? Watch it on YouTube.