Methods for automating SQL CU patching?

Question

For reasons that are not mine to address, we are looking for alternatives to WSUS and SCCM to automate SQL CU patching (particularly on SQL 2017+)

There are some neat Powershell solutions like this one by Adam Bertram which I found via the related post Automate the patching of SQL Severs on Windows

I know there are a plethora of commercial solutions, that Google finds easily. I am looking for solutions we can manage in house.

I am looking for other approaches to consider as well. After extensive searching the only other approach I can even imagine is a SQL job, that runs on server restart (server patching occurs regularly generally with restart) that would go look in a shared folder for the most current CU and apply it if not installed. But I don't find any mention of solutions other the those above.

Are there any methods for automating SQL CU patching, that I have not found examples of?

If SQL job patching is viable, are there any examples?

Answer 1

I would not go with SQL agent running Patch to upgrade sql server. During patching and/or after patching SQL server needs to be restarted along with a reboot of windows machine - depending on the files that are locked during patching.

Alternatives are described in my answer here : https://dba.stackexchange.com/a/105837/8783

Also, dbatools has Update-DbaInstance.ps1 that can perform installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.