One Peril of Database Proliferation

By now many of us have upgraded from SQL Server 2008R2 and we’re on the “regular Cumulative Updates” train now. For the rest, it’ll (hopefully) happen soon. And since we want a long runway, we’re upgrading to SQL Server 2016 or 2017. Current software! New features! Mainstream support! But…there’s a catch.

Staying current

DBAs & sysadmins don’t want to fall too far behind on patching for a variety of reasons. It used to be that monthly patch cycles were primarily for Windows Server. When we were running SQL Server 2008R2, our last significant update was years ago. SQL Server 2012 is now out of support and isn’t receiving updates very often. Point being, a SQL Server update install has been the exception, not the norm, in the patch cycle for many of us the past couple years.

Appearance: SQL Data Partners Podcast #161

Carlos Chacon (twitter) was kind enough to have me back on the SQL Data Partners Podcast to talk about my experiences with managing 8000 databases on a single instance and upgrading to SQL Server 2016. He, Kevin Feasel (blog | twitter) & I had a great conversation in which I may have gushed a bit about dbatools. Then we wrapped up with the SQLFamily questions as we didn’t do them on my previous appearance last year.

Tidier PowerShell Scripts with Default Parameter Values

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.

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.

Things I Wish I Knew When I Started

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.

Wordpress's Block Editor and the Publicize Feature

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.

A Monumental Migration to SQL Server 2016 - Part 2

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 Monumental Migration to SQL Server 2016 - Part 1

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.

2018 Year in Review

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.

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.

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.