Press "Enter" to skip to content

Category: Powershell

Tracking Best Practices With dbatools And Pester

Published 2017-12-22 by Kevin Feasel

Jason Squires has an example of combining dbatools with Pester to build out SQL Server instance quality reports:

With what I have learned from Pester, it seems to be best to test each command on its own. That got me to thinking, what if I walk into a large environment and I want to see an enterprise summary of best practices? I started working on a few different versions with the ultimate goal of using dbatools function Get-DbaRegisteredServer. I have included a few examples of Test functions that include best practices, as well as some default database best practices all from dbatools.

In my first run, I wanted to make sure that I can pass multiple servers with multiple tests and came up with the below script. You will notice the top section that uses the Get-Dba functions, I had to supply my own values of the properties I am testing. In the bottom section, you will notice that dbatools Test-Dba functions have the IsBestPractice property already and that is all you have to test against.

Read on for a sample of what you can do.

Comments closed

Error Handling In Powershell In SQL Agent Jobs

Published 2017-12-20 by Kevin Feasel

Ben Miller has a couple of tips when executing Powershell within SQL Agent jobs:

What you don’t see is the way you have the job step succeed or fail. When using most commands in modules, you may find that errors still cause the step to fail because of the way they report the failure (some kind of throw or a Stop condition outside your control). So if you want things to fail that normally would show red on the screen but things would continue, remember that the default ErrorAction is Continue, so even though you get an error, PowerShell will just continue.

Read on for more.

Comments closed

DataRow To JSON With Powershell

Published 2017-12-19 by Kevin Feasel

Rob Sewell shows how to convert a .NET DataRow into its JSON form using Powershell:

I wanted to be able to Mock $variable. I wrapped the code above in a function, let’s call it Run-Query

Which meant that I could easily separate it for mocking in my test. I ran the code and investigated the $variable variable to ensure it had what I wanted for my test and then decided to convert it into JSON using ConvertTo-Json

Read on to see the fun mess that ConvertTo-Json made and then Rob’s simplification.

Comments closed

String Formatting With Powershell

Published 2017-12-14 by Kevin Feasel

Thomas Raynor provides a quick tip for string formatting in Powershell:

And, for some reason, instead of the default output which is formatted like a table, I want output presented like this.

This is a silly example, but notice that even though there are extensions of varying length (.ps1 and .dll are four characters including the dot, and .xlsx is five), all of the “file extension: <number>” is aligned.

How’d I do that?

Read on to learn how.

Comments closed

What’s In Your Powershell Profile?

Published 2017-12-05 by Kevin Feasel

Shane O’Neill wants to know what’s in your Powershell profile:

This brings me back to the main point. My profile does 3 things

  1. Changes the default colour of error and warning messages,
  2. Sets an alias for notepad to “n” since I use it so much Set-Alias -Name n -Value notepad , and
  3. loads up the dbatools prompt

I don’t import any modules because the two that I use the most are updated so frequently plus I’m currently using PowerShell 5 so they get automatically loaded when I type in one of their commands.

If you don’t already have a profile, read on and see how you can do it.  And over-do it if you’re not careful.

Comments closed

Policing Database Deployments

Published 2017-12-05 by Kevin Feasel

Drew Furgiuele has a rather interesting library that he’s released to the general public:

The base return is the TSQLFragment object, which in turn has a Batches object, which in turn holds… well it can hold a lot of different things. When the text is parsed, it will determine what type of object to return based on the statement it determines it is. For example, if it’s an insert statement it will be a certain type of object with a given set of properties and methods, and if it’s, say, a create index statement you’ll get different properties, such as which table or view is getting the index along with the indexed columns and included columns. It really is interesting.

But interesting can a double-edged sword: since the statement object that gets returned can be different for each parsed piece of code, that means to set up any type of intelligence around the stuff we’re dealing with, we need to check for very specific objects.

Unfortunately, I never got past the first animated GIF, whose subtitle was wrong.  You, however, should read the whole thing.

Comments closed

Using Pester For Configuration Checks

Published 2017-12-01 by Kevin Feasel

Andrew Pruski shows how to use Pester to audit SQL Server configuration settings:

One Pester test running!

What I like about this is that it can be easily dropped into a job scheduler (e.g.- Jenkins) and then you’ve got a way to routinely check (and correct) all the configuration settings of the SQL instances that you monitor.

Pester would not have been my first thought for configuration checking, but it does serve as another useful option.

Comments closed

Powershell Speed Testing

Published 2017-11-30 by Kevin Feasel

Shane O’Neill shows off a Powershell script which allows you to simplify performance testing:

Apart from catching up on news during my commute I only really use notifications for a certain number of hashtags i.e. #SqlServer, #tsql2sday, #sqlhelp, and #PowerShell.

So during work, every so often a little notification will pop up on the bottom right of my window and I can quickly glance down and decide whether to ignore it or check it out.

That’s what happened with the following tweet:

Click through for Shane’s demo.

Comments closed

Additional Restore-DbaDatabase Functionality

Published 2017-11-30 by Kevin Feasel

Stuart Moore shows off a few examples of advanced Restore-DbaDatabase usage:

No matter how hard the dbatools; team tries, there’s always someone who wants to do things we’d never thought. This is one of the great things with getting feedback direct from a great community. Unfortunately a lot of these ideas are either too niche to implement, or would be a lot of complex code for a single use case.

As part of the Restore-DbaDatabase stack rewrite, I wanted to do make things easier for users to be able to get their hands dirty within the Restore stack. Not necessarily needing to dive into the core code and the world of GitHub Pull Requests, but by manipulating the data flowing through the pipeline using standard PowerShell techniques, all the while being able to do the heavy lifting without code.

Click through for several examples.

Comments closed