Press "Enter" to skip to content

Category: Powershell

Powershell Interactive Debugging in Visual Studio Code

Jess Pomfret shows how to use the interactive debugger in Visual Studio Code to troubleshoot an issue in Powershell code:

So I figured I’d take a look and see what was happening and how we could fix it. Now I’m going to be honest with you, my usual method of debugging involves adding Write-Host 'Hi‘, or piping objects to Out-GridView. I did start down this route, but the Get-DbaRegServer function calls an internal function, and things quickly got complicated.

Luckily, the PowerShell extension for VSCode includes a debugger so we can level up our game and use that to track down our issues.

Click through to see how it works.

Leave a Comment

A New Powershell Module for SQL Server Security

Stuart Moore introduces dbaSecurityScan:

How easy it to audit them? If someone asks you the DBA exactly who has access to object A, can you tell them? How do people get access to that object, is it via a role, a schema or an explicit permission?

Is that information in an easy to read or manipulate manner?

How do you ensure that permissions persist between upgrades? I’ve certainly seen 3rd party upgrades that have reset database level permissions. Do you have a mechanism to check every permission and put them back as they were?

We’re all doing the devops these days. Our database schema is source controlled, and we’re deploying it incrementally in pipelines and testing it. But are we doing that with our database security?

So in the classic open source way, I decided to scratch my own itch by writing something. That something is dbaSecurityScan, a PowerShell module that aims to offer a solution for all of the above.

Click through to see what dbaSecurityScan covers today, how to call it, and what you can do to get more info.

Leave a Comment

Deleting Old Build Definitions in Azure DevOps

Mark Broadbent solves a problem for us:

I have been experiencing a problem for quite a while now in my current environment, in that some of our old builds cannot be deleted. When you attempt to do so it results in the following error:

One or more builds associated with the requested pipeline(s) are retained by a release. The pipeline(s) and builds will not be deleted.

Many of our pipelines have undergone a lot of change over time to the degree it is not even obvious anymore why (or indeed where) these builds are being prevented from being dropped. The only thing that is clear is that until they can be, the old build definitions will remain.

Regardless of the reason why, Mark has the answer for how.

Leave a Comment

Powershell 7 and SMO

Max Trinidad gives us a primer on SMO with Powershell 7:

There are two ways you could use SMO in PowerShell 7 (cross-platform):

1. Installing the SMO NuGet packages, two packages are requiered:
a. Microsoft.SqlServer.SqlManagementObjects Version 150.18208.0 (as of 03/23/2020)
b. Microsoft.Data.SqlClient Version 1.1.1 (recommended)

2. Installing the PowerShell Module: SqlServer Version 21.1.18221 (as of 03/23/2020)

Keep in mind, once the packages and/or modules are installed, you need to update them manually.

Read the whole thing.

Leave a Comment

Creating Powershell Functions which Accept Pipeline Input

Patrick Gruenauer shows how to make your Powershell functions pipeline-capable:

You probably have already created your first function in PowerShell and now you want that your function is capable of pipeline input. Right? Ok, you’ve come to the right place. In this blog post I will carry out creating a sample function that accepts pipeline input by value.

Patrick also shows us one of the earlier hang-ups of pipeline development if you don’t have the correct process blocks.

Leave a Comment

Tips and Traps with PowerShell 7

Jeffrey Hicks takes us through some of the tricky parts of migrating to Powershell 7:

A long established community best practice in PowerShell scripting is not using command and parameter aliases. In a cross-platform world, this is even more critical. You may have been in the habit of using Sort in your code in place of Sort-Object. I know I have. I didn’t mind bending the no alias rule abit because there was nothing cryptic about Sort.

But in the Linux world, sort is a native command. There is no PowerShell alias. If your code uses sort, on Linux it will call the native command which will most likely break your code.

Read on for several more hints.

Leave a Comment

Preparing for Demos with Pester

Jess Pomfret has some advice for you if you ever give a presentation:

If you don’t know what Pester is, it’s a test framework for PowerShell.  In the simplest explanation, using their Domain-Specific Language (DSL) you describe how things should look. If all looks good it returns output in green and if it doesn’t you get red output.  There are a lot of great use cases for Pester, like using it to ensure your code does what it’s supposed to, using it to validate your SQL Server environment (dbachecks), or in this example using it to make sure your demos are setup and ready to go.

When I’m preparing for a presentation I go through the demos over and over again, so it’s easy to accidentally leave things in a state that will cause issues when I go to do my demos in the presentation. If you’re creating a table, for example, during the demo and you already created it practicing and then forgot to drop it, the demo gods will strike and it’ll fail when it matters most! A simple Pester test to check whether the table exists will solve this issue.

Even if you aren’t giving talks in public (or inside your company), Pester is a useful tool for ensuring that the thing you expect to be the case actually is the case.

Comments closed

Setting Up a SQL Server Lab with AutomatedLab

Jess Pomfret looks at a very interesting Powershell module:

There is a fantastic PowerShell module called AutomatedLab that can enable you to easily build out a lab for the  specific scenario you need to test. Even better is the module comes with 70 sample scripts that you can start with and adapt to meet your needs.

The module gives you the option to work with Hyper-V or VMWare. I will say most of the examples are using Hyper-V, and that is what I’ll be using also.

For my lab I want a SQL Server 2019 instance joined to a domain, and a separate client machine that I can manage the SQL Server from. On the client I would need to be able to connect to the internet as I want to be able to download PowerShell modules from the gallery easily.

It’s about time for me to rebuild my lab, so I’ll need to check that out.

Comments closed

Creating an Elastic Jobs Agent

Kate Smith continues a series on elastic jobs in Azure SQL Database:

Having laid the conceptual groundwork for Elastic Jobs in two previous postings (12), I am now going to create an elastic job and associated credentials using PowerShell.  For this scenario, I have one or more databases with a table ‘T’ and statistics ‘tStats’. I want to enforce an update for these statistics every day. To do this, I need to check that my stats have been updated in the past day, and if not, update them. The T-SQL to update statistics on a table “T” with stats named “tStats” is simple:

Click through for the Powershell script.

Comments closed