Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to apply you’re going to be spending a considerable chunk of time on it.
Thankfully, the dbatool Powershell Module makes this process much easier! In fact, once you get it figured out, using Update-DbaInstance to patch your SQL Servers becomes very simple. But, getting it figured out can be a little confusing, so I’ve outlined the steps below that I use. The biggest hangups I had were setting up a central patching location and using a credential to access it.
Set Up A Central Patching Location
First, you need a network share to put your SQL Server patches on. This share needs to be accessible by all of the SQL Servers you are wanting to install updates on. What works for me is to have a directory structure of ‘SQLSERVER/VERSION/SP/CU’, which contains each executable file in the appropriate directory. So, for patching SQL Server 2016 I would have a path of ‘SQLSERVER/2016/SP2/’ which will look something like this:
Note that the SP2 directory contains the executable for SP2, as well as the directories for the other various CU updates that apply to SP2. Now all we have to do is specify the network path in our Update-DbaInstance call and it can find the patches that it needs. In my case I typically specify the root of the SP directory (SP2 in this case), and dbatools will figure out the CU level on its own from there. In theory, I could just point it to the root SQLSERVER/2016 directory (not shown) and it’s supposed to figure out what patches to apply. However I did not try from there, so I cannot say if it works or not.
Using A Credential With dbatools
This one stumped me for a bit. Once you figure it out it is super simple, but it took me a little while to find examples and get it working. For this you have to use the Get-Credential cmdlet, and then pass the credential in to the Update-DbaInstance call. Get-Credential isn’t part of the dbatools library, but rather a native powershell command.
Running the following command will create a credential for your Active Directory account and save it in a variable named “cred”. When you execute this command, it will prompt you for your AD password.
|
1 2 3 4 |
#create a credential $cred = Get-Credential DOMAIN\your.account |
Specifying A Patch Level
One of the really nice things that Update-DbaInstance allows you to do is specify the patch level that you want to update your SQL Servers to. So, even though I have CU7 listed in my shared directory, I can tell Update-DbaInstance to only patch up to CU5 if I want. To do this, just specify a “version” in your command.
This took a little big of digging, and trial and error, for me, as I couldn’t find an details around how to format the version. But, basically you would specify the version in the format of “2016SP2CU5” for patching SQL 2016 to SP2 CU5.
Here’s The Full Script
Ok, enough talking (typing?), I know you only came to get the full script. One of the things I always do is run Get-DbaBuildReference to ensure that dbatools knows what the latest updates are. Here it is, with comments:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#Update the Build Reference so DBATOOLS is aware of the latest SP/CU versions #Set the versions to whatever version you're using Get-DbaBuildReference -MajorVersion 2016 -ServicePack 2 -Update #create a list of servers that you want to patch $ServerList = 'SQL01','SQL02','SQL03' #create a credential to pass in to the Update-DbaInstance command; this will prompt for your password $cred = Get-Credential DOMAIN\your.account #Set the version that you want to update to $version = '2016SP2CU5' #Start Patching! The -Restart option will allow it to restart the SQL Server as needed Update-DbaInstance -ComputerName $ServerList -Path '\\network\share\path\SQLSERVER\2016\SP2\' -Credential $cred -Version $version -Restart |
And that’s it! I have been testing and using this script a little bit for the last several months to do my SQL Server patching, and have decided that this is going to be my default patching method going forward.
great,
but how to do only for CU on SQL2017
You would just specify that in the version variable:
$version = ‘2017CU5’
What if i have to run this powershell script in a sql job scheduled. I dont want it to prompt for password. Is there a way ?
The documentationn for get-credential has an example of passing in a password so that you do not get prompted, however I would not recommend this as you will have to store the password in plain text.
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/get-credential?view=powershell-6
I would also avoid setting up a scheduled job that patches SQL Servers automatically. This sounds like a recipe for trouble.
How to patch multiple versions? 2012, 2014, 2016, in the same script?
Great question!
You can specify multiple versions in the -version parameter, like this: -Version SQL2012SP3, SQL2016SP2CU3
There is an example of this in the docs: https://docs.dbatools.io/#Update-DbaInstance
Hi , does this tool support FCI cluster
error got ARNING: [10:42:13][Update-DbaInstance] No SQL Server installations found on node1
thanks
Hi Khaled,
I would recommend asking that on the dbatools slack channel (https://dbatools.io/slack/)
Hi,
I have SQL Server 2017 for update CU
i have run script with necessary changes but below error occurs
Get-DbaBuildReference : A parameter cannot be found that matches parameter name ‘MajorVersion’.
At line:4 char:23
+ Get-DbaBuildReference -MajorVersion 2017 -ServicePack 2 -Update
+ ~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-DbaBuildReference], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Get-DbaBuildReference
WARNING: [08:12:20][Update-DbaInstance] Error while looking for SQL Server installations on SQL152008 | Connecting to remote server SQL152008 failed with the following error message : The WinRM client sent a reques
t to an HTTP server and got a response saying the requested HTTP URL was not available. This is usually returned by a HTTP server that does not support the WS-Management protocol. For more information, see the abou
t_Remote_Troubleshooting Help topic.
Can you help me ?
Hi Vipul,
I would recommend posting that error/warning on the dbatools slack channel (https://dbatools.io/slack/). They will have more knowledge about what that means than I will.
Pingback: Patch Update apply and automate SQL server patch for multiple servers patching compliance by DBATools Powershell – Sql server Blog Forum
Hi Eric,
Great tool !! Thanks. Have a quick question. How can you use it to apply a SQL CU to a server running 4 SQL instances? Can you apply the CU to all the instances on a particular server? or you have to specify them in the server list ? $ServerList = ‘SERVER1\INST1,SERVER1\INST2,SERVER1\INST3…..’ Thanks !!
Is it patching them in parallel?
Nope picking one by one..which is time consuming.
Hi Javier,
either i specify a list of servers in the $Serverlist or either one by one , ie $serverlist=’sql1′, ‘sql2’,
This is awesome but….
Do you know why it restarts the SQL servers so many times?
Tried this in a test environment – it rebooted the SQL servers 4 or 5 times.
Applying one update level up – SQL 2016 SP2 CU10.
This is too many times, once is enough.
In Production 4 or 5 reboots is excessive.
Thanks Ewan,
I have never seen it restart that many times. I would recommend posting that question on the dbatools slack channel (https://dbatools.io/slack/). They will have more knowledge about that than I will.
Many times when I run the script it will detect a pending restart, which it must do because SQL Server will not allow an SP or CU to be installed if there is a pending restart. Then it will also restart SQL after each update it installs. So in a case like that, if I were to apply 1 SP and 1 CU that would be 3 restarts.
Hi Eric. Thanks for the reply !
Yes that is what I would expect as well.
I’ll do more testing.
Like the approach, it’s a good idea, saves a lot of time when patching lots of SQL servers.
Pingback: Mettre à jour et appliquer des updates cumulatives a plusieurs instances en quelques minutes – DBA SQL Server BLOG
It’s a great tool , but just wanted to check if it can update multiple instance(with different version) at same time on same server?
It should be able to, although I’ve never tried it. You may want to ask over on their slack channel and see if anyone there has done it. https://dbatools.io/slack
Great script!
Does this also patch ssis,ssrs, r services etc which are installed on the server?
Can we also use this script to apply security updates / hot fixes? If so, would we need to amend the script in any way?
Thanks Sanj!
This script runs the update installer just like you would if you ran it manually, so it should update the entire SQL install on the server.
I haven’t tried it, but if you do not specify a patch level then it should try to update to highest available level, so if you have any hot fixes downloaded it should use them. That’s just my theory, though. You may want to ask on https://dbatools.io/slack to be sure.
Hi ERIC,
How can we make the script run parallel on multiple servers.
Thank you
Avinash
Hi Avinash,
I would recommend asking that on the dbatools slack channel (https://dbatools.io/slack/)
This is really usefulf.
What could be the $version value for SQL2017 & 19, where there are no SP’s and only CU’s?
I tried using: $version = ‘2017RTMCU25’
How can i patch multiple servers with different sql edition and deploy both servcie pack and CU package in same command/script
Example 2 SQL 2016 needs Servcie Pack 3 and 2 SQL 2017 need CU 26
I believe you can pass in a list of the versions you want to patch to:
$version = ‘2016SP2′,’2017CU26’
You may also be able to omit the version parameter and have it automatically update to the highest version. I don’t remember if it will try to update the highest version available from MS, or if it’s the highest version found on your patch drive.
I would recommend asking this on the dbatools slack channel. (https://dbatools.io/slack/)