Problem
In the modern IT world, we can now see areas like DevOps becoming more popular and growing in demand. I don’t know about you, but when I hear DevOps and what I’ve experienced professionally, I immediately associate it with automation. But guess what? Since what we’ve been doing so far with our "PowerShell for the DBA" series revolves around the automation of execution of PowerShell scripts, maybe we can give it a shot to see if the DevOps space can serve our purpose.
Solution
In this article, we will continue to explore options you can use to automate the execution of PowerShell scripts, but this time from the DevOps perspective using an automation tool called Jenkins.
Initial Considerations
- PowerShell proficiency.
- I will be using a very simple test scenario just as proof that the automated executions work. This will consist of a test database with a simple table with some records in it. The PowerShell script will connect to the instance and issue a TRUNCATE statement against the table (the table name might vary for each example to avoid giving the impression that I’m copying/pasting the screenshots for the different presented tools throughout the series).
- To be specific, I will be using Windows 11, so certain things might be slightly different (hopefully not that much) depending on the Windows version you use to replicate any examples within this article.
- This won’t be a deep dive into the tools that will be presented, but instead, I will try my best to cover the basics to achieve our goal.
What is Jenkins?
Straight from Jenkins website: Jenkins is a self-contained Java-based program, ready to run out-of-the-box, with packages for Windows, Linux, macOS, and other Unix-like operating systems. This obviously implies that it’s completely possible to deploy Jenkins using Docker and Kubernetes.
It basically is a tool that you can use for automation, and it heavily aims at the continuous integration cycle of software development projects (build, test, deploy).
It is 100% free. With that said, feel free to follow the installation steps/guide so that we can accomplish our purpose.
Installing Jenkins
Go to Jenkins download and deployment and download the version you prefer, whether the LTS (Long Term Support) or the Weekly Release. I usually like to go with the LTS since I know it is stable.
Install Jenkins using the guided Setup Wizard, as shown below:
Pay close attention to the next screen. I’m choosing "Running service as LocalSystem" because I’m doing this for testing purposes. If you plan to use this in a formal environment, make sure to create a dedicated service account with minimal required privileges.
If you don’t have JDK or JRE in your environment, you can use this link to get it: JDK 17.
I’m going to make a quick pause here just to clarify what’s JDK and JRE, to be fair with the reader that’s not familiar with the terms (if you are, feel free to skip to the very next screenshot to follow along).
- JRE – stands for Java Runtime Environment, and it basically is a platform that you can use to execute Java programs.
- JDK – stands for Java Development Kit, and it provides all the tools required to compile, debug and execute a Java Program.
As you can see, the setup screen above clearly states that only Java 11 and 17 are supported, but for some reason I wasn’t able to find a standalone installer for JRE. I was reading in the official documentation that starting with Java 11, JRE is no longer offered, so that’s why I went straight with JDK 17 (the one I provided in the link above). Here is more info about this from the Oracle website.
Let’s continue with the installation.
The version I installed when this article was written is 2.375.3 (obviously), but yours might be different.
Using Jenkins
Ok, so the installation is finished, so what’s next? We go to http://localhost:8080 (make sure to specify the port you selected during the installation), and this is what we’ll see for the very first time. Go to the path specified below and copy/paste the password.
For simplicity, I’m picking "Install suggested plugins."
After installing the plugins, it will ask to create an Admin User or "Skip and continue as admin." For this part, I’d say pick whatever you want.
This is the screen you should see after the setup completes. Although there are several options, we will first click "Create a Job."
Let’s specify a name for our job and click on "Freestyle project."
Let’s type a description for our job and then scroll down to the "Build Environment" section.
We want to automate our PowerShell script, but as you can see there’s no PowerShell option.
Let’s head to the PowerShell | Jenkins plugin.
For this guide, follow the instructions from the first option presented and the plugin should appear in your dashboard.
After installing the plugin, let’s head back to create our job and see if the option is now available.
To manually run the job, go to the dashboard and click on the green triangle at the very right of the job name.
I did this and stumbled upon something interesting to consider. When I check the "Console Output" of my build, it tells me it succeeded, but I can see an interesting message. It tells me that the login failed for user ‘NT AUTHORITY\SYSTEM’ because I told Jenkins to run the service as ‘LocalSystem,’ and my PowerShell script is made to login using Windows Authentication which will attempt to use the user of the runtime context. To solve this, I’m creating a SQL login and will tweak my script to use those credentials.
Here are the details.
Running the job again after those tweaks, I can see a different Console Output:
By the way, here’s the PowerShell script I used for this execution:
Automating Jenkins
That’s pretty much a success, but you might wonder: "Hey, that was just a mere manual execution of the code, and there was zero automation demonstrated!" You are 100% correct, and that’s because I overlooked an option.
In the "Build Triggers" section, there’s an option called "Build periodically," which allows you to enter a cron expression (pretty much the same as in Linux). So, let’s specify a cron expression to automate the execution of the PowerShell script. Small pause here: a cron expression is a string comprised of five or six fields separated by a white space that represents a set of times. This is heavily used in Linux and Unix systems to schedule the execution of tasks, which is commonly known as cron jobs.
Even though you have a good explanation right there in Jenkin’s interface, you can play around with this cool application to get a grasp on the concept.
After specifying the cron expression, saving the job, and waiting for the automatic execution, this is what I see in the Console Output:
This is great! But you might be wondering (again), "How can I trust this guy to know if the Console Output he is presenting actually refers to a successful execution from the scheduled variant vs. the manual one?" That’s a valid question, and the answer would be the following: If you look closely at both Console Output screenshots, you will notice that the first line for the manual execution says "Started by admin." The automated one says, "Started by timer".
General details about Jenkins
Before wrapping up the article, I’d like to leave you with some general details about Jenkins:
- By default, Jenkins is installed as a Local Service with an automatic startup type.
- What does Jenkins use as a persistence model to store all the settings, jobs and configurations? It doesn’t use a database, but rather a set of files and directories that can be found in C:\ProgramData\Jenkins\.jenkins
- As you noticed through the guide, we interacted with Jenkins through the web browser, so 2 things can come out of this:
- Yes, Jenkins by default (as part of the installation) deploys a webserver called Winstone (which is a Java servlet container). However, I read that you can tweak it so that it can use Apache, Tomcat or Nginx.
- Web-based interaction is not the only way that you can interact with Jenkins, you can use a Command Line Interface (CLI) or even a Rest API.
- In the "Manage Jenkins" section you can find tons of cool stuff to manage your setup.
- You can find notifications when a new version is available (or even pick if you want to upgrade automatically).
- You can manage your plugins.
- You can manage your users and credentials.
- You can even shutdown the service gracefully (meaning that it will first stop executing new builds so that the system can eventually shut down safely).
Summary
With this, I’m wrapping up the first DevOps automation tool to program the execution of PowerShell scripts to perform SQL Server tasks. After testing the tool for this specific purpose, here are my impressions (with no particular order in mind):
- Although we achieved what we wanted, the execution of PowerShell scripts wasn’t available right out of the box. So, it seems it wasn’t built with the capability in mind from the start. There’s nothing wrong with that because the plugin did the trick. I was just curious about that detail.
- In both successful and not successful script executions, the Console Output always showed me a successful attempt. It is hard to know if your script succeeded without reading between the lines in the Console Output. Maybe there’s a way to tune that, but I guess it might give you false positives out of the box.
- When you automate the execution of scripts/tasks, nothing in the dashboard immediately lets you know that. Maybe you can tweak the view to bring that information up, but nothing directly.
- I can see a good use case for the automation of PowerShell scripts using Jenkins if your organization/project heavily uses it to orchestrate stuff. If Jenkins is not being used, I don’t see the point in bringing it to the rest of the stack.
- It has a very rich plugins section that can help enrich the experience a lot, and since it’s an open source project, then everybody has the possibility of contributing to it.
Next Steps
- I had my first take on one of the most popular tools in the DevOps space. This was something new to me, and I’m glad I could share it here at MSSQLTips.com.
- The next DevOps tool to automate the execution of PowerShell scripts to perform SQL Server tasks will be Puppet. So, stay tuned if you found value in the content presented in this article.
- If you know anyone who might benefit from this content, please let them know about this article.
