By: Alejandro Cobar | Updated: 2023-05-02 | Comments | Related: > 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.
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).
Check your mailbox for the respective confirmation. In my case, it took a few minutes to show up.
Follow the steps to get started:
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."
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.
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.
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:
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:
This creates a Windows Task Scheduler entry that you can immediately check:
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.
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
- In the next article, I will explore the third and final DevOps popular tool for automation, Chef.
- If you'd like, you can check my previous articles on this PowerShell scripts automation series:
About the author
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
