Press "Enter" to skip to content

Category: Powershell

Index Usage across Replicas

Jess Pomfret does the math:

Last week, I was working on a project to analyse indexes on a database that was part of an availability group. The main goal was to find unused indexes that could be removed, but I was also interested in gaining an overall understanding of how the system was indexed.

Unused indexes not only take up disk space, but they also add overhead to write operations and require maintenance which can add additional load on your system.  We can also use this analysis to look for a high number of lookups which could indicate we need to adjust indexes slightly.

Click through to see how you can connect together index usage stats from the primary and secondary replicas of an availability group.

Comments closed

Creating a Clock with Powershell

Jeffery Hicks knows what time it is (it’s clobberin’ time):

I’ve published a new project to the PowerShell Gallery. This is something that I needed, and maybe you do as well. Even though I have the typical clock running in the Windows taskbar, I have an ultrawide monitor so it isn’t always easy to read. I had been running the xclock app from WSL which was nice. But realized what I really wanted was a WPF-based clock that would display a formatted date-time string on my desktop.

Now there’s a PSClock similar to xclock, and you can check it out.

Comments closed

Secure FTP in Powershell

Chad Baldwin needs to send a file:

Over the years I’ve written dozens of these, one thing that often hangs me up are the “copy to SFTP” and “copy from SFTP” steps. usually what happens is I build two scripts…an “export script”, which has a manual step of “open FileZilla and upload file”, and then an Import script with another manual step to download the file.

After some Google searching to see how to handle SFTP in PowerShell, I ran into this StackOverflow answer (the creator of WinSCP) which introduced me to some cool new alternatives for dealing with FTP, FTPS, SFTP, SCP, etc using PowerShell.

Click through to see one way to do it, as well as some additional recommendations from Viewers Like You.

Comments closed

Multi-Threading with dbatools

Andy Levy has some lessons learned:

Over the summer, I spent some (a lot of) time working on updates to a script at work which runs multiple processes in parallel. Everything seemed to work OK for a while, but then everything broke. It broke right around the time dbatools 1.1 dropped, so I started thinking that something must have changed there. Turns out, it was entirely my fault and I hope this post will help you avoid the same trap.

Don’t fall into the same traps Andy did; read the whole thing.

Comments closed

Compressing Multiple Files into an Archive with Powershell

Patrick Gruenauer zips all the files:

Compressing files is a common task. For saving time, you can use PowerShell to automate the compression process. In this blog post I will show you how to compress multiple folders at once with the PowerShell Cmdlet Compress-Archive. The compressed zip files will be stored separately in a specific file. Let’s dive in.

Click through for a script which compresses each folder in its own zip file.

Comments closed

Sorting a List with Powershell

Kenneth Fisher lines ’em up and knocks ’em down:

In my last post I grabbed a file list but I really need it sorted alphabetically. Now, I’ve been a DBA my entire career and one of the things that gets hammered into you is that unless you specifically sort a list, there is no guarantee that that list will be sorted. So how do I sort my list?

To learn how and see a few examples of it in action, check out Kenneth’s post.

Comments closed

Dynamic Parameter Code in Powershell

Jeffrey Hicks shows off some Powershell 7 functionality:

One of the topics we’ve discussed in the PowerShell Cmdlet Working Group is a request to make it easier to insert dynamic parameters. I am a bit torn on this. On one hand, I see the value in dynamic parameters. These are parameters that only exist if some condition is met, such as if the current location is in the Windows registry. The downside is that these parameters are difficult to discover and awkward to document. On top of that, the PowerShell code necessary to define a dynamic parameter is complicated and definitely not beginner-level. This is what I think the issue is really all about. So I decided to write my own tooling to make it easier to insert dynamic parameters.

Some of those examples go from “This looks reasonable” to “That’s a lot of code” pretty quickly. In fairness, though, this isn’t the type of thing you’ll write every day.

Comments closed

The Purpose of Powershell Providers

Robert Cain explains what providers do in Powershell:

Providers are an interesting concept in PowerShell. A provider allows us to use a standard method to communicate with different areas of your computer using the same set of commands. For example, the file system is accessed using a provider. When you use Get-ChildItem it goes through the file system provider to return a list of the files on your computer.

We’ll take a deeper look in a moment, but first let me mention that for all of the examples we’ll display the code, then under it the result of our code. In this article I’ll be using PowerShell Core, 7.1.5, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

Click through for a listing of several providers and more detail on two of them.

Comments closed

Building a Welcome Prompt for Powershell

Jeffrey Hicks makes Powershell more welcoming:

I realized it had been a while since I wrote a Friday Fun post. These posts are intended to demonstrate PowerShell in a fun and often non-practical way. The end result is generally irrelevant. The PowerShell scripting techniques and concepts I use are the real takeaways. The task is nothing more than a means to an end.

Today’s project is inspired by Linux. Specifically, the WSL Ubuntu installation I run in Windows Terminal. When I first launch it, I get a welcome screen like this.

I thought, why not do something similar for PowerShell?

Read on to see the result, which looks quite nice.

Comments closed

Moving Files Associated with Availability Groups

Eitan Blumin has a doozy of a short script:

Today, I’m sharing with you a cool Powershell script that basically implements the methodology necessary to move database files to a new location in AlwaysOn Availability Groups, without breaking HADR.

It’s based on a few very useful step-by-step guides on the topic such as this one and this one and this one. But it takes it a step further by being a single cohesive Powershell script that does everything end-to-end.

Well… Almost everything… The only thing it’s missing is somehow disabling any SQL Agent jobs that may be performing backups. I still haven’t figured out how to possibly automate such a thing, so you’d have to do that manually on your own.

Click through for instructions, notes, and warnings, as well as the script itself.

Comments closed