Scheduling PowerShell Scripts with Puppet

By:   |   Updated: 2023-05-02   |   Comments   |   Related: More > DevOps


Problem

In the previous article, we explored how to schedule the execution of PowerShell scripts using Jenkins, a very popular DevOps automation tool. To continue with the series, we will check out another prevalent DevOps tool called Puppet to achieve the same purpose.

Solution

In this article, we will go through the required steps to install and configure Puppet to automate the execution of PowerShell scripts to interact with SQL Server instances.

This will not be a deep dive into Puppet, but instead, I will try my best to cover the basic concepts.

Puppet

What is Puppet?

Taken straight from their website: "Puppet is a tool that helps you manage and automate the configuration of servers. Puppet is configured in an agent-server architecture, in which a primary server node manages the configuration information for a fleet of agent nodes."

Is it free?

There is a free trial that states, "free on up to 10 nodes – at any time." Therefore, it is not entirely free, but you can tinker with it in some capacity.

Installation Steps

Head to Free Trial — Puppet | Puppet by Perforce and click on the free trial button to proceed.

Puppet website for free trial

Fill out the form and check your email with the instructions.

Note: A sales representative will try to call whatever phone number you specify (trust me).

Successful registry confirmation

Check your mailbox for the respective confirmation. In my case, it took a few minutes to show up.

Email from Puppet to access download

Follow the steps to get started:

Puppet installation instructions

If you scroll down the page, you will see two sections: Primary Servers and Agents. Primary Servers can only be installed in Linux environments, i.e., we will not pick any of them. For this tip, we will focus on the Windows Agent, so download that one.

Quick pause here to clear the air a bit on what a Primary Server and Agent are:

Taken straight from Puppet's website: "Puppet agent is the application that manages the configurations on your nodes. It requires a Puppet primary server to fetch configuration catalogs from. Depending on your infrastructure and needs, you can manage systems with Puppet agent as a service, as a cron job, or on demand."

"The Puppet primary server is the server that stores the code that defines your desired state. The Puppet agent translates your code into commands and then executes it on the systems you specify, in what is called a Puppet run."

Download options for Windows

Proceed with the installation of the Windows Agent.

At the time of this writing, I installed version 7.21.0 of the Windows Agent, so don't be surprised if you see a few differences when you try this later on your own.

Puppet Agent Setup Wizard Welcome
License summary
Configuration
Ready to Install
Installing status
Install complete

Where to Go From Here?

So far, we have learned that we need a Primary Server and an Agent to get things going. We only have the Agent, so we will not achieve our goal, especially now that we can only install a Primary Server using Linux (e.g., Debian, Ubuntu, Redhat Enterprise).

Scheduled_task Module to the Rescue!

This module allows us to manage scheduled tasks for Windows Server 2012 and newer operating systems. Therefore, this means this is a Windows-only module (which fits our use case here).

Puppet can allow the installation of modules developed by the community. Even though we saw earlier that the website gives the impression that Puppet is targeted toward the Enterprise, it has open-source projects (i.e., Puppet Modules) that make the product highly customizable.

Installation

Open a Command Prompt, type "puppet module install puppetlabs-scheduled_task --version 3.1.1" and hit Enter.

Command prompt

We need to create a manifest file to generate the schedule for our PowerShell script. A manifest file is a list of resources with a unique title and named attributes describing the desired state.

With that said, here's the specific manifest file I created for this demonstration:

scheduled_task { 'Puppet PowerShell script execution': #Unique name for the scheduled task
command   => "$::system32\\WindowsPowerShell\\v1.0\\powershell.exe",
arguments => '-File "C:\\SQL Server\\PowerShell scripts\Puppet.ps1"',
enabled   => 'true', 
trigger   => [{
         schedule   => 'daily',     # Defines the trigger type.             [required]
         start_time => '02:31',     # Defines the time the task should run. [required]
         start_date => '2023-03-19' # Defaults to the current date.         [not required]
  }],
} 

This manifest file should be saved with a .pp extension anywhere you want.

To make sure that your written file is a valid one, before attempting to move forward, execute the "puppet parser validate" command like this:

puppet parser validate command

When there are no errors, the command will not output anything.

I wouldn't bother that much with this command because I tried to modify small things like "triggerf" instead of "trigger", and the command returned nothing in both cases. Only when you omit or misspell things like the ":" or the "=>" is when it returns an error.

To let Puppet know that you want to apply this manifest file, you execute the "puppet apply" command:

“puppet apply” command

This creates a Windows Task Scheduler entry that you can immediately check:

Windows Task Scheduler

To be honest, I'm kind of disappointed with this approach because I was expecting Puppet to store the metadata somewhere to manage the executions of tasks. Perhaps trying to work with Puppet for a Windows-only solution is not the way to go. Ultimately, not having a Linux Primary Server setup hurt the Windows-only attempt.

Finally, here's a screenshot of the content of the test PowerShell script used for this demo.

Test PowerShell script

Takeaways From This Exercise

  • Even though I didn't achieve the article's primary purpose in the way I would've liked, I showed you how I came to that conclusion for the sake of science.
  • Puppet gave me the impression of not being so friendly to start working with if you have little to no experience with the tool, meaning that the learning curve will be pretty high if you tackle it independently. Honestly, I had an easier time with Jenkins to achieve what I wanted, and with zero experience with the tool.
  • This is just my personal and honest opinion. Perhaps your experience with it is vastly different, which would be amazing.
  • If your organization is heavily invested in Puppet, and Senior DevOps Engineers are willing to lend you a hand, then it could be an excellent opportunity to consider exploring the tool for your DBA PowerShell automation scripts if you see any benefit in doing so.
  • Maybe in the future, I will give a shot to the Primary Server in Linux to see if that's the absolute way to go. As far as a Windows-only option for the automation of PowerShell scripts, I would say no.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-05-02

Comments For This Article