Press "Enter" to skip to content

Category: Powershell

Copy Logins between SQL Server Instances

Published 2021-12-17 by Kevin Feasel

David Alcock wants to move a login:

Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.

With domain accounts that’s pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it’s just a case of recreating the account on the new instance.

SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn’t know what the password should be. It’s worth saying at this point that the preference should always be to use domain accounts, they’re more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let’s proceed with the scenario of recreating a SQL authentication login with an unknown password, and we’ll need to get creative.

And as you’d expect, dbatools makes a dramatic appearance.

Comments closed

Importing Azure active Directory Users into Power BI

Published 2021-12-14 by Kevin Feasel

Reza Rad gets an assist:

There are two main methods to fetch the Azure Active Directory information; Microsoft Graph, or PowerShell Cmdlets. Both methods are very useful. However, explaining both in one article will be overwhelming. In this article, I’ll focus on how you can fetch the information using PowerShell Cmdlets. The method I explain here is manual. However, the PowerShell scripts can be automated to run as a scheduled process (I might explain that later in another article too). Let’s see how it works.

The method explained here is exporting the AAD users into a CSV file first, and then Power BI imports data from the CSV. You can use any other intermediate data sources such as Excel, SQL Server, etc if you want to. You just need to use their PowerShell cmdlets or parameters to do that.

Special thanks to Aaron Nelson for helping on preparing the demo for this article. Anytime I have a PowerShell question, he is the master who just finds a way to do it in a few seconds. Connect with him using his blog, Twitter, GitHub, or LinkedIn profile.

Click through for the Powershell-based solution.

Comments closed

Powershell Equality Operations

Published 2021-12-02 by Kevin Feasel

Dave Mason is not amused:

When comparing two values in PowerShell, you’ll have to march to the beat of a different drum. The syntax is drastically different:

The short reason why Powershell uses equality operators like -eq is that Bash uses them. Though the funny thing is that Bash actually uses == for string equality comparison and only uses -eq for numeric equality comparisons. The norm for POSIX is =, adding yet another level of fun.

Comments closed

Variables and Scope in Powershell

Published 2021-12-01 by Kevin Feasel

Dave Mason continues a quest into the bowels of Powershell:

Let’s talk a little bit about PowerShell variables and how long they exist within the scopes they’re defined. I’ve encountered some behavior that for me, was unexpected. It’s made my development efforts unproductive–especially when it comes to debugging.

Just like with notebooks, it’s important to remember that the Powershell prompt has a session, and that you aren’t running fresh every time. You can also use Dave’s solution to the problem, which makes sense as well.

Comments closed

Powershell Functions and Return Type Oddities

Published 2021-11-30 by Kevin Feasel

Dave Mason goes down the rabbit hole:

As a result of some struggles trying to automate a process, I’ve learned some things about PowerShell. After getting to the bottom of a time-consuming problem, I thought it worth a blog post that might save someone else some time and heartache.

Let’s begin with this simple function named Get-RandomDate. It generates and returns a random date that is between today and X days ago. It has an input parameter $DaysAgo, which is of type [System.Int32]–it is a mandatory parameter.

That’s all straightforward, but then things get weird.

Comments closed

Managing File Retention in Blob Storage

Published 2021-11-23 by Kevin Feasel

Jeet Kainth shows how to configure a retention strategy in Azure Blob Storage:

This blog is a follow up to a previous blog I wrote about backing up Azure Analysis Services cubes in Azure, that blog can be found here. This blog shows how to implement a retention policy using PowerShell in Azure Runbooks to remove the backups after a set number of days. To create a new Runbook in the Azure portal, go to the relevant Automation account in the relevant resource group and then select Runbooks from the left hand pane. Note you will need to add the Az.Storage module to the automation account to be able to use some of the commands listed in this blog.

Click through for the process, including Powershell code to perform the task.

Comments closed

Using Enums in Powershell

Published 2021-11-22 by Kevin Feasel

Robert Cain quietly tells us that Powershell is a real programming language, sysadmins who claim to hate programming:

This post begins a series on using Classes in PowerShell. As a first step, we will cover the use of an Enum, as enums are frequently used in combination with classes.

An Enum is a way to provide a set of predetermined values to the end user. This allows the user to pick from a finite list, and assure a value being passed into a function or class will be valid.

Click through to learn more about enums and how they work in Powershell.

Comments closed

Index Usage across Replicas

Published 2021-11-17 by Kevin Feasel

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