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:$falseLo 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:
- dbatools has made it ridiculously easy to complete DBA tasks with PowerShell
- 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.
Nice article – thanks for posting. It took me some playing around (very new to PowerShell), but finally got it working. This is going to save a ton of time. Much appreciated!
Awesome, I plan to test your script at home first. I would love to see a video of the installation.
Thank you! Very well explained with comedic relief. You have Brent Ozar’s training/communication skills. You moving to Iceland anytime soon 🙂
Haha! The sections are song names from Utah Saints, very clever!
The -InstanceName parameter of Update-DbaInstace command is not working for me. Can anyone tell me how to use it?
I had the same problem and it was because I needed to update my version of DBATools.
Very useful indeed.
Can you provide further explanation as to how you are determining the version name? Especially for SQL Server security patches. Thanks.
The easiest way to determine a SQL Server version is by executing SELECT @@VERSION on the instance. That will tell you the version, edition, and some other information you might find helpful.
Hi Matt, I was also looking for a method to update to a particular Security Update. It seems that you should be able to use the command below to update to a specific KB Article/Number:
Update-DbaInstance -ComputerName “SQL-01” -KB 123456 -Restart -Path \\network\share -Confirm:$false
I’ll be trying this soon so will report back on how it goes.
Great post. Just wondering if all of the SQL instances were at the same patch level prior to applying the update? Eg: SQL-01 is running a different SP/CU/SU level from SQL-02 but both will be updated to CU16?
Thanks
In the case above they were all new instance with RTM installations and no CUs, but I’ve run scripts similar to the one in the post against instances with the same version but with different CUs to get them all to a single CU level.
i set the version to this below but it’s not working.
$VersionCU = “2019RTMCU12”
Microsoft SQL Server 2019 (RTM-CU12) (KB5004524)
i’m trying to go from 15.0.4153.1 to 15.0.4223.1
this is the error – WARNING: [16:27:02][Get-SqlInstanceUpdate] Couldn’t find an exact build match with specified parameters while updating SQL2019
Any thoughts?
Ryan ! 🙂 I had the same issue.
You need to manually update the JSON file to include the verion you want to go to.
Giant pain in the rear, but that’s PShell for ya.
Solution example here.
# ———————————————————————-
## PATCHING SQL Servers from a \\share with PowerShell
## ISSUE: https://glutenfreesql.wordpress.com/2020/04/12/manually-adding-patch-information-for-update-dbainstance/
## SOLUTION: need to update JSON file manually – dbatools-buildref-index.json
## here: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.145\bin\dbatools-buildref-index.json
# example: SQL2019CU17 = version 15.0.4249.2
# trying to apply KB 5021124 to take SQL2019 from version 15.0.4249.2 to 15.0.4280.7 – this needs to be in the JSON file ** leave out the .7 ***
# {
# “Version”: “15.0.4280”,
# “KBList”: “5021124”
# },
## IMPORTANT !!
## update JSON file here: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.145\bin\dbatools-buildref-index.json
## DELETE this json file C:\Users\\AppData\Roaming\PowerShell\dbatools\dbatools-buildref-index.json
## then TRY AGAIN
# ————————————————————————————————————————
Here is the full issue + solution + an example:
## PATCHING SQL Servers from a \\share
#——————————————————————————————–
## ISSUE: https://glutenfreesql.wordpress.com/2020/04/12/manually-adding-patch-information-for-update-dbainstance/
## SOLUTION: need to update JSON file manually – dbatools-buildref-index.json
## here: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.145\bin\dbatools-buildref-index.json
# example: SQL2019CU17 = version 15.0.4249.2
# trying to apply KB 5021124 to take SQL2019 from version 15.0.4249.2 to 15.0.4280.7 – this needs to be in the JSON file ** leave out the .7 ***
# {
# “Version”: “15.0.4280”,
# “KBList”: “5021124”
# },
## IMPORTANT !!
## update JSON file here: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.145\bin\dbatools-buildref-index.json
## DELETE this json file C:\Users\A#CampbellE\AppData\Roaming\PowerShell\dbatools\dbatools-buildref-index.json
## then TRY AGAIN
# ————————————————————————————————————————
## SQL2019CU18SecurityUpdate
## we want to update from version 15.0.4249.2 to 15.0.4280.7 *** drop the .7 for the JSON file ***
## KB 5021124
# The following array contains the destination servers
$ServerName =
“server1”,
“server2”,
“server3”,
“server4”
$UserName = Get-Credential
$PathCU = “\\patchingshare\SQL2019CU18SecurityUpdate”
# using Update-DbaInstance from dbatools here:
Update-DbaInstance -ComputerName $ServerName -KB 5021124 -Restart -Path $PathCU -Credential $UserName -Confirm:$false