Twitter is a strange place. I mean, aside from all the outrage practice and animal videos.

Last week I sent out a tweet about how I used dbatools to update apply cumulative updates to 13 SQL Server instance in 12 minutes. It got nine retweets and over 40 likes. Now, 40 likes in the grand scheme isn’t much, but for a guy like me with a handful of followers – it certainly gets your attention.

In one of the replies I was asked to put a post together about how I did such a thing (the updates, not the tweet), so here we are. If you’ve never used PowerShell, this might be where you could start. It’s absurdly simple. I mean, it was only five lines in PowerShell, and you could easily do it in less than that.

Something Good

This all started because we had some 14 new SQL Server 2017 instances that we were setting up, but we hadn’t yet applied the most recent cumulative update that we are using in our environment.  I started using the Update-DbaInstance cmdlet in the script below to apply to one server, but then I looked at the list of outstanding requests and thought about something Buck Woody once told me.

“You don’t have time for that. You’re going to be dead soon.”

He’s a fantastic fellow, but we should all be grateful he didn’t become a physician.

At any rate, I also remembered Update-DbaInstance can be run against multiple instances with a single command line. Now, I didn’t know if it was consecutively (serial) or concurrently (parallel) so I thought I would test it to find out. And by “test” I mean pointing it at the other 13 instances all at once.

What Can You Do For Me

Here is the PowerShell code for those who would like to play along at home.

$ServerName = "SQL-01","SQL-02","SQL-03","SQL-04","SQL-05","SQL-06","SQL-07","SQL-08","SQL-09","SQL-10","SQL-11","SQL-12","SQL-13"

$UserName = Get-Credential

$PathCU = "\\SomeServer\SomeShares\SQL 2017\Cumulative Updates\CU16"

$VersionCU = "2017RTMCU16"

Update-DbaInstance -ComputerName $ServerName -Restart -Version $VersionCU -Path $PathCU -Credential $UserName -Confirm:$false

Lo and behold, all the updates were at the same time, with boxes in the PowerShell ISE showing me the status for each one. It was magical.

(And no, those are not my server names. Come on, man.)

For those of you even less familiar with PowerShell than your humble host, let me ‘splain what’s going on.

• The things with the dollar signs are variables. I’m just declaring what they are. They aren’t required – you could pass them as literal values to the command at the end of the script. I just did it for readability.

• Get-Credential is a cmdlet that creates a credential object for a specific user name and password. What that means to you is when it is executed you’ll get a prompt allowing you to enter a login and password that can be used to execute the cumulative updates on all the servers. This is nice because you only have to enter this info once, not a time for each server.

• ComputerName is self-explanatory, but again you can pass MULTIPLE values to this. That’s the key to the speed.

• Restart indicates to reboot the server after the cumulative update is applied. That’s right – the 12 minute duration included reboots!

• Version is a kind of code passed to UpdateDbaInstance. It can include service packs as well (i.e. “2016SP2CU9”) but 2017 doesn’t have service packs so you use “RTM” in that space.

• Path is the directory where you have the .exe you downloaded from Microsoft to apply the cumulative update. Put it somewhere you can access it and DO NOT rename it. If you do this won’t work because UpdateDbaInstance calls another cmdlet that knows what that file name should be based on what you passed for Version.

• Credential uses whatever was entered for Get-Credential. If you fat fingered that info none of this will work. This could be your single point of failure.

• Confirm is set to false because I don’t want to click extra “Are you sure?” type of prompts. Live dangerously, my friends.

If you’re still apprehensive about all this you could first run this all with -WhatIf tacked on at the end of the Update-DbaInstance line. That might help identify any errors such as connectivity issues beforehand without actually doing anything.

My Mind Must Be Free

On a personal note, it feels strange for me to write a post about PowerShell, because I feel like I know only slightly more about the subject than Kendra Little’s new puppy. And yet in recent months I’ve started to use it more often to handle my DBA-related tasks. There are two reasons for this:

1. dbatools has made it ridiculously easy to complete DBA tasks with PowerShell
2. Occasionally I just don’t have the time to complete tasks any other way

I’ve realize not all SQL Server professionals are using PowerShell, but even if you aren’t now you really should consider utilizing dbatools.

You might not have time to do things otherwise. That’s what Buck Woody said.