Like many folks using WordPress, I post a tweet each time I publish a blog entry and that’s done automatically by WordPress. In the WordPress Classic Editor, there was a section in the sidebar next to the post to select where the post was publicized (Twitter, LinkedIn, etc.) and customize the accompanying text. By default, the message is just the post’s title, but I like to add in hashtags when appropriate so being able to edit that text is important to me. After switching to the new Block Editor in WordPress 5, I couldn’t find those settings. Here’s how to get them back.Continue reading “WordPress’s Block Editor and the Publicize Feature”
In my previous post, I outlined the preparations we undertook to migrate a large SQL Server 2008R2 instance to SQL Server 2016. This post details migration day.Continue reading “A Monumental Migration to SQL Server 2016 – Part 2”
A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production. With SQL Server 2008R2’s EOL looming, it was time to migrate production to SQL Server 2016.Continue reading “A Monumental Migration to SQL Server 2016 – Part 1”
As we open 2019, I thought I’d take a moment to reflect on the past year.Continue reading “2018 Year in Review”
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.
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.
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 –
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.
The above is a fairly trivial script but it serves the purpose of demonstrating multi-threading with this module.
- 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.
- 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
NotStartedstate, patiently waiting their turn.
- 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.
- So that the RSJob system can keep track of the jobs properly, I’m lumping them into a named
- Because the module’s zip file is so small, I introduced a bit of a delay into each job. Why?
- To give me time to capture screenshots while the jobs are in various states.
- To prevent tripping GitHub’s anti-flooding protections.
So let’s fire this up!
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!
You still need to clean up after yourself! Don’t forget to
Receive-RSJob and then
Remove-RSJob. I have something special for that.
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.
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.
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.
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.
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.
- 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.
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:
WHERE IS YOUR HAT!? HOW WILL I FIND YOU!?!?
— Bert Wagner (@bertwagner) November 8, 2018
Wtaf? The hat can come off?!?
— Andy Mallon 🧔 (@AMtwo) November 8, 2018
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!
My album of people and events at Summit 2018. Somehow a number of photo opportunities were missed!
@SQLMonkeyNYC asked on Twitter this morning:
#sqlhelp Does anyone have a product that will back up a single table and restore it to another database? Thanks in advance!!
— Mr Sunshine aka Gandalf!! (@SQLMonkeyNYC) November 14, 2018
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.
- Grab the table from the source database and export the create script. I had to use
Export-DbaScriptwill 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
-QueryFilein the next step. Gotta love PowerShell – there’s always a few ways to do things.
- Take the
CREATE TABLEscript that we just exported from the source database and run it against the destination.
Copy-DbaDbTableDatato 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.