Press "Enter" to skip to content

Category: Powershell

Visualizing Checkins

Published 2016-11-02 by Kevin Feasel

Rob Sewell uses Power BI to map where he’s been:

I am using the swarm API but the principle is the same for any other API that provides you with data. For example, I used the same principles to create the embedded reports on the PASS PowerShell Virtual Chapter page showing the status of the cards suggesting improvements to the sqlserver module for the product team to work on. Hopefully, this post will give you some ideas to work on and show you that it is quite easy to get excellent data visualisation from APIs

First up we need to get the data. I took a look at the Swarm developers page ( The Trello ishere by the way) I had to register for an app, which gave me a client id and a secret. I then followed the steps here to get my user token I was only interested in my own check ins so I used the steps under Token flow Client applications to get my access token which I used in an URL like this.

This post includes some Powershell and quite a few animated GIFs, making it easy to follow.

Comments closed

Automated Emails

Published 2016-10-25 by Kevin Feasel

Allison Tharp shows how to send automated e-mails with Powershell:

The update has two parts: how I feel about my work and how I feel about my department.  For each of these, I wrote a few ‘beginning’ sentences and a few ‘ending’ sentences.  The script picks a random beginning and ending sentence for each category (work and department), color codes it, and sends the email to my personal and my work emails.

I love the randomization.

Comments closed

Automatic Variables In Powershell

Published 2016-10-24 by Kevin Feasel

Constantine Kokkinos has a list of automatic variables in Powershell:

Been a few days of learning since I last wrote one of these, but I have come back to the automatic variables page on the PowerShell documentation enough times that I think I should just blog the important parts for myself.

  • $? – TRUE/FALSE if the last thing you did succeeded.

  • $_ – Something everyone uses in posh, current pipeline object.

  • $Args – all the undeclared params passed to a function, try to avoid.

  • $Error – the array of error objects that represent a stack of the most recent errors. use $Errors0 to get the most recent error.

Read on for more variables.

Comments closed

Parallel PoshRSJob Template

Published 2016-10-19 by Kevin Feasel

Cody Konior walks through using PoshRSJob with a custom function:

Recently I migrated from my own runspace module to Boe Prox’s PoshRSJob which is pretty much perfect. But today I wanted to share how to integrate PoshRSJob cleanly into your functions through a default -Parallel parameter and using a template.

You can very easily modify this for your own purposes however it’s even more awesome as-is if you run parallelised tests for one major input (like a computer name) but where additional information might also be passed in through object properties on a pipeline (I’ll explain why you’d want to do that later in the post). Here’s what it looks like:

Read on for code and explanation.  Powershell parallelism is something that I’ve never been good at, so hopefully this makes it easier for me…

Comments closed

String Trimming

Published 2016-10-14 by Kevin Feasel

Richie Lee has a Powershell cmdlet to trim a string:

When building up urls from different parameters in something like TeamCity, or Octopus, it’s simple enough to get double “//” in urls if the parameters are not consistent. So little helper functions are always useful to have imported to manage such things. Below is an example of such a thing!

Click through for the function.

Comments closed

Always Encrypted Powershell Cmdlets

Published 2016-10-10 by Kevin Feasel

Sanjay Mishra alerts us to new Powershell cmdlets for enabling Always Encrypted on columns:

The July 2016 release of SSMS (and later versions) introduced a set of PowerShell cmdlets through a new ‘SqlServer’ module. This pagedescribes the various capabilities that these cmdlets bring to the table. Of most interest to the specific scenario described above is the Set-SqlColumnEncryption cmdlet. In the post below, we will walk through the steps required to use this – first from a PowerShell session to test the capability, and then finally from a C# application which is using PowerShell Automation to invoke the cmdlets from an application.

As a side note it is worth knowing that the cmdlets in the ‘SqlServer’ PowerShell module can also be used for automating key setup and management (and are, in many ways, more powerful than SSMS – they expose more granular tasks, and thus can be used to achieve role separation and to develop a custom key management workflow – but that is likely a topic for a separate post!)

Sanjay also includes a sample Powershell script to show how it works.

Comments closed

Renaming Servers

Published 2016-10-04 by Kevin Feasel

Steve Jones shows how to rename a server using Powershell:

I’m sure many people are experimenting with VMs and SQL Server. If you’re like me, many of you just default to installing Windows 7/10 or Windows Server xx Standard for your testing. Those systems work fine, but I’ve been trying to build slimmer systems, which means looking at Server Core. Installing Server Core is much the same as other versions, though you end up with only a command line. If you’re like me, using VMWare, you also might end up with a server name like “WIN-LKR3R4FfL5T”.

I want to change that. It’s a fine name if I’m working locally. It’s not to much fun connecting across a network. This post looks at how to rename that machine.

This is probably a good idea to do before installing any major software.  Renaming a server under SQL Server is possible, but there are a few extra steps to the process.

Comments closed

Finding All Sysadmins

Published 2016-09-30 by Kevin Feasel

Chris Bell has a Powershell script to find all sysadmins on a SQL Server instance:

The script below identifies the accounts on your SQL Server that have full sysadmin rights, either on their own or via an Active Directory Group.

To run this, you need a few things setup first.

  1. A file named Instances.txt that has each instance you are going to check on its own line. Just the name, nothing more. You can see the reference to the location at the beginning of the script, just change it to wherever you put your file.

  2. Rights to read the AD information for the domain. This way we can get the members of any groups granted access to your SQL environment.

Click through for the script.

Comments closed

Using Pester To Validate Script Installations

Published 2016-09-29 by Kevin Feasel

Rob Sewell wants to use Pester to guarantee that he has Ola’s maintenance scripts installed on a server:

First I thought about what I would look for in SSMS when I had installed the maintenance solution and made a list of the things that I would check which looked something like this. This would be the checklist you would create (or have already created) for yourself or a junior following this install. This is how easy you can turn that checklist into a Pester Test and remove the human element and open your install for automated testing

  • SQL Server Agent is running – Otherwise the jobs won’t run🙂

  • We should have 4 backup jobs with a name of

  • DatabaseBackup – SYSTEM_DATABASES – FULL

  • DatabaseBackup – USER_DATABASES – FULL

  • DatabaseBackup – USER_DATABASES – DIFF

  • DatabaseBackup – USER_DATABASES – LOG

  • We should have Integrity Check and Index Optimisation Jobs

  • We should have the clean up jobs

  • All jobs should be scheduled

  • All jobs should be enabled

  • The jobs should have succeeded

There’s a very nice script and walkthrough of the process if you click through.

Comments closed

SQL Server Configuration Section On Azure VM

Published 2016-09-29 by Kevin Feasel

Jack Li diagnoses an issue in which the SQL Server Configuration section of an Azure Virtual Machine only appeared under certain circumstances:

If you created an SQL Server VM via azure portal, there will be a section called “SQL Server Configuration” which was introduced via blog “Introducing a simplified configuration experience for SQL Server in Azure Virtual Machines”. Here is a screenshot of that setting.  It allows you to configure various things like auto backup, patching or storage etc.

I got a customer who created a SQL VM via powershell.  But that VM doesn’t have the section “SQL Server Configuration”.   Using his powershell script, I was able to reproduce the behavior.  When I created via portal UI, I got the “SQL Server Configuration”.

Read on for the solution.

Comments closed