Posts

I was recently working on a PowerShell script to set up some new databases for my users and found myself writing the same things over and over again.

1
2
3

Invoke-DbaQuery -SqlInstance MyServer -Database $SrcDB -Query "select field1 from table...";
Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "update table...";
Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "insert into table...";

By the 4th Invoke-DbaQuery, I found myself thinking “this repetitive typing kind of sucks.” Then I remembered Chrissy LeMaire’s segment in the first PSPowerHour where she talked about default values, and her accompanying dbatools blog post. Most of the blog posts and demos of this feature focus on using it from the command line, so I had overlooked the fact that I could use it from within a script as well, and even change the values when looping.

Way back in August, Matt Cushing (blog|twitter) was preparing to teach and asked for a list of “what do you wish you’d known when you started” items that he could present to his students. I threw a barrage of Twitter direct messages at him and he incorporated much of it into his post, but I thought it was worth posting here as well.

Most of these aren’t technical “I wish I understood the nuances of filtered indexes” type of thing. Many of them are hard-learned lessons that would have kept me out of hot water had I known them previously.

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.

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.

Final Prep

We completed our nightly backups as usual on Friday night, so when I arrived Saturday I kicked off a final differential backup to catch any overnight changes. We’ve multi-threaded Ola’s backup script by creating multiple jobs and I started them all at once with (of course) PowerShell.

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.

As we open 2019, I thought I’d take a moment to reflect on the past year.

Blogging

2018 was my biggest year of blogging yet! I published 28 posts and got more traffic than I ever thought possible, thanks in no small part to being linked by Brent Ozar’s (blog|twitter) newsletter.

I also moved my blog to its own domain (which you’re looking at now), with independent hosting. I’d been putting this off for over a year. The old Wordpress subdomain name was clunky and I don’t feel like it properly reflected the direction I’ve taken since setting it up. I’ve had a few bumps along the way which I can’t quite explain (and as a result, didn’t get a post out for New Year’s Eve as I’d hoped), but I’m enjoying the freedom of having my own domain and full control of what I do in managing the site.

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.

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.

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.