Why Ansible When I Have dbatools and PowerShell?Why Ansible When I Have dbatools and PowerShell?

PowerShell and dbatools excel at imperative, stateful operations: full instance migrations, automated backup validation, and live incident triage. But when you use procedural scripts to enforce configuration baselines across dozens of servers, inventory, drift detection, and state tracking all land on you.

You do not need to abandon dbatools for Ansible. Pair Ansible and SqlServerDsc for declarative fleet state and multi-node orchestration, keep dbatools for complex engine operations, and you get continuous, auditable compliance without giving up your PowerShell toolkit.

Why DBAs Hate Typical DevOps Advice

If you've spent five, ten, or twenty years mastering SQL Server, you earned it the hard way.

You clicked through nested SSMS property dialogs and the Failover Cluster Manager wizard. You sweated through 2 AM maintenance windows. Then you moved to PowerShell, found Chrissy LeMaire and the dbatools community, and your job changed. Migrating fifty databases, configuring Database Mail, or standardizing MAXDOP across twenty instances went from days to minutes.

Now you have a folder, or a company Git repo, full of battle-tested .ps1 files. They run your DR failovers, verify your backups, and disable those SQL Server telemetry services.

They work.

Then the DevOps world tells you they're the problem. A 2025 piece in The New Stack frames the industry's move from admin-written scripts to declarative tools as progress, and it writes off the script era as "environment-specific and brittle." Translated for a DBA, the advice sounds like this: stop writing PowerShell, learn YAML, and manage your Windows clusters from a Linux control node.

Your reaction is predictable and mine was the same:

"Why would I throw away ten years of rock-solid PowerShell and the best database module on the planet (dbatools) to write YAML for a tool built by Linux admins? My scripts already run."

DBA to DBA: you were right to be skeptical.

Most generic DevOps advice assumes stateless applications. If a container or web server dies or gets misconfigured, you kill it and spin up a new one. A SQL Server instance hosting a 10 TB SQL Server database doesn't work that way. You can't treat a stateful database engine like a disposable web frontend.

The "Linux tool" objection is weaker than it sounds, too. Ansible's Windows modules are written in PowerShell. Every win_ task in a playbook is PowerShell running on your server.

So your PowerShell and dbatools experience carries straight into everything that follows, and you'll keep using all of it. What changes is scale.

That's the question worth asking: what happens when your fleet grows to the point where even your best PowerShell scripts hit a structural ceiling?

The Operational Ceiling

Picture 150 SQL Server instances across Development, QA, Staging, and Production. You have a solid PowerShell script that enforces your standard configuration: Max Server Memory, Cost Threshold for Parallelism, the Dedicated Admin Connection, and your standard DBA maintenance jobs.

It's good engineering. But look at what it takes to run it.

1. The server list. Where do the server names come from? A hardcoded array, an AD query, a CSV on your desktop? They may even live in a Central Management Server and you pull them with Get-DbaRegServer. That solves the list but not the context: which servers get which settings, which need a longer WinRM timeout, which run in QA mode. That context ends up back in your script as if/else branches.
2. The procedural plumbing. To make the script safe, you query the current value, decide whether a change is needed, make the change, catch WMI and WinRM exceptions, log the result, and retry on network hiccups. The line that changes the setting is one line. The plumbing around it is 150.
3. The tribal knowledge. Only you know that -SkipSecondaryChecks goes in QA but not Prod, or that the warehouse instance is running on a non-default port. That knowledge lives in your head, so nobody else on the team will touch the script.

What you've built is an interactive runbook that needs a human to operate it.

So you become the bottleneck. You run the maintenance. You're chained to your laptop during change windows. And when you take a vacation, routine maintenance waits until you get back (I know this scenario all too well).

Two Layers, Two Tools

Database automation splits into two layers, and each tool fits one of them.

• dbatools is imperative. It's built around actions: Copy-, Test-, Start-, Restore-, Export-. It understands SQL Server's internals through SMO and the DMVs, and it handles stateful, data-heavy work better than anything else available.
• Ansible with SqlServerDsc is declarative. You describe what a server should look like (Ensure = Present, OptionValue = 1), and the engine decides whether anything needs to change. Ansible also handles everything around the work: which hosts, in what order, with which variables.

Here's what that looks like:

Operational Scenario	Best Tool	Why
Full Instance Migrations	dbatools (Start-DbaMigration)	Moves databases, logins with original SIDs and password hashes, Agent jobs, linked servers, Database Mail, and credentials in one command. No declarative tool replicates this.
Backup Validation	dbatools (Test-DbaLastBackup)	Restores the latest Full/Diff/Log chain to a test instance, runs DBCC CHECKDB, reports the result, and drops the database.
Pre-Flight AG & Kerberos Checks	dbatools (Test-DbaAvailabilityGroup, Test-DbaSpn)	Validates AG health and prerequisites and Active Directory SPN registration before failovers or migrations.
Incident Triage	dbatools (Invoke-DbaWhoIsActive, Get-DbaWaitStatistic)	You need answers in a live terminal session, not a playbook run.
SQL Server Installation	Ansible + SqlServerDsc	dbatools can install SQL Server too (Install-DbaInstance). Ansible earns its place when setup is one step in a larger build: OS prerequisites (64KB allocation units, Instant File Initialization, Lock Pages in Memory, firewall rules) and unattended setup in one reproducible run.
WSFC & AG Builds	Ansible	Sequences the multi-host build: clustering features, the cluster and its witness, Always On, endpoints, and the listener, with no GUI wizards.
Fleet-Wide Baseline Enforcement	Ansible + SqlServerDsc	Enforces memory, cost threshold, MAXDOP, and DAC across the fleet in parallel, with no custom error-handling loops.
Rolling AG Patching	Ansible (sql_ag_patch)	Runs the full secondary-first, failover, patch-the-former-primary sequence in a guaranteed order.
Compliance Auditing	Ansible (--check)	A scheduled read-only run flags drift without making a change.

The rule of thumb:

• Use dbatools for stateful operations, migrations, and triage.
• Use Ansible and SqlServerDsc for fleet baselines, multi-node workflows, and compliance.

Three Problems Scripts Don't Solve

Configuration Drift

A script tells you the state of your servers at the moment it finishes. Three weeks later:

• An engineer chasing high CPU lowers MAXDOP from 8 to 4 and forgets to put it back.
• A domain GPO refresh strips Lock Pages in Memory or resets the Windows power plan back to Balanced.
• A vendor installer creates an unapproved sysadmin login.

The dbatools team built dbachecks for this, and it works. Pester tests that report drift on a schedule. The catch is that your checks and your fixes live in two different places, and they can drift apart too.

With Ansible and SqlServerDsc, one task definition does both jobs. Run the baseline playbook in check mode every morning and it reports which servers no longer match (in check mode, changed means "would change"):

TASK [Ensure MAXDOP matches the corporate standard] ***************************
changed: [sql-prod-02.corp.local]
ok: [sql-prod-01.corp.local]
ok: [sql-prod-03.corp.local]

Run it without --check, and the same task puts sql-prod-02 back in line. In production, schedule the check-mode run via a cron job or a job template in Ansible Automation Platform with an email notification, so the drift report is waiting for you each morning.

Change Advisory Board Friction

If you work in a regulated industry (banking, healthcare, government, insurance), you know the Change Advisory Board.

Submit a 500-line PowerShell script for a weekend AG patch, full of remote session loops, pending-reboot checks, failover retry logic, and nested try/catch blocks, and the questions start. What happens if it fails on line 210? Does it reboot without asking? Does it fail back automatically? Reviewers treat a long custom script like an unknown binary, because it could do anything.

With Ansible, your team vets a reusable role like automatesql.mssql.sql_ag_patch once. After that, every monthly change request is a short playbook:

---
- name: Patch SQL Server Availability Group
  hosts: sqlservers
  gather_facts: true

  vars:
    ag_name: "Prod-Orders-AG"
    desired_sql_version: "17.0.5005.3"   # SQL Server 2025 CU9 (KB5122048)
    sql_instance_name: "DEFAULT"
    sql_patch_source: "//FileServer/SQL_Updates/"
    sql_patch_filename: "SQLServer2025-KB5122048-x64.exe"
    sql_patch_checksum: "30F5A2F57DC82C8E5A497FDEBD15263840D160414E3D0049F174F9C4059D0994"
    reboot_secondary: true
    patch_primary: true
    sql_ag_failback: true

  tasks:
    - name: Import the sql_ag_patch role
      ansible.builtin.import_role:
        name: automatesql.mssql.sql_ag_patch

A change manager or auditor can read those variables in under a minute: the target AG, the exact patch file and its SHA256 checksum, the expected build after patching (17.0.5005.3), and explicit switches for rebooting secondaries, patching the primary, and failing back.

Multi-Node Coordination

Patching an Always On AG means a strict sequence across nodes: validate AG health and find the primary, set the secondaries to manual failover, patch and reboot them, fail over to a patched secondary, patch the former primary, and optionally fail back. Failover modes have to be restored even if an update fails partway through.

In PowerShell, that means Invoke-Command loops, session management, and CredSSP workarounds. Ansible was built to run tasks across hosts in a guaranteed order, which is how the sql_ag_patch role runs the whole sequence from the short playbook above. I walk through each step in How to Fully Automate SQL Server AG Patching.

What Declarative Looks Like

dbatools makes setting max memory easy. But Set-DbaMaxMemory is an action cmdlet: it sets the value and calls Alter() without comparing it to the current setting. If an instance is already at 57344 MB, it "changes" 57344 to 57344. Even -WhatIf reports that as a change, because -WhatIf describes the action without checking current state.

To leave compliant instances alone, you write the filter yourself:

Test-DbaMaxMemory -SqlInstance $servers |
    Where-Object { $_.MaxValue -ne $_.RecommendedValue } |
    Set-DbaMaxMemory

That's three clean lines. Now repeat the pattern for 25 settings, registry keys, firewall rules, and service accounts, each with its own test.

With SqlServerDsc, the test is built in. Every resource runs a Test phase before it changes anything:

---
- name: Standardize Enterprise SQL Server Baseline
  hosts: sqlservers
  gather_facts: true

  tasks:
    - name: Ensure SqlServerDsc PowerShell module is installed
      community.windows.win_psmodule:
        name: SqlServerDsc
        state: present

    - name: Enforce Maximum Server Memory
      ansible.windows.win_dsc:
        resource_name: SqlMemory
        Ensure: Present
        InstanceName: MSSQLSERVER
        # DynamicAlloc calculates the OS memory reservation from the host's hardware.
        # Set DynamicAlloc: false and pass MaxMemory to use a fixed value instead.
        DynamicAlloc: true

    - name: Ensure MAXDOP matches the corporate standard
      ansible.windows.win_dsc:
        resource_name: SqlMaxDop
        Ensure: Present
        InstanceName: MSSQLSERVER
        DynamicAlloc: false
        MaxDop: 4

    - name: Ensure the Dedicated Admin Connection is enabled
      ansible.windows.win_dsc:
        resource_name: SqlConfiguration
        InstanceName: MSSQLSERVER
        OptionName: "remote admin connections"
        OptionValue: 1

Look at what's missing: no Where-Object filters, no try/catch blocks, no hand-managed change flags.

• Test before set, every time. Each SqlServerDsc resource compares the current value to the desired one. If they match, SQL Server isn't touched and Ansible reports ok.
• Audit with the same file. Run it with --check and Ansible runs only the Test phase across the fleet, reporting which instances drifted without changing anything.
• Values come from inventory. Replace the hardcoded MaxDop: 4 with a group variable, and Dev, QA, and Prod each get their own standard from the same playbook.

Meanwhile, your dbatools scripts keep doing what they do best: backup restore drills (Test-DbaLastBackup), instance migrations (Start-DbaMigration), and pre-flight AG and Kerberos diagnostics (Test-DbaAvailabilityGroup, Test-DbaSpn).

Your Next Step

Adding Ansible moves your work up a level. From running the update script to designing the database automation standard that developers, platform engineers, and pipelines use on their own. You keep the database depth that generalist DevOps engineers lack, and you gain their shared language of version-controlled, declarative infrastructure.

You don't have to make that move all at once, and you don't have to give up the PowerShell toolkit you've already built.

If you're ready to set up your first control node, start with these walkthroughs on configuring Kerberos authentication with Ansible and setting up OpenSSH on Windows Server 2025 for Ansible.

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

Until then: keep dbatools for the deep engine work it does best, and pair it with Ansible and SqlServerDsc to bring declarative scale to your fleet.