Press "Enter" to skip to content

Category: Powershell

Working with Central Management Servers in dbatools

Published 2020-08-17 by Kevin Feasel

Mikey Bronowski continues a series on dbatools:

The built-in feature of the SSMS allows us to configure a group of SQL instances and run queries against multiple instances at once. With the registered servers you can also build a list of SQL Servers in one place, so everyone with access to the CMS can see them. First, we will start by creating registered servers and server groups.

This is an underrated set of functionality for SQL Server and dbatools does a good job working with it.

Comments closed

Splatting in Powershell

Published 2020-08-14 by Kevin Feasel

Mark Wilkinson describes splatting in Powershell and shows how you can use it to handle optional parameters:

I have to start of by saying I hate the name “splatting”. I didn’t come up with it, and I don’t like using it, but it’s the only word we have. Splatting is a way to pass parameter values to a function using a single array or hashtable. In this post we’ll be talking about hashtables because I think it is the more useful of the two.

Splatting is easy to explain in an example. 

And then that’s exactly what Mark gives us. Click through for the example as well as how you can set those optional parameters.

Comments closed

Stopping and Starting Virtual Machines in a Resource Group

Published 2020-08-11 by Kevin Feasel

Dennes Torres walks us through a script to stop or start all virtual machines in an Azure resource group:

Some tasks on azure are easier if we automate them. The Azure Portal provides us the cloud shell, which we can use for this kind of automation.

I was making some experiences with SQL Server Always On, so I created three VMs inside a resouce group. Every time I want to start some experiment I need to start all three VMs and, in the end, stop all three again.

Read on to see how Dennes is able to accomplish this.

Comments closed

Creating Nonsense Documents with Powershell

Published 2020-08-10 by Kevin Feasel

Jeffrey Hicks has a nonsense generator:

Today I thought I’d share my PowerShell solution to a recent Iron Scripter challenge. The challenge was to create PowerShell code that would create nonsense documents, with a goal of creating 10 sample files filled with gibberish. Yes, other than maybe wanting some test files to work with, on its face the challenge appears pointless.  However, as with all of these challenges, or even the ones in The PowerShell Practice Primer, the journey is the reward. The true value is learning how to use PowerShell, and maybe discovering a new technique or command. The hope is that during the course of working on the challenge, you’ll improve your PowerShell scripting skills. And who knows, maybe even have a little fun along the way.

It’s not quite up to the level of quality that you find in post-modern academic papers, but it’s getting there.

Comments closed

Reviewing the Windows Event Log with Powershell

Published 2020-08-05 by Kevin Feasel

Jess Pomfret takes us through an improvement to the old Get-EventLog cmdlet:

Recently I was tasked with troubleshooting an incident on a SQL Server at a certain point in the past, the issue being a high CPU alert.  It’s hard (without monitoring solutions set up) to go back in time and determine what the issue is.  However, one thing we can check is the windows event log to see if there was anything happening on the server at that time.

Now, you probably know that my favourite tool of choice is PowerShell, so let’s take a look at how we can use Get-WinEvent to see what was happening in the past.

Get-WinEvent is the newer revamped version of Get-EventLog, and there are two improvements I believe are worth mentioning. Firstly, with the introduction of filter parameters we can now find certain events much easier, which we’ll talk about a little later. Secondly, the performance of Get-WinEvent is much faster than using the legacy command.  I believe this is due to the filtering happening at the event engine instead of within PowerShell.

Those are some rather welcome improvements.

Comments closed

Finding Running SQL Agent Jobs

Published 2020-08-05 by Kevin Feasel

Garry Bargsley has a quick dbatools script to find running SQL Agent jobs:

Do you run SQL Agent Jobs in your environment?  Do you know what is running at any given time?  Most people do not know what is currently running if you ask them.

There are several times I need to know what job(s) is running on which server. For instance, let’s say on the morning of monthly maintenance you want to check to make sure all your important ETL jobs are complete or that there are currently no database backup jobs running.

Read on to see the cmdlet and how you can use it to query across a broad set of servers.

Comments closed

Creating Executables from Powershell Scripts

Published 2020-08-03 by Kevin Feasel

Patrick Gruenauer introduces us to an interesting tool:

Have you ever dreamed of creating exe files out of your PowerShell scripts? If yes, read on. In this post I will show you how you can create exe files with a small and simple program called ps2exe.

Read on to see how it works. Then give it a try and wallow in the fact that some Powershell-wielding production DBA who absolutely is not a developer and doesn’t write code like developers slowly realizes that the developer call was coming from inside the house.

Comments closed

Sending Messages from SQL Agent to Microsoft Teams

Published 2020-07-31 by Kevin Feasel

Rob Sewell is waiting for a message and it comes in two parts. First up, sending SQL Agent results to a Teams channel:

Using dbatools we can create a simple script to gather the results of Agent Jobs form a list of instances. Maybe it would be good to be able to get the job runs results every 12 hours so that at 6am in the morning the early-bird DBA can quickly identify if there are any failures that need immediate action and at 6pm , the team can check that everything was ok before they clock off.

But that’s not enough for Rob:

Following on from yesterdays post about creating an overview of SQL Agent Job Results and sending it to a Teams channel, I was given another challenge

Can you write a job step that I can add to SQL Agent jobs that can send the result of that job to a Teams Channel

The use case was for some migration projects that had steps that were scheduled via SQL Agent Jobs and instead of the DBA having to estimate when they would finish and keep checking so that they could let the next team know that it was time for their part to start, they wanted it to notify a Teams channel. This turned out especially useful as the job finished earlier than expected at 3am and the off-shore team could begin their work immediately.

Read the whole thing, as Rob has some detailed code examples.

Comments closed

Discovering Provider-Specific Commands in Powershell

Published 2020-07-24 by Kevin Feasel

Jeffrey Hicks performs some Powershell research:

I’ve been diving into PowerShell help lately while preparing my next Pluralsight course. One of the sad things I have discovered is the loss of provider-aware help. As you may know, some commands have parameters that only exist when using a specific PSDrive.  For example, the -File parameter for Get-ChildItem only works in the file system. If you change to the CERT: PSDrive that parameter isn’t available. But you do get a new parameter like -CodeSigningCert. It used to be that you could use help to discover these differences. Get-Help has a -Path parameter to assist you.

Sadly, at least for me as a PowerShell teacher, this no longer works, especially if you have updated help recently in Windows PowerShell. Microsoft is working on some updates to how Help is delivered but for now we might need alternatives.

Read on for alternatives.

Comments closed