Automating Windows and SQL Updates with Powershell (T-SQL Tuesday #130 – Automate Your Stress Away)

Elizabeth Noble (b|t) hosts this month series of blog posts with the title „Automate Your Stress Away“.
In case you are new to T-SQL Tuesday this is the monthly blog party started by Adam Machanic (b|t) and now hosted by Steve Jones (b|t). 

You can read more about the invite by clicking on the T-SQL Tuesday logo.

I’ll write about my struggle with the Windows and SQL Updates on my SQL Server.

We use WSUS as part of our deploying strategy for Updates, because we need control about what Updates are going to be shipped to our servers. So we ship Updates to some chosen Server before we ship them to all.

Next important part is when to install updates. As most of you know, during installation of SQL Server Updates, also with other Windows Updates, affected services will be restarted. Because of that, installation is not possible during working hours.

With Windows Server 2016 the update times can be adjusted, but not the day.

So we decided to manage all by ourselves.
We created a job that runs on two of the sundays of the month at 2 o’clock in the morning. During this job, the updates were fetched from the WSUS server, installed and the machine will be rebooted afterwards, if neccessary.
We started with one time per month but from time to time Updates didn’t get installed and it needs a second try. So we changed to two times a month.

This is the PowerShell Script we use. As I’m not an expert any help or hint for doing better is really appreciated.

# This is the modul we use
# Install-Module PSWindowsUpdate -force

# Here we are logging what Updates got installed.
get-date -Format u >> c:\WUInstall.log

Get-WUInstall -Install -AcceptAll -verbose >> c:\WUInstall.log

# most of the time when Updates are installed, a restart is neccessary;
# when restart is needed send Email and restart.

$reboot=Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending"
if ($reboot -eq $true)
{

   $subject='Windows Updates on server: <servername>'
   $sendFrom=<Mail From>
   $sendTo=<Mail to>
   $smtpServer=<SMTP Server>
   
   Send-MailMessage -From $sendFrom -To $sendTo -Subject  $subject -SmtpServer $smtpServer -Attachments 'c:\WUInstall.log'

    Restart-Computer -Force
}

Then we use the task planner of windows to start the script at the time described above.

One Problem we are struggling with in the moment is that the task planner also starts the update on saturdays at the same weekends we want it to run on sundays. We have no idea what’s going on there.

Thanks for reading,
Volker

Credits for some parts of the script go out to Eelco Drost and his article https://eelcodrost.com/2019/10/16/patching-sql-server-ag-using-sccm-and-powershell/
Thanks for sharing!