Last week, you took a massive step.

You built your first Ansible playbook, ran it, and saw for yourself that a different way of managing SQL Server is not only possible but within your reach. You had your first win as an Automation Architect.

But with that first success comes a new, perfectly valid question that every skilled DBA asks: "Is this safe?"

You've built a tool that can check a server's version. But what happens if you build a playbook that makes changes and run it five times in a row? This is the fear of unintended consequences, the worry that our automation could cause more problems than it solves. It’s the voice of the careful "Mechanic" inside us wanting to prevent a mistake. Without an answer to this question, you'll never trust your automation enough to solve a real problem.

Your caution is valid. 

The difference is that an Architect doesn't avoid this problem; they solve it with a core principle, a golden rule that separates simple scripts from professional orchestration. That rule is Idempotency.

It sounds complex, but you already understand it intuitively.

Let's break it down.

Example - Formatting Disks to Support SQL Server Databases

Think of a typical SQL Server installation.  There are several steps that need to be performed prior to installation.  One of those steps is to format disks that will be home to numerous database files using the correct block size (64 KB, which can vary depending on storage vendor) vs. the default of 4 KB.  For example, see my diskPrep task in the automatesql.mssql.sql_install role.

• If the disk isn't formatted, and you run your automation, the end state of the disk should be formatted using a block size of 64 KB.  The state of the disk changes.
• If the disk is already formatted using a 64 KB block size, what happens?  Nothing (if you've used the community.windows.win_format module, this would be the case).  The state is already what you desired.  

That is idempotency.  An operation is idempotent if the result of performing it once is exactly the same as the result of performing it repeatedly.  The "format the disk" action is idempotent.  It checks the current state and only makes a change if the state is not what is desired.

Ansible and The Architect's Responsibility

A tool like Ansible is designed to be mostly idempotent. Its built-in modules (copy, service, file, etc.) are created with this principle in mind. If you tell a module to ensure a file exists, it will create it if it's missing, but do nothing if it's already there. This is the "magic" of a desired state tool.

However, there are exceptions. In our last post, we used the win_powershell module. This module is like a universal adapter—it's incredibly powerful, but it puts the responsibility for idempotency squarely on your shoulders.  If an Ansible module exists to perform the task at hand, it's always best to use it. 

Next, look into using ansible.windows.win_dsc.  This module works with numerous PowerShell Desired State Configuration resources to further expand Ansible's usage and maintain idempotency. 

As a last resort, roll your own, but always be sure that the scripts you build follow the golden rule.  

Ansible can't know if the PowerShell script you wrote is idempotent. If your script appends a line to a file every time it runs, it is not idempotent. It's the Architect's job to build their custom scripts with the golden rule in mind. For example, your script should check if the line already exists before trying to add it.

See the failback_ag.ps1 script as an example of combining idempotency and the SqlServer PowerShell module.  A failback to the original primary in an Availability Group occurs only when required. 

By understanding this distinction, you gain the confidence to build truly professional automation. You learn to leverage Ansible's built-in idempotent modules for common tasks and to write your own idempotent PowerShell for custom tasks. You've moved from writing a simple script to thinking like an Architect—defining the state you want and ensuring the custom code you write adheres to that principle.

The next time you write any script or playbook, ask yourself the question: 

"If I run this again, will the outcome be the same?"

This simple question is the foundation of every safe, reliable automation system.

Next week, we'll take this a step further. Now that we know our automation is safe, how do we make it smarter? We'll explore how to make our playbooks adapt to different environments automatically using a feature called Ansible Facts.

By internalizing the principle of idempotency—both in the tools you choose and the code you write—you avoid the biggest failure in automation: building a fragile, unpredictable system that no one trusts. You leave behind the world of one-off, dangerous scripts and step firmly into the world of professional, reliable orchestration.