Press "Enter" to skip to content

Category: Powershell

Generating SQL Server Data Tools Solutions from Templates

Published 2020-06-04 by Kevin Feasel

Sander Stad walks us through creating a template for building SSDT solutions:

Yes, templates. But how are we’re going to create a template for an SSDT solution in such a way that it can be reused?

That’s where the PowerShell module called “PSModuleDevelopment” comes in. PSModuleDevelopment is part of the PSFramework PowerShell module.

The PSModuleDevelopment module enables you to create templates for files but also entire directories. Using placeholders you can replace values in the template making is possible to have the name and other variables set.

This is where the SSDT template comes in. I have created a template for SSDT that containes two projects. One project is meant for the data model and the other project is meant for the unit tests.

Read the whole thing and check out Sander’s GitHub repo.

Comments closed

Setting Drive Allocation Unit Size using Powershell

Published 2020-06-04 by Kevin Feasel

Eric Cobb has a tiny script for us:

There seems to be some ongoing debate around whether or not formatting your data and log drives with 64KB allocation unit size even matters anymore. I would encourage you to do your own research to determine if you want to do this or not. My personal take on it is: if it doesn’t hurt, and it may help, and it only takes 2 seconds to click the “go” button on my PowerShell script, then I would rather go ahead and do it and not need it than not do it and wish I had later down the road.

I don’t have a strong opinion in that debate, myself, so I’ll just say that if you want to see how to do this in a couple lines of Powershell code, check out Eric’s post.

Comments closed

Finding the Power BI Local Tabular Instance

Published 2020-06-01 by Kevin Feasel

Emanuele Meazzo shows which port your local SSAS Tabular instance is running on as you navigate through Power BI:

I’m sure you know that PowerBI tabular models are 100% the same as SSAS tabular models, hence you can use SSMS to connect to a PowerBI Tabular model as if it was SSAS (because it is), to do whatever you want, for example scripting out the objects that you’ve created in the PowerBi Desktop GUI or extract the model to deploy it in SSAS.

The only issue to do so is that PowerBI Desktop each time you open a report creates the local SSAS Tabular instance on a random port, so it’s not like you can save a connection string and that’s it

But Emanuele has a quick Powershell script to find the port for you.

Comments closed

Replicating Permissions with dbatools

Published 2020-06-01 by Kevin Feasel

Claudio Silva shows how you can take one user’s permission set and apply it to another user with dbatools:

What are the steps involved in a process like this?

Generally speaking, the quickest way I know is:
1. Get the permissions of the source login/user (For each database because we don’t want to miss any permission)
2. Save them to a .sql file
3. Open the file and replace ‘srcUser’ by ‘newUser’
4. Execute

Claudio shows how you can accomplish this quickly.

Comments closed

Setting Default Parameters with Powershell

Published 2020-05-28 by Kevin Feasel

Jess Pomfret takes us through the main idea behind PSDefaultParameterValues:

I first heard about PSDefaultParameterValues from a PSPowerHour session by Chrissy LeMaire in 2018. After rewatching this recently, I realised she even mentioned this exact scenario. However, it took until I recently rediscovered this handy preference variable that it all clicked together.

PSDefaultParameterValues does exactly what the name suggests- it lets you specify default values for parameters. PSDefaultParameterValues can be set as a hash table of parameter names and values that will be used in your session for any function that can use it.  A simple example is the verbose parameter. If you wanted to turn on the -Verbose switch for every function you run you could add -Verbose to each function call, or you could set PSDefaultParameterValues.

Click through for a few examples of how you can use this.

Comments closed

Network Monitoring in Powershell

Published 2020-05-27 by Kevin Feasel

Jeffrey Hicks builds out a quick Powershell script to test network performance:

I hope you’ve been trying your hand at the scripting challenges being posted on the Iron Scripter web site. The challenges are designed for individuals to do on their own to build up their PowerShell scripting skills. A few weeks ago, a challenge was posted to create a network monitoring tool using PowerShell and the Write-Progress cmdlet. I thought I’d share my notes on the challenge and some of the code I came up with.

It’s an interesting challenge and solution.

Comments closed

The Pain of Nested Powershell Modules

Published 2020-05-26 by Kevin Feasel

Fred Weinmann explains why you probably don’t want to build a nested module in Powershell:

Yay, but … that is not enough for some people:

– What if somebody copy&pastes it to another machine and forgets the dependencies?
– What if another module uses the same dependency, but at a different version?
– What if I don’t want to confront the user with those dependencies?
– What if a later version of my dependency breaks things? I just tested this version!

And the answer to all four of those is the same: Ship dependencies within your own module, hidden from prying eyes. With the NestedModule feature of PowerShell modules we natively support that as well. Great! Feels good, feels stable, feels reliable, feels … solid.
It’s an illusion.

Click through to understand why this benefit is illusory.

Comments closed

dbatools Commands for Performance Tuning

Published 2020-05-22 by Kevin Feasel

John McCormack takes a look at dbatools with an eye on performance tuning:

DBATools is well known in the SQL Server community for the ease at which it allows you to automate certain tasks but did you know that DBATools can help with performance tuning your SQL Server. As my job recently changed to have more of a performance tilt, I wanted to investigate which commands would be useful in helping me with performance tuning. It turned out there are quite a few.

There are some good commands in here.

Comments closed

Helping Users in Powershell Scripts

Published 2020-05-19 by Kevin Feasel

Greg Moore walks us through Powershell’s ability to display help info:

Shortly after writing my last article on Parameters, I had to update a script, and I wanted to make it easier for others to run. One of the features I wanted to add was the ability to show them what the script would do with the provided parameters without actually running the script, in other words, provide “help”.

There is a thoughtful way to do this, and Greg walks us through it, while also showing us a few false starts along the way.

Comments closed

Taking the Pulse of Your Azure VM

Published 2020-05-14 by Kevin Feasel

Mark Broadbent has put together a quick Powershell script to figure out what’s going on with an Azure VM:

I was recently given the task of identifying the state of an Azure VM so that an automation script using the az vm run-command invoke would not fail if the VM was down or under a reboot.

I initially thought the task would be really easy and a simple query of the VM state using Get-AzVM would provide us with a running state property of the VM, but as it happens the state is a little abstracted.

Click through to see how Mark solved the challenge.

Comments closed