Press "Enter" to skip to content

Category: Powershell

An Overview of Powershell for DBAs

Published 2024-04-26 by Kevin Feasel

David Seis shares tips from an upcoming talk:

PowerShell is an essential tool for SQL Server database administrators looking to streamline their workflow and automate repetitive tasks. When it comes to troubleshooting your SQL Server instances, PowerShell is an essential tool for the DBA toolbox.

As a versatile scripting language and command-line shell, PowerShell enables seamless integration of commands, arguments, variables, and modules with an interface and logic that SQL users would find pleasantly familiar. This blog post will guide you through the basics of PowerShell, demonstrate how to implement simple automation for routine database tasks, and explore advanced automation strategies to optimize your database management.

The post starts as a basic primer on Powershell but also includes plenty of notes on good ways to make use of the language as a DBA.

Leave a Comment

Copying Azure SQL Managed Instance Databases

Published 2024-04-25 by Kevin Feasel

Scott Klein performs a migration:

So, back to our customer. They essentially lifted and shifted their on-premises databases to Azure SQL Managed Instance and have been using it successfully for nearly two years. Again, this is awesome.

Last week they came to us and asked about reporting with Managed Instance. They were looking at data marts and data warehouses, but we needed more information. It turns out they have some people that just want the ability to query the databases, and potentially hook up Excel to these databases for data analysis.

The caveat is that the people I was talking to didn’t want to give the other group direct access to the production environment. Toootally get that. Yeah, like 100% get it. So, what are the options?

Read on for the solution Scott came up with.

Leave a Comment

Using Powershell to Set the SQL Server Port Static

Published 2024-04-19 by Kevin Feasel

Vlad Drumea doesn’t want a dynamic port number:

This post demos a script that I’ve put together to automate the configuration of the static TCP port for a SQL Server instance using PowerShell.

The script is derived from another PowerShell script that I’ve written to help spin up SQL Server test instances in my home lab.

This should be helpful you’re working in a restrictive environment where you can’t install additional PowerShell modules, and you couldn’t take advantage of dbatools’ Set-DbaTcpPort.

Click through for the script, but also use dbatools whenever you can because it’s a good product and I haven’t done any unpaid shilling for them in far too long.

Leave a Comment

Waiting for a Job to Complete in Powershell

Published 2024-04-18 by Kevin Feasel

Patrick Grueanuer waits for a job:

The cmdlet Wait-Job waits until one or all of the PowerShell jobs running in the session are in a terminating state. In this blog post I will show you an example you can build on. Let’s get started.

Start-Job creates one or more PowerShell background jobs. These jobs are running hidden in the background and enable you to continue your work in PowerShell. This example starts a port scan background job.

Click through to see how Start-Job works and what you can do with other job-related cmdlets.

Leave a Comment

Searching for Files in a Blob Storage Container

Published 2024-04-10 by Kevin Feasel

Andy Brownsword hits one of my bugbears:

Shifting from handling data on premises to Azure has been a real change of mindset. Whilst what I want to build may be similar, the how part is completely different. There’s a learning curve not just to the tooling but how you use it too.

This is one of those instances.

I had a storage container with files which had a date in their name. I wanted to perform a wildcard search to select some of them. That sounds straight forward, right?

This is unnecessarily painful, especially if you’re trying to find the right full backup in a container filled with full and transaction log backup files. Andy’s solution does work but also requires a full scan of keys. And I don’t think there’s a better way to do it.

Leave a Comment

Building a Powershell Transcript

Published 2024-04-04 by Kevin Feasel

Rod Edwards saves the transcript:

I have a love for Powershell, it makes my life as a SQL Server tech so much easier when I have to either treat my instances as a herd cattle, rather than separate bovine entities, or have them interact with other technologies as part of a larger task… ie… play with the other farm animals if you will.

Enough of the animal metaphors.

We write Powershell, we run powershell, we swear when our powershell does not do what we would like it to. To this end, we need to log the output somewhere, particularly when our scripts are not going to be run interactively so outputting to the screen becomes pointless.

Click through for a depiction (sans farm animals) of how transcripts can be useful and how you can create one.

Comments closed

Using Runbooks in Azure Automation

Published 2024-03-21 by Kevin Feasel

Rod Edwards has a process for that:

Nobody likes to do the same monontonous task over and over again. Well, saying that, maybe some out there do in order to look and feel busy…but I don’t, as I nearly always have something else more pressing or fun or interesting to do. By automating those repeatable tasks, it reduces boredom, chance of errors, and stress if you’re already a busy bunny.

This is where Automation comes into play, and in Azure we have a few options. This post focuses in Azure automation.

Read on to see how Azure Automation works and how to build a Powershell runbook in it.

Comments closed

Setting up Replication with dbatools

Published 2024-03-13 by Kevin Feasel

Jess Pomfret continues a series on replication in dbatools:

This post is focusing on how to setup replication with dbatools. We support all three flavours – snapshot, transactional and even merge replication!

In this article I’ll be creating a transactional publication, but the steps for setup are very similar no matter which flavour you’re implementing.

I’ll walk through and demonstrate all the steps to setup replication in this article as dbatools allows us to complete them all. However, I won’t go into a lot of details on why or how replication works, or provide guidance on best practices. If you need more information on replication as a technology then I recommend visiting the Microsoft Docs.

Read on for a demonstration of how the cmdlets work for adding a publication, articles, subscriptions, and more.

Comments closed

Tips for Using Powershell in Azure

Published 2024-03-12 by Kevin Feasel

Paul Harrison shares a few tips with us:

When I’m working with a new object in Azure I often don’t know where the information I care about is actually found in output. PowerShell makes it easy to navigate through objects, however it isn’t easy to get an overview of all properties available if they’re nested 5 levels deep. I like to use ConvertTo-JSON to help me get a general understanding for a new object and which properties are available and how to find them.

Read on for more information about converting to JSON and four other tips.

Comments closed

Configuring a New Powershell Installation

Published 2024-03-07 by Kevin Feasel

Jeff Hicks starts over:

The other day on X, I was asked about what things I would setup or configure on a new PowerShell installation. This is something I actually have thought about and face all the time when I setup a new demo virtual machine. I had been meaning to build new tooling to meet this challenge, and the question provided the spark I needed to get off my butt and get it done.

Read on for some thoughts on the topic, as well as a lengthy script to get you going.

Comments closed