Keeping your SQL Server instance up to date with the latest service packs and cumulative updates is one of the key components of SQL Server security. In this blog post, we will audit the dbatools command Update-DbaInstance, which can save you hours every month and help keep your environment more secure.
I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Update-DbaInstance is powerful tool to automate the update of one or many SQL server instances without needing to log in.
Scope
Dbatools is a community project full of bright minds. This audit series is more an informational series on useful capabilities and considerations rather than rigorous testing. This is to inspire even greater use of PowerShell and by extension dbatools as I believe it will assist in automating many tasks as a DBA.
1. Script Overview
Script Name: Update-DbaInstance
Author: Kirill Kravtsov
Description: Invokes installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.
2. Testing Environment Setup
I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on this blog post by Jonathan Kehayias.
Download and Install
You are probably familiar with dbatools at this point, but if not now is the perfect time to familiarize yourself with the install and use of the module. On your test environment, you can open PowerShell and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post.
Install-Module Dbatools Import-Module Dbatools
Initial Setup
To save both me and you time, I will skip the test environment steps since they are identical here to my first post.
Instead, I will discuss how important it is to keep your SQL Servers updated. These updates fix bugs, close security holes, and make SQL Server more resilient. However, Microsoft is not perfect and sometimes they introduce new bugs, security holes, and make SQL server less reliable. That is why we usually plan for a 30-day grace period so the market can test the new releases before we recommend them to our clients.
No matter the strategy you use, leaving your instance unpatched is not usually in your best interest. Unless of course you are running SQL server on a completely air-gapped system in your own home lab. Then maybe you could skip it.
3. Execution
Assuming you do need to patch your SQL server instances, I will go through a few scenarios and automations that can help
This command required the use of SQLCredential in my environment. You can comment it out using ‘#’ if you are only focusing on a local instance.
Scenario 1: One-off Instance Update
This scenario is for the one-off update that is common in smaller environments or if patching automation is not implemented yet. This command makes it easy to quantify your patching preferences and kick it off whenever needed. Splatting is in play again to make sure things look nice.
$cred = get-credential
$targetlist = 'labsql1'
$splat = @{
ComputerName = $targetlist
Version = 'CU12'
Path = "\\labshare\SQLBackups\SQL Updates\2022"
Restart = $True
Download = $True
ExtractPath = "\\labshare\SQLBackups\SQL Updates\UpdateHistory"
Confirm = $False
Credential = $cred
}
Update-DbaInstance @splat
This scenario is especially useful when targeting just one version and you have a specific build or CU in mind. We switch to gridview here, since it avoids the many warnings about a stale build index. This is by design, but it is worrisome when you see a sea of orange when trying to update your SQL Server instances.
Results
The process works well. As you can see below, the process gives a simple output useful for tracking and validation. Overall, it took a few minutes with the restart included. I consider this very streamlined especially over manually logging in, copying the installer, starting and manually restarting the host.
Scenario 2: Parallel Update
This scenario is an extension of the above, just showing that it can handle multiple instances at once, making patching night easier than ever, especially with the download flag. Once again, we switch to gridview.
$cred = get-credential
$targetlist = 'labsql1','labsql2','labsql3'
$splat = @{
ComputerName = $targetlist
Version = 'CU13'
Path = "\\labshare\SQLBackups\SQL Updates\2022"
Restart = $True
Download = $True
ExtractPath = "\\labshare\SQLBackups\SQL Updates\UpdateHistory"
Confirm = $False
Credential = $cred
}
Update-DbaInstance @splat | Out-GridView
Instead of version you could have used the KB flag if you had a very specific build in mind, but the documentation is very straightforward for that scenario.
Results
One of the ideal scenarios for this tool is to point it at multiple instances and have them all update at the same time to a target version. This saves you time and energy and provides useful tracking output for you.
One thing to note here is that the restart argument proved to be rather finicky in whether it functioned. It did work on the one off scenario, but not on this parallel target run. However, in earlier testing on my own it did work on a parallel target run. If you end up using this script in your own environment, be sure to account for this. If it were me, I would add the restart-computer command to the end of my script, I would inform that command with any instances that have a restarted value of “False”.
Scenario 3: Scheduled Update of a Set of Instances
The next step for those without a specific build in mind likely would be a scheduled run to update a specific set of instances to the most recent release. We are limiting the targets to just LabSQL1&2, so that we can use LabSQL3 with the next scenario. The script loses just 1 argument to make it work for this scenario, the version flag. One consideration, is that the “stale index” previously mentioned is updated on the DBatools side about once a month on average, based on commit history. For more control of your patching, you may be interested in Scenario 4.
$cred = get-credential
$targetlist = 'labsql1','labsql2'
$splat = @{
ComputerName = $targetlist
Path = "\\labshare\SQLBackups\SQL Updates\2022"
Restart = $True
Download = $True
ExtractPath = "\\labshare\SQLBackups\SQL Updates\UpdateHistory"
Confirm = $False
Credential = $cred
}
Update-DbaInstance @splat | Out-GridView
This is the first script that would yield positive results if put on a schedule. I will not cover it in this post, but I typically use task scheduler for such automation and there are already some great guides on the subject.
Results
Honestly, this scenario is more useful for lower environments where testing the newest release is expected and breaking effects are good to see. This scenario’s script functions as expected and updated the two target instances to the latest target in the index. The latest in the index and the actual latest happened to be the same but as described above that may not always be the case due to the DBAtools build index only being updated around once a month. Also note that for this run, the restart argument did not function as I expected it to, meaning to restart before if needed (which it did in my estimation because I had just applied CU13 without restarting) and then after the CU19 installed, which is best practice.
Scenario 4: Manually Download and Update
I wanted this scenario to be more impressive, hoping the script could look through my chosen directory and get the latest version available. However, I had no such luck. Still, I think it is important to point out that it is possible to download the updates yourself rather than relying on the download argument and the process will work the same. I downloaded the CU16 update and changed the argument from CU to KB since I hadn’t shown that yet.
$cred = get-credential
$targetlist = 'labsql3'
$splat = @{
ComputerName = $targetlist
Path = "\\labshare\SQLBackups\SQL Updates\2022"
KB = 5048033
Restart = $True
ExtractPath = "\\labshare\SQLBackups\SQL Updates\UpdateHistory"
Confirm = $False
Credential = $cred
}
Update-DbaInstance @splat | Out-GridView
Results
This scenario is the most likely for production environments. Having the DBA manage the update installer download and the manually change the script with the new expected target KB or CU is probably for the best, security wise. Note that the restart flag did work as expected, restarting before the update was applied due to a pending restart as well as after. This may be an issue with my environment, but at the very least I would take note and possibly add the catch and restart-computer command to the end of any automation I build in the future.
These four scenarios cover the basics and should be a good jumping off point into the other examples in dbatools documentation if you have a more unique situation.
Scenario 5: The Scenarios that Didn’t Happen
There were a couple of scenarios I would have loved to share. Unfortunately, they didn’t work with my current level of understanding with DBAtools and this command, or aren’t possible with the command as written. The two ideal scenarios I wished I could have made were 1) to be able to get the latest over a certain time age – for example the latest build over 30 days old. Or 2) to be able to pass a full updater path to the command so that I can load an installer into a versioned directory and build a script to get the newest installer in that directory to patch with.
I assume there would be many technical challenges on the DBAtools team side. However it does limit me from using it in the way I think many others would as well. To be fair, test-dbabuild is close in allowing the maxbehind argument which would give various versions of n-x, but the time is not apart of that since we typically recommend the latest as long as it is over 30 days old in most cases.
Warnings and Caveats
The warning here will spread across all of the commands we audit as well as anytime you use PowerShell. This script updates an instance – this process can have an extreme effect on your instances – even bringing them down completely with some underlying conditions being true. I highly recommend if you use this command that you are present for any time it targets production instances, and even dev instances. The likelihood of something going wrong is low, but farther from zero than we’d like. I would take extreme care that you test this command in a lower environment first – as always but especially this time. And be sure your lower environment is a copy of your production environment as close as possible to avoid any surprises due to version, edition, or build differences.
Conclusion
Update-DbaInstance is a very powerful tool, especially since updating instance should be a relatively common task. This is likely one of the more dangerous scripts I have covered due to the fact that if a patch goes wrong, delivered by this command or not, it can take quite a bit of time to get an instance back up and running. For the most part, this command will be a tool that causes significant time savings as it simplifies a task that only grows with the size of the environment. This tool can save hours per month and keep your environment more secure. Take some time this week in a testing environment and see how easy it is to use. I promise you will feel the draw of working it into your normal SQL update process especially if you are still patching manually!
