Updating SQL Server with dbatools v1.0 Update-DbaInstance
I am super excited that the great folks over at dbatools.io are releasing version 1.0. It’s insane to think they have over 550 commands now!
So, on this momentous day of releasing dbatools version 1.0 I am releasing blog post 1, I have contributed to dbatools a little over the last 12 months or so and I literally use it every day as a DBA so I figured the least I could do right now, is share some of the ways I use dbatools, so in this post we will talk about how I update SQL Server using dbatools command Update-DbaInstance.
So the manual way is to copy the CU onto each node and run it, I could write some PowerShell to try and partly automate it but I don’t have time for that. Enter Update-DbaInstance – the nice folks at dbatools have already written the PowerShell, and it’s smart, very smart! You can specify more than 1 node and it will do some crazy parallelism and update all the nodes at once!
Let’s see an example, we have SQL 2016 SP2 with no CUs installed.
And I have dropped 2016 SP2 CU6 into a shared folder.
[crayon]Update-DbaInstance -ComputerName laptop -Path ‘\\laptop\updates’ -Verbose -WhatIf[/crayon]
Running the command with -WhatIf we see what Update-DbaInstance proposes to do.
What if: Performing the operation "Update 2016 to SP2CU6 (KB4488536)" on target "laptop".
So let’s let it run it again without -WhatIf
[crayon]Update-DbaInstance -ComputerName laptop -Path ‘\\laptop\updates’ -Verbose[/crayon]
I didn’t tell it to install without checking first so it asks me. If you wanted it to go ahead without checking then set -Confirm:$false. Also, I didn’t give it permission to reboot so it won’t do that either, if a pending reboot is required before the install it will fail and let you know it can’t install. If you’re happy for it to reboot the node then set the flag -Reboot.
Confirm
Are you sure you want to perform this action?
Performing the operation "Update 2016 to SP2CU6 (KB4488536)" on target "laptop".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "Yes"):
So after a few minutes we get the output!
ComputerName : laptop
MajorVersion : 2016
TargetLevel : SP2CU6
KB : 4488536
Successful : True
Restarted : False
InstanceName :
Installer : \laptop\updates\sqlserver2016-kb4488536-x64_d02797c06ff36cde40c9cbd812946e0ecb59917c.exe
Notes : {Restart is required for computer laptop to finish the installation of SQL2016SP2CU6}
So here are some things you should know.
- If you have more than 1 instance on the server it’s going to look for updates for them all in the folder you specify, if it finds them it will want to apply them, so consider separating your updates into folders if say you only want to update SQL 2016 and not 2017. Or specify an instance instead of a computer.
- To install the update, it copies it from the share you specify to the largest drive on the server, you can change this behavior and specify a location e.g. -ExtractPath ‘c:\temp’ (new in v 1.0!)
- -Throttle, by default this is set to 50 for parallel updating, you can lower or higher this value.
- I always have the -Verbose on for this command, I like to know exactly where it is up to.
- This is a high impact command, in reality, you want -Restart allowed or it’s going to fail or it’s going to finish and be incomplete.
And that’s it! Any questions then check out the docs/code or hit us up on slack: https://dbatools.io/slack/
Check out these other blogs!
Want to know more about dbatools? Check out some of these posts
dbatools 1.0 – the tools to break down the barriers – Shane O’Neill
dbatools 1.0 is here and why you should care – Ben Miller
dbatools 1.0 and beyond – Joshua Corrick
dbatools 1.0 – Dusty R
Your DBA Toolbox Just Got a Refresh – dbatools v1.0 is Officially Available!!! – Garry Bargsley
dbatools v1.0? It’s available – Check it out!