Press "Enter" to skip to content

Category: Powershell

Parallelism Options in Powershell

Rod Edwards enumerates the list:

This is one that I should have learnt way earlier than I actually did if i’m being completely honest. One of my colleagues wrote a cool function for multithreading, and although this isn’t it, it did made me look into how it all worked.

Some of the techniques can be quite complicated, especially for a DBA who’s not as experienced with Powershell yet (and a ForEach loop just works right?) so I thought i’d post about the simplier methods that can be employed, and tweaks to get most of the benefits, without the complexity.

So, lets jump straight into it.

Rod does focus on Powershell jobs and thread jobs, so check out how those work. A while back, I had a comment here talking about the various options though Rod has definitely put a lot more effort into this task.

Comments closed

Handling Orphaned Database Files with dbatools

Rod Edwards rounds up the orphans:

This may be an edge case issue, it may not. Or some may not know this is a potentially a thing. For any of the above questions, i’m not sure of the answer. I do know however, that it doesn’t involve morally suspicious fairy tales of any kind, flutes, or pastry products for that matter.

I also know that it’s something that could potentially be robbing disk space across SQL Estates so i’ll talk about it anyway and supply a simple way to fix this in one sweep using the magnificent DBATools.

Rod’s claim is no pastry products, but my counter-argument is that the command probably runs better if someone brings donuts in.

Comments closed

Dynamic Parameters in Powershell

Laerte Junior explains how dynamic parameters work in Powershell:

Have you ever been in a situation that you want to call a cmdlet or a function with a parameter that depends on a conditional criteria that is available as a list? In this article I will show a technique where you can use PowerShell Dynamic Parameters to assist the user with parameter values.

In the documentation of Dynamic Parameters found at about_Functions_Advanced_Parameters in get-help it is defined as “parameters of a cmdlet, function, or script that are available only under certain conditions.” And can be created so that appears “only when another parameter is used in the function command or when another parameter has a certain value.” So, we can say that PowerShell Dynamic Parameters are used when the result of a parameter depends on the previous parameter.

Click through for examples.

Comments closed

Useful Operations in dbatools

Rod Edwards shows off some nice functionality in dbatools:

I often build solutions around the dbatools functions, the below is just some of my Operational favourites. With some I’ve included the output pipe that I use most frequently, but obviously, you can view and use the output however you choose to. Clearly, DBATools has many functions to add/remove/update SQL as well, but i’m just folking on the ‘gets’ here.

Naturally, as mentioned…its powershell, you can programmatically use this for any of your automation needs. Marvellous.

The ever growing list of commands can be found here: command index – dbatools . This can prove daunting to new users of the toolset, so here’s a starter for 10.

Click through for those 10.

Comments closed

Looping through SQL Server Instances in Powershell

Ajay Dwivedi has a script for us:

As a database administrator, often I have to fetch some metadata from all the SQLServers that we have. Other times, I have to execute some DDL or DML on all the servers.

In this blog and shared video, I show how to write a multiple server PowerShell script where server list source could range from raw text files to some inventory-based query result.

This is a serial operation, so you’re hitting one instance at a time. I’ve noticed that Powershell has about a half-dozen ways of performing parallel actions but they all seem to come with at least one fatal flaw.

3 Comments

Tokenizing SQL Queries

Phil Factor has a new script:

Imagine this: you have several directories full of SQL script files, and you need to know where a certain table is used. You’d rather like the context too, so you can check the whole SQL Expression and work out why it is running so slowly. Maybe, from that same daunting set of several directories, you need to search for a comment, either end of line, or block comment, perhaps structured. It could be that you just need to execute each query or statement in turn to check performance.

It’s not unusual to want to search just within strings. Although simple searches can be effective, you will at some point need a tool that is able to recognise and return a collection of strings representing the SQL code, divided up into the respective components of the SQL language.

For this article, we’ll use a PowerShell cmdlet, called Tokenize-SQLString, which is in my GitHub repository.

Read on for examples of how it works and the types of things you can do with it.

Comments closed

Password Handling in Powershell Automation Scripts

Ajay Dwivedi gives us a tip:

I have been writing automations using PowerShell for many years now. One common issue I notice with people’s code is the improper handling of passwords. In this blog, I share how to set up a Credential Manager on a SQLServer and use the same for handling passwords in automation.

To setup Credential Manager, we need to download and execute the following steps using scripts from my Github repo SQLMonitor.

One point I’d like to clarify in Ajay’s scripts is that the passwords in the database aren’t hashed. Hashing is a one-way operation, so you’d never be able to decrypt it with a passphrase. The password is encrypted and the passphrase isn’t a salt—salts are a way of making a hash unique from the plaintext to prevent multiple users with the same plaintext password from having the same salt. Encryption instead of hashing is the correct answer here because you need the plaintext of the password to perform the automated operation.

As for ENCRYPTBYPASSPHRASE(), it’s okay if you’re running SQL Server 2017 or later. For 2016 and earlier, it uses Triple DES with 128 bit key length and that’s no good.

I’d also look into the Powershell SecretStore module and possibly integrate into an existing key vault if you have one.

Comments closed

E-Mail Alerts when a Database is Offline

Volker Bachmann gets an e-mail:

As the second article in the dbatools Quickies series, I would like to introduce another mail script that sends an email when databases on the servers examined are offline.

Unfortunately, it happens every now and then that databases are briefly taken offline but then forgotten. Here we receive weekly emails with all databases that are offline and where we can then follow up. If no database is offline, we will still receive an email with a short note.

Click through for the article in English and German, as well as a dbatools script and some additional remarks from Volker.

Comments closed

Finding Disks Low on Space via dbatools

Volker Bachmann has a script for us:

I will publish short PowerShell scripts with dbatools in loose order that will help with simple tasks or checks on multiple systems.

I would like to start with a script that lists hard drives that have less than a certain percentage of free space left, here 10%.
These are summarized in an email and sent as a list.

Click through for the script, as well as quick German and English explanations.

Comments closed