Thread-safe PowerShell Logging with PSFramework

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.

Continue reading “Thread-safe PowerShell Logging with PSFramework”

PowerShell Multithreading with PoshRSJob

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.

Enter PoshRSJob

Boe Prox comes to the rescue with his PoshRSJob module. This module provides runspace-based functions that mirror PowerShell’s native job functions to create – Get, Receive, Remove, Start, Stop, and Wait – plus some additional functionality like the ability to import modules (without needing Import-Module inside the Scriptblock) and throttling the number of concurrent jobs running.

Code

Explanation

The above is a fairly trivial script but it serves the purpose of demonstrating multi-threading with this module.

  1. In a loop, I’m creating two dozen RSJobs to fetch the PoshRSJob module from GitHub and write it out to a directory on my computer.
  2. I’ve set a throttle limit of 3; while all 24 jobs will be created, only three will run at any given time and the remainder will be in a NotStarted state, patiently waiting their turn.
  3. I’m passing value of the loop counter and my output directory into the job’s scriptblock so that I can write out 24 individually-named files.
  4. So that the RSJob system can keep track of the jobs properly, I’m lumping them into a named Batch.
  5. Because the module’s zip file is so small, I introduced a bit of a delay into each job. Why?
    1. To give me time to capture screenshots while the jobs are in various states.
    2. To prevent tripping GitHub’s anti-flooding protections.

So let’s fire this up!

Jobs created, but only three running

I’ve started up the script and it’s created all the RSJobs, but only the first three are running because that’s where I set my throttle limit. Let’s check in on the progress with Get-RSJob – all the same concepts you’re used to with regular PowerShell jobs (receiving, removing, checking for errors, etc.) work with RSJobs too!

And we’re done!

You still need to clean up after yourself! Don’t forget to Receive-RSJob and then Remove-RSJob. I have something special for that.

Bonus Round!

Let’s say you’ve got a lot of RSJobs. Or maybe long-running RSJobs. Wouldn’t a nice, user-friendly view of the progress be handy? You bet it would!

This is both dense and verbose, but I wrote it really quickly and it works (but not just for this post, I’ve used it in production), so I’m not tinkering too much with it right now. Once the jobs are created (and a few have started), this script runs a loop watching the output of Get-RSJob and updates a progress bar to reflect where we are. It also takes care of receiving and removing the completed jobs.

It’s true that I’m not checking for any errors in my job output. In the production process that I multithreaded, the Scriptblock inside each thread does its own error logging to a file.

Double Bonus

Did you notice the paths I’m writing to and the terminal theme? Yeah, this works on PowerShell Core too. Everything you see here ran on macOS as I was writing this post.

Conclusion

For light to moderate use, PoshRSJob is a really handy way to multi-thread processes that aren’t natively multi-threaded in PowerShell. It doesn’t seem to scale well to hundreds of threads, though. There is an open issue that seems to be related on GitHub, but I’ll discuss my own experience with it on a near-future post.

Tips for Attending a SQL Saturday

Matt (blog | twitter) is preparing for his first SQL Saturday presentation next weekend in Washington, DC. He’s asked:

I wanted to get an idea of some good, bad, and surprise experiences that people had at everything from a SQL Server User Group meeting to PASS Summit. Things you found out right before, during or even after that you were glad you did or wish you did.

Random Thoughts

SQL Saturdays are similar to PASS Summit, but much smaller in scope and budget. Most SQL Saturdays have a twitter hashtag; follow it before the event so you can get an idea of who’s attending and make plans to meet some of those people.

If your SQL Saturday has an after-party, it’s usually not heavily attended as people tend to want to get home to their families (unlike Summit, where you’re already away from home). But I definitely encourage you to go if there is one! You might even find yourself invited to an after-after-party.

I can’t say I’ve had a bad experience at a SQL Saturday, although I have sat in on a session or two that didn’t really grab my interest the way I’d hoped. And unlike PASS Summit, it’s really hard to gracefully exit the smaller rooms at SQL Saturday mid-session if you decide it’s not really for you.

The biggest surprise to me after my first couple events was the inspiration that I had coming out of the event. After each event, I have new ideas for projects, changes to make at work, blog posts, and ways to give back to the community.

Other Tips

  • Volunteer! If you don’t sign up before the event, talk to the folks at the registration/check-in desk and ask if they need any help. Every session needs a room monitor – just someone to get a headcount, help the speaker with timing (if they want), find help for A/V issues, pass out and collect session evaluations. You’re going to be in the room anyway. This is a great way to meet both organizers and speakers – folks who are active in the community.
  • If there aren’t any sessions in a timeslot that grab your attention, skip it and read the next two items.
  • Network with your fellow attendees. These are folks who live and work in your area. They may become your next job lead, or your next new hire.
  • Talk to the sponsors! Make sure you thank them for sponsoring. If there are sponsors whose products you currently use, ask them questions about making the most of those products. Just don’t monopolize their time; they need to talk to new people as well to justify their sponsorship dollars. I’ve been known to hang out at the SentryOne booth on more than one occasion. I’m just a very satisfied customer and love talking with them about how I’m using their products.
  • If you find a session particularly engaging or relevant to your interests, catch the speaker when the session is over and say “this is really interesting and I can see myself using it this way, can we talk later?” Ask if you can connect on LinkedIn; follow them on Twitter.
  • Attend the after-party (if there is one)! It’s a great way to connect with organizers, speakers, and volunteers after the stress of the day has passed. It’s a smaller, quieter environment and easier to have a longer conversation.

After SQL Saturday

Remember those people you met on Saturday? Keep that conversation going. Make contact on LinkedIn or Twitter. Do they have a blog? Follow it! For example, I met Matt at PASS Summit 2017 and we haven’t seen each other since. But we talk regularly on Twitter and we follow each others’ blogs. If you’re meeting more local people (which will happen at SQL Saturday), catch up at the next user group meeting, or arrange lunch sometime.

PASS Summit 2018 – A Lesson in Personal Branding

Last year at PASS Summit 2017, I heard a number of comments related to people not recognizing me without the hat in my social media photo/avatar. The linked post started and concluded with the notion that I needed to take a new photo.

That never happened. Instead, I embraced The Hat. And it was totally worth it. I got a bit of ribbing from folks early in the week but it was all friendly. Trying to meet up with people at various points in the week, I’d get messages like “I’m in (location), looking for your hat, where are you?” People introduced themselves when they spotted me doing my PASS Ambassador duties because they recognized the hat from my photo.

A couple people had more fun than I expected:

I met a few people sans hat and they didn’t immediately connect me (the person) with me (the Twitter account). Not a big deal, but I learned an important lesson from it. Apparently, this is how I’m known in a portion of the community now – the guy in the hat. Folks are accustomed to seeing/talking to me on Slack/Twitter with a particular image next to my name. That’s the image of me they have in their mind. Therefore, it’s best if I maintain that appearance offline as well.

I’m OK with this. It sets me apart from the crowd a bit and as time marches on and protecting my head from the elements becomes more important, it becomes a more necessary part of my wardrobe. At big events like this where I’m actively trying to meet people I’ve only spoken with online, I just need to be more consistent about wearing it.

The Hat stays!

PASS Summit 2018 – Photos

My album of people and events at Summit 2018. Somehow a number of photo opportunities were missed!

Copying Individual Tables with dbatools

@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.

T-SQL Tuesday #108 – Learning Tech Beyond SQL Server

Malathi Mahadevan (b|t) is hosting this month’s T-SQL Tuesday, a monthly blog party for the SQL Server community.

Malathi has asked us to:

Pick one thing you want to learn that is not SQL Server. Write down ways and means to learn it and add it as another skill to your resume. If you are already learning it or know it – explain how you got there and how it has helped you. Your experience may help many others looking for guidance on this.

The timing for this works out well for me, as I’ll be working on my 2019 goals very soon and education is part of those. But right now, we’re kind of a homogenous shop, which makes stretching beyond SQL Server a bit more effort. I’m occasionally indecisive, so I’m going to throw out a few ideas of things to learn in 2019.

  1. Need to learn for work: Postgres on AWS. We have a pilot project starting up using this and eventually, my team will need to support it. So, we need to at least understand the basics – backup & restore, basic monitoring/troubleshooting.
  2. Want to learn on my own: A document database of some description. I’ll probably land on MongoDB due to convenience and cost (or lack thereof). Runner-up: DynamoDB.
  3. Stretch goal: I still don’t entirely get the concepts behind Docker or Kubernetes (although I’m close on Docker). I’ve tried to run SQL Server 2017 on my Mac via Docker, but Docker itself seems to make the machine unstable. Kubernetes is a complete mystery to me; I know a few people who are doing “stuff” with it, but haven’t talked with them about it yet.

But you may be asking “Andy, where’s Azure? CosmosDB is a document database on that platform. And PostGres is on Azure too, why not learn it there?” My shop is starting to do new work on AWS, so the opportunities are more available there and I can apply what I learn at work more readily. So it’ll cost me nothing out of pocket if I go with that or MongoDB. As for the learning plan:

  1. I’m going to have to do this one for work regardless, so step one will be setting aside time at the office to work through the AWS tutorials and documentation. Then set up a few test databases, throw some data in, and play around.
  2. I know we have a few MongoDB experts locally and a user group, so hopefully they can get me started in the right direction. I’ve got a Raspberry Pi 3B as well as a Pi Zero W that I can put to use here.
  3. Apparently one can run MongoDB on Docker with Kubernetes so maybe I can kill two birds with one stone? It could be an excuse to pick up a couple more Pis. Or maybe fire up some VMs on the rack mount server that’s been sitting in my basement for 5 years.

MongoDB & Docker/Kubernetes are completely new ground for me to cover, so just figuring out where and how to get started may be a challenge. By the time publishes, I may already be starting to read the MongoDB manual en route to PASS Summit – and I know I can talk to people about this stuff at Summit to get ideas on where to start.

Addendum

This post was written published early by accident. My reading of the MongoDB manual didn’t happen last Tuesday as I was not feeling well on my flight to Seattle.

PASS Summit: Planting Seeds of Knowledge

On the eve of this year’s PASS Summit, I find myself reflecting on my first Summit in 2012. My employer was generous enough to pay for not only Summit itself, but a pre-con session on Tuesday as well.

I was a developer with an interest in SQL Server and PowerShell at the time, not a DBA. Becoming a DBA wasn’t on my radar yet. Regardless, I used the opportunity to attend a full-day class on managing SQL Server with PowerShell, taught by Allen White (blog | twitter). One of the big focal points of the day was the SQL Server Management Objects, aka SMO.

What I learned that day wasn’t immediately usable in my day-to-day job. Parts of it flew way over my head at the time. But the seeds were planted. It’d be several years before I got to the point of really applying it. Initially, my application was limited to some development “DBA-lite” tasks and suggestions for our sysadmins.

After a job change to become a full-time DBA, I started using and then contributing to dbatools, which uses SMO heavily. That previous exposure to SMO proved very beneficial. dbatools provides a lot of functionality, but it doesn’t do “everything.” In my own scripts, I’ve used that knowledge of the SMO object methods and properties to extend that functionality.

As I pore over this year’s schedule, I remind myself and ask you to consider not just what’s applicable to your present work and environment. Catch some sessions just to see what else is out there, or what’s coming. It’ll open doors for you in the future. Being informed about a wider range of topics will help you guide the conversation toward better solutions in the future. You don’t have to become a master of the topic; just know that something exists, what it can be applied to (equally valuable: where it won’t apply), and where to start gathering information if the solution is worth pursuing.

PASS Summit 2018 – Let’s Hang Out! Where to Find Me

In addition to being an amazing opportunity for both technical and professional development, PASS Summit is a #sqlfamily reunion and a huge networking event. Catching up with old friends, meeting new ones, finding out who’s doing what with which technologies, etc.

This is one of the few times each year I’m anything even approaching a social butterfly and I’ve had one or two people ask about my schedule so we can plan meetups. Summit is so huge that you have to plan these things. You can’t count on randomly bumping into people around the convention center or city, and if you happen to spot someone you really want to meet, you have to take advantage of that opportunity the first time it presents itself.

I will have some dbatools badge ribbons with me this year, but I waited too long to re-order so they’ll probably run out early. Get ’em while you can!

So, with less than two weeks until we’re all together in Seattle, where will I be found? Here’s my schedule thus far for “extracurriculars” – non-session, non-keynote time. Keep in mind that this is just what I have booked right now – I’ve purposely kept time open as I know other things will pop up, plus I like to see where the breeze takes me

Note: I’m an early(ish) riser and while I’m not out until 3 AM at #SQLKaraoke, I don’t turn in at 9 PM either. I’m always ready for a late-night snack & beer or if you want to grab breakfast, we can probably make that happen Thursday or Friday.

Tuesday

  • 12:00 – My flight is scheduled to arrive in Seattle
  • 13:30 – 13:30 – PASS Local User Group Leader meeting. I’ll probably arrive late and I expect to catch some flak from Grant for that.
  • 16:00 – 18:30 – SQL Trail Mix
  • 18:00 – 19:30 – v.20 Celebration
  • 19:30 – 22:30 – I’ll be at an event off-site.

Wednesday

  • Breakfast with my Summit Buddies crew
  • 11:30 – 12:30 – Hosting the Development & Administration Tools Birds of a Feather table in the Quest Dining Hall
  • 15:00 – 18:00 – Community Zone US East Spotlight hours. I won’t be there the full 3 hours but as I’m in the US East region, I’ll be there for a while meeting folks.
  • 19:00 – 21:00 – I’ll be at an event off-site.
  • 20:30 – 22:30 – I’ll be at a different event off-site.

Thursday

  • Sometime – I need to find a geocache so I can get one day closer to having a find recorded on each day of the calendar year. Fortunately, there are a couple very close to the convention center
  • 11:00 – 12:00 – The Apress booth will be hosting a signing for Malathi Mahadevan’s (b|t) book Data Professionals at Work, along with a SentryOne giveaway. Looking forward to picking up a copy of the book and getting it signed by the contributors, high school yearbook style!
  • 11:30 – 12:30 – Lunch
  • 12:45 – 13:45 – I’ll be posted as a PASS Ambassador in the 6ABC Lobby helping people find their rooms and sessions. I’ll be wearing a red PASS vest for visibility.
  • 17:00 – 18:00 – PASS Board Q&A
  • 19:00 – 22:30 – Games Night in Ballroom 6A. If you aren’t signed up for one of these (they’re happening both Wednesday and Thursday nights), it’s really a good time to unwind and have a relaxing evening talking with people over a board or card game.

Friday

  • 12:00 – 13:00 – Hosting the Migrating SQL Server Birds of a Feather table in the Quest Dining Hall
  • 13:00 – 14:00 – I’ll be posted as a PASS Ambassador on the Skybridge, once again in the red PASS vest
  • 22:15 – Plane leaves Seattle. If you’re flying out on JetBlue, hang out with Matt (b|t) & me in the terminal!

Please ping me on Twitter or the SQL Community Slack (@alevyinroc there too) if you want to meet up during the week, whether it’s to grab a bite to eat, attend a session together, get a beer, trade badge ribbons, or just say hi!