Powershell

In my previous post, I mentioned that I wasn’t checking the status of my RSJobs because I had logging built into my function. I originally tried to log out to plain text files with Out-File, but quickly hit a wall. In a multi-threaded script, attempting to write to the same text file from multiple threads results in collisions and waits at best, and outright failure at worse. Perplexed, I turned to the PowerShellHelp channel in the SQL Community Slack and asked for suggestions for thread-safe logging to text files.

Intro

PowerShell has had a native method for spawning multiple “threads” ever since I can remember, in the form of the *-Job functions. They work OK, but there are a couple downsides:

• Each job is its own PowerShell process, so it takes a non-trivial amount of time and memory to spin each up
• There’s no built-in method for throttling the number of concurrent jobs

This combination will become an ugly mess if something spins out of control and you spawn dozens or hundreds of jobs. PowerShell jobs are better tailored to small-scale asynchronous background processing. You can wrap the functions to limit the number of concurrent jobs but again, there’s a lot of overhead involved in creating and tearing down jobs. Warren Frame created Invoke-Parallel, which uses runspaces (lighter-weight than jobs) and allows for throttling, but isn’t quite as full-featured as jobs are.

@SQLMonkeyNYC asked on Twitter this morning:

Pat Phelan replied, suggesting that dbatools can do it, but after thinking on it for a bit and poking at a few functions, I realized that it’s not possible with a single function (yet). No worries. We can do it in three lines for now.

Explanation

1. Grab the table from the source database and export the create script. I had to use -PassThru because otherwise, Export-DbaScript will create a file. Not the worst thing, but writing the file and the reading it immediately afterwards is a bit messy. Although now that I write that out, I suppose I could capture the filename that’s output and pass that to -QueryFile in the next step. Gotta love PowerShell - there’s always a few ways to do things.
2. Take the CREATE TABLE script that we just exported from the source database and run it against the destination.
3. Run Copy-DbaDbTableData to bring the data over from the source to the destination.

This is a really quick & dirty example where I’m copying between two databases on the same instance, but should give you the idea. You can copy across instances and copy multiple tables as well.

The first edition of the PSPowerHour is in the books and it looks like it was a big success. This one was dbatools-heavy but I chalk that up to the dbatools community having lots of free time because we’ve automated so many of our tasks :)

Overall Impressions

I signed in about half an hour ahead of the webcast and was the first one there. Shortly thereafter, I was joined by Michael Lombardi (twitter, then Jess Pomfret (blog|twitter) and Chrissy LeMaire (blog|twitter). After ironing out a few glitches, we got everyone in the right place and kicked off the broadcast. Everything ran very smoothly, especially considering the number of people involved - Michael and Warren F. (blog|twitter) did a terrific job of orchestrating everything.

It’s official! I will be speaking at the inaugural PowerHour online lightning demo event on Tuesday, August 21st at 2200 UTC. I’ll be demoing Better, Safer SQL Queries from PowerShell.

If you’re working with SQL Server from PowerShell, either as a DBA, analyst, or anyone else running queries, you’ve probably used Invoke-SqlCmd. But depending on how you’re building your queries, this can be error-prone or a huge security exposure! With the help of the dbatools module, I’ll show you how to write and run these queries better and safer - and make them easier to work into your scripts to boot.

Earlier this week, the PowerHour was announced. What is it? It’s kind of like a virtual user group. One hour, 6(ish) lightning demos (10 minutes or less), centered on PowerShell. All community-sourced and driven - anyone can submit a proposal for a demo and if accepted, you’ll be slotted into an available spot.

They’ve already set up a YouTube Channel so you can either watch live or catch up later on, and the whole deal is being organized and managed through GitHub. Got something you want to show off? Log an issue using the template!

This week I had a user come to me asking about how fields were defined on a few tables he was using in writing some reports. Long story short, he’s been tasked with writing some new reports and updating existing ones, but he doesn’t have visibility to the database itself so he’s left with the “ok, let’s try this” approach and then reading error messages to debug when things go sideways. Very tedious.

Every year, I spend the Sunday after SQL Saturday Rochester scanning & processing raffle tickets for our wonderful sponsors. Here’s how the system works:

• Attendees get tickets (one ticket per sponsor) with their name, the sponsors name, and a QR code on them
• The QR codes represents a URI, unique to the combination of event, attendee and sponsor.
• Attendees drop their tickets in a box to enter the sponsor’s raffle prize drawing
• When the URI from the QR code is accessed, it registers in the SQL Saturday system
• Organizers run a report for each sponsor that includes the contact info of all attendees who dropped off a raffle ticket, then email the report to the sponsor

It works pretty well, but the hangup is that most QR scanners will open your web browser (or prompt you to open it) to the URL on each scan. For 150+ tickets, this takes a long time. Every year, I lament “oh, how I wish I could just scan these, collect the URLs into a nicely formatted file, and script this whole thing”.

I’ve been a proponent of dbatools for close to a year now and even contributed to the project, but surprisingly haven’t been a heavy user of it. Mostly due to a lack of opportunity. I’m aware of many of the functions by virtue of working on the built-in documentation and following the project and presentations about it.

So when the need arose to move a development/test instance of SQL Server from a VM onto a physical server, I knew exactly what I wanted to do. I was warned that the contents of this instance had been moved once before and it resulted in over a week of work and a bunch of trouble. I can’t speculate on why this was as I wasn’t there to see it, but I wasn’t going to let that happen on my watch. So, with equal parts hubris and stubbornness (and a dash of naïveté), I dove in. We have the technology. We will migrate this thing.

This month’s T-SQL Tuesday is hosted by Rob Sewell and he’s posed the following question:

What are you going to automate today with PowerShell?

I’m cheating a little bit in that this is something I did a couple weeks ago, but it was immensely helpful. I’d been working on building out a new instance to migrate our test databases onto, but the developers had an urgent need to do some testing in isolation so they “borrowed” that new instance. But we had an additional requirement - the configuration needed to match production as closely as possible, more than our current test instance. Of course, I reached for PowerShell and dbatools.