Using Bolt and PowerShell with MS SQL Server

Hello and welcome to this blog post all about using the new Bolt tasks in the Puppet module for Microsoft SQL Server. If you're not familiar with Bolt, it's our new multi-platform task automation application that lets you run one-off tasks against multiple machines at the same time, and makes it easy to share the tasks you're running with the rest of your team. The tasks that we have implemented in the SQL Server module really showcase that ability to share code with your team. Did I mention it's agentless? If you, or a DBA you know doesn't want extra agents installed on the database boxes, don't worry. I get it, and it's not a problem.

And if you hadn’t already heard, we recently made the SQL Server module more freely available so that it’s easy for anyone to start managing it with Bolt.

Before I dive into the tasks themselves, if you want to just follow along by reading to the end that's great. If you want to follow along by trying it out as you read then you will need to install Bolt on your machine first. You should also consider reading this excellent blog post by Glenn Sarti detailing the more general subject of using PowerShell with Bolt.

Setting up your environment

To see and use these tasks from the command line you will need to have Bolt installed on your machine and you will need to install the SQL Server module.

To install the module with Bolt ensure that your Bolt configuration directory has a file called Puppetfile in it. You should be able to find it in your $env:HOME directory at $env:HOME\.puppetlabs\bolt\Puppetfile. Ensure that the Puppetfile has the following line somewhere in it: mod 'puppetlabs-sqlserver'

Once that line is in place you should be able to run the following command: bolt puppetfile install, and you should get output something like the following:

You will know it worked if you can run the following command and get output that includes tasks that have sqlserver:: in their name:

The Tasks

With that said, we can get into what these new tasks are, and how you can use them on the command line or in the Puppet Enterprise GUI. When we're done with what these tasks can do, we'll talk about how they are implemented, and how you can even leverage some of the code we wrote to write custom tasks for your own environment.

sqlserver::get_sql_logins

We saw above that the task exists on our machine, now let’s get some additional information about it so we know how to use it.

The output above tells you all you need to know about how to use the task. Notice that all of the parameters for this task are marked Optional. This is because if you just run the task against a SQL Server with no parameters at all, its default behavior will be to look at all sql instances on a server and return information about all of the logins that it finds. Let's see an example of that. In the command below to execute the task you will see that I have already set up a SQL Server called gu213n926ppeudt.delivery.puppetlabs.net. The ‘winrm://’ portion in front of the machine name tells Bolt that I want to connect to it over WinRm, not SSH. The --no-ssl-verify is there because this is a stand alone-machine, not domain joined, so my machine doesn’t trust the machine certificate of the test box I’m connecting to. Using --no-ssl-verify tells Bolt to skip verifying the machine certificate it receives from the test box and encrypt the connection anyway.

There are a number of things to talk about in the output, so I'll stop and talk about them.

  1. The -p parameter at the end has to be the last parameter. It's how Bolt knows that it needs to ask you to type in a password.

  2. These tasks will always return data in JSON format, even though I didn't specify the --format json directive when I called Bolt. If you don't mind output like you see above, that's ok. If you want to capture the output into a variable and do some querying and filtering we will need to switch to the --format json style, which we'll see an example of shortly.

  3. This summary view of the logins contains only the information that we think most admins will want, most of the time. It's good for quickly determining if a login exists on a server, if it's disabled, if it's a domain login or a server login, all of those kinds of things. What you can't see here though is which sql instance the login belongs to. We think most admins will only run one sql instance at a time on a machine, so that information isn't in the summary view. If you want a more detailed view of the logins on a server you execute the task you can use the detailed parameter and get more information. Below is an example of the information you will get for a single login using the detailed parameter:

Notice that the instance name property shows you that the machine I'm running these tests against has more than one instance running on it. One of them is the default instance, but this login belongs to a named instance. These tasks are all designed to handle named instances on a machine properly.

The great thing though is that since we're running these tasks from PowerShell we don't have to look at this stuff directly. We can use the --format json Bolt parameter with ConvertFrom-JSON to make this stuff much easier to deal with*. Let's run the command like this:

*Sorry PowerShell 2.0 people. You get no ConvertFrom-JSON love.

The really cool thing that these examples above show us is that by returning JSON, we can use PowerShell to convert the JSON into objects!

All of the examples for this task so far have been running it with no parameters to get all of the logins on a server. Go back and look at the parameters it takes and you will see that you can also specify login names to search for, either using fuzzy matches, or exact names. You can also specify the instance name to connect to, so you don't get all of them. This way if you are using this data to take actions further on, or to inspect the results visually, you can cut down on the clutter.

sqlserver::set_sql_logins

Since we just got done using the get_sql_logins task as an example of some of the ways to use the tasks on the commandline, the rest of these will go a little faster.

Notice that in the output above, the sa login is still enabled. It's common to have corporate policies that it should be disabled, so let's take a look at how to do that. The first new thing we'll see in this task is using the --noop (No Op(eration)) parameter:

What we see is that in the objects we get back there is a set of changes that refers to a login that was matched to be modified. That object will have a set of noop changes, which are changes that would have gone through if this was a real run, and a set of properties_set which are things that were actually manipulated. If we run this again without the --noop parameter, and also change the login password we get this:

Now the change set is in the properties_set property, and we see that the account has been disabled, and that what we have changed is password.

sqlserver::get_sqlagent_jobs

For this task we can be even shorter here in this post, and I'll just give you an example of the kind of data that it returns about jobs and encourage you to play with this one on your own. The thing to note about this return data is that it returns most things you might reasonably want to know about an agent job including the names and properties of the steps in the job, but no data from the agent logs as that would be too much.

Keep in mind that many of those times like lastRunDate are effectively zero because this is a test machine and I haven't kicked those jobs off yet.

sqlserver::start_sql_agent_job

This task will kick off an agent job from whatever job step you specify, or the first step if you don't use the step parameter. A few things to keep in mind about this one:

  1. If you don't specify an instance name it will use the default instance. This is for convenience to avoid typing it out when that's what most people will want most of the time.

  2. The job step numbers are a zero-based array. So to start a job at step 5 you will need to pass the number 4 to the step parameter. This is because the programming objects we use on the back end are zero-based arrays, and it's best not to try to be too slick with the user input and cause weird things to happen.

  3. A lot of agent jobs run long, which is why the default is to kick off the job asynchronously and return data to the console. The data you get back should indicate the job is running, but servers can take a highly inconsistent amount of time to start or finish jobs. If the data you get back doesn't say something like currentRunStatus=running, it could be that the server didn't finish initializing the job before the script completed, or that it spun up and finished, all before the script was done. Inspect the lastRunDate property on each step looking for differences to indicate one step was started and another has not yet, or run sqlserver::get_sqlagent_job again to get the jobs’ latest status information.

Convert your PowerShell script into a task

If you have your own PowerShell script that you run against SQL Servers, it’s easy to convert that script into a Bolt Task. As a reminder the PowerShell with Bolt blog post has instructions for how to convert any PowerShell script into a task, but especially in the case of writing PowerShell for SQL Server, you can use some of the code we have written in your own scripts.

When implementing these tasks, I found that there was a significant amount of duplicated code between them. It makes sense that there would be since there are a number of things all four of them need to be able to do. They need to be able to find the instances of sql running on a node, they need to select the right instance to connect to, get a connection, etc.

In PowerShell it's common to take those kinds of common tasks, extract them into functions, and then to place the function definitions into a separate file that is then dot-sourced into your script.

To see how we've done this we can look at the task metadata for the file that contains all of the shared functions. The get_sql_logins.json file has a line for importing a file that exists in the SQL Server module.

*Read more about shared code files

Notice that the file path starts with the name of the module, but does not include the namespace; just the module name. If you take a look at the code in shared_task_functions.ps1, it contains a series of functions, but no code that uses them. This file's only purpose is to define functions for other files to use.

Each of the task files then imports that file's functions using a section of code like this one in get_sql_logins.ps1. Once it's imported the shared functions, it can use them to do things like find all of the sql instances running on a node ensuring you only get the instances you asked it to connect to via the $instance_name parameter.

One thing you might notice about some of the functions is that some of them have strict appended to their name. That's because the functions without strict in the name will match loosely, meaning it will return as many matches as possible, even partial name matches. The strict ones are designed to only return exact name matches to prevent accidental matches. Usually you will use the looser function type for tasks that only retrieve information, and the strict kind for tasks that could modify information and you want to force the user to be more specific about what they want to modify.

The end result is that if you want to write your own Bolt tasks that can connect to SQL Server and retrieve information, all you have to do is include that file as shown, expose an $instance_name parameter (or whatever you want to call it), and you don't have to re-write that code for yourself!

Closing

In this post we have talked about the new Bolt tasks in the Puppet SQL Server module. You should have a good idea now of what they are capable of and how to use them. I hope you are not only inspired to use them, but that you have an idea of some of the things that Bolt can help you with when managing your SQL Servers.

We also talked about some of the internals of how these tasks were implemented, and how using a shared code file could help you in the future if you want to write your own custom tasks that need to do some of the same things that these tasks are doing already.

Thanks for reading! Let us know how you use Bolt with #PuppetBolt.

Puppet sites use proprietary and third-party cookies. By using our sites, you agree to our cookie policy.