MSSQL Patch

Starting with SQL Server 2017, no Service Packs will be released. Microsoft now recommend ongoing, proactive installation of CU’s as they become available. You should plan to install a CU with the same level of confidence you plan to install SP's (Service Packs) as they are released. This is because CU’s are certified and tested to the level of SP’s

Check Patch Levels

Database-Key-Dates

Best Practice

Sandbox

Sandbox (First Test)

Sandbox (Backout Test)

Sandbox (Second Test)

Dev/Test

DR/RO Replicas/HA

You still need a viable backup though... rolling patching isn't going to protect you from data corruption (it's likely to get replicated to other replicas before you notice it).

Live

Don't patch all Live servers in the same window... what would you do if you needed to backout the patch from multiple servers?

GDR vs CU

In SQL2017+ GDRs will use specific CUs as a baseline.

i.e. apply a CU, apply a GDR for that CU, later apply the next CU (which will include the GDR)
A baseline can be an RTM or SP release. If you switch from GDR to CU you cannot switch back (unless you upgrade to another baseline release).

Automation

Be aware of what might be happening on your SQL Server during your patching window. e.g avoid backup windows, scheduled checkdb windows. 

Also be aware of the impact on Always On Availability Groups if the scheduled patch performs a reboot.

Be sure of your backout plan. If the patch automation has unexpected consequences, what will you do?

Potential Automation Tools

(These are not recommendations; just tools I am aware of)
From 22:03

Combining OS Patching and SQL Server Patching

"You can't first install the OS updates and then the SQL Server update without a reboot in between. SQL Server Setup has a check that raises a red flag if there are pending reboots. (That is, pending file renames.) So if you are looking into to reduce the number of reboots, you need to try with installing the SQL Server update before the Windows update."  Erland Sommarskog (1)

However...

"There is unlikely to be any prerequisite for a Windows update that requires specific SQL Server updates to be applied because not all Windows Servers run SQL Server, however, it is possible that you may need a specific Windows update for a given SQL Server update (none spring to mind but it is conceivable). So given this, applying Windows Updates first is probably your best bet.

NOTE: Windows updates often set the restart pending flags and this can cause SQL Server update installers to fail the prerequisite checks. You need to orchestrate your patching process to handle restarts between applying patches if required."  HandyD (2)

Backout

TODO

Bibliography & References

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/latest-updates-for-microsoft-sql-server?view=sql-server-ver15https://buildnumbers.wordpress.com/sqlserver/https://sqlserverupdates.com/https://www.brentozar.com/archive/2015/05/announcing-sqlserverupdates-com/https://sqlperformance.com/latest-buildshttps://sqlperformance.com/latest-builds/sql-server-management-studiohttps://SQLServerBuilds.blogspot.com
https://www.dbta.com/Columns/SQL-Server-Drill-Down/Keep-Up-With-the-Latest-SQL-Server-Updates-141254.asphttps://www.brentozar.com/archive/2020/08/sql-server-cumulative-update-documentation-is-going-downhill-fast/https://twitter.com/ChrisAVWood/status/1291104967701430277https://docs.microsoft.com/en-gb/archive/blogs/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ismhttps://docs.microsoft.com/en-gb/archive/blogs/psssql/sql-server-2012-setup-just-got-smarterhttps://littlekendra.com/2016/04/28/required-testing-for-installing-sql-server-cumulative-updates-and-service-packs/https://learn.microsoft.com/en-us/lifecycle/policies/fixed
Backout / Uninstallhttps://www.sanssql.com/2012/10/un-installing-service-pack-for-sql.htmlhttps://learn.microsoft.com/en-us/answers/questions/138345/how-to-rollback-patch-on-sql-server-2016https://dbtut.com/index.php/2019/05/13/how-to-uninstall-sql-server-service-pack-or-cumulative-update/https://dba.stackexchange.com/questions/254154/windows-server-and-sql-server-updates-and-patching-and-how-to-rollback-revert
Check Patch Levelshttps://www.mssqltips.com/sqlservertip/5408/check-current-patch-levels-for-all-sql-servers-in-environment/
Best Practicehttps://www.tek-tools.com/security/sql-server-patching-best-practiceshttps://www.brentozar.com/archive/2021/06/how-to-patch-sql-server/https://www.sqlnethub.com/blog/how-to-patch-a-standalone-sql-server-instance/https://www.brentozar.com/archive/2015/02/patching-sql-server-availability-groups/https://www.sqlshack.com/apply-sql-server-patches-or-cumulative-updates-in-sql-server-always-on-availability-groups/https://glennsqlperformance.com/2021/12/12/why-should-you-patch-sql-server/(1) https://learn.microsoft.com/en-us/answers/questions/1458795/sql-server-cumulative-updates-best-practice(2) https://dba.stackexchange.com/questions/254154/windows-server-and-sql-server-updates-and-patching-and-how-to-rollback-reverthttps://learn.microsoft.com/en-us/sql/database-engine/install-windows/view-and-read-sql-server-setup-log-files?view=sql-server-ver15
Automationhttps://flxsql.com/downloading-latest-sql-server-updates/https://docs.dbatools.io/#Update-DbaInstancehttps://docs.dbatools.io/#Get-DbaKbUpdatehttps://nvarscar.wordpress.com/2018/12/30/automating-sql-server-patching/https://desertdba.com/how-i-applied-13-cumulative-updates-in-12-minutes/https://littlekendra.com/2016/07/28/should-i-automate-my-windows-updates-for-sql-server-dear-sql-dba-episode-10/https://community.bmc.com/s/question/0D53n00007bGQYxCAO/database-patching-compatibility-matrix-with-tssa-2002
Testinghttps://littlekendra.com/2016/04/28/required-testing-for-installing-sql-server-cumulative-updates-and-service-packs/http://michaeljswart.com/2014/01/generating-concurrent-activity/
AlwaysOnhttps://dba.stackexchange.com/questions/248866/sql-server-always-on-availability-groups-patching https://medium.com/tech-at-nordstrom/successfully-upgrading-both-windows-sql-server-without-downtime-e1fde448b18bhttps://www.sqlshack.com/apply-sql-server-patches-or-cumulative-updates-in-sql-server-always-on-availability-groups/
GDR vs CUhttps://www.sqltattoo.com/blog/2021/01/sql-server-patching-gdr-vs-cu/https://dba.stackexchange.com/questions/298483/on-what-basis-do-we-decide-whether-to-use-gdr-or-cu
SQL2019https://sqlperformance.com/latest-builds/sql-server-2019https://techcommunity.microsoft.com/t5/sql-server-blog/cumulative-update-18-for-sql-server-2019-rtm/ba-p/3640393https://support.microsoft.com/en-us/topic/kb5017593-cumulative-update-18-for-sql-server-2019-5fa00c36-edeb-446c-94e3-c4882b7526bchttps://www.brentozar.com/archive/2021/02/whats-new-undocumented-in-sql-server-2019-cumulative-update-9/
SQL2017https://sqlperformance.com/latest-builds/sql-server-2017
SQL2016https://sqlserverupdates.com/sql-server-2016-updates/https://sqlperformance.com/latest-builds/sql-server-2016https://learn.microsoft.com/en-us/answers/questions/563527/when-will-the-new-cu-for-ms-sql-server-2016-sp2-be.htmlhttps://learn.microsoft.com/en-us/lifecycle/products/sql-server-2016https://support.microsoft.com/en-us/topic/kb5003279-sql-server-2016-service-pack-3-release-information-46ab9543-5cf9-464d-bd63-796279591c31https://learn.microsoft.com/en-US/troubleshoot/sql/releases/sqlserver-2016/servicepack3
SQL2014https://sqlperformance.com/latest-builds/sql-server-2014
SQL2012https://sqlperformance.com/latest-builds/sql-server-2012