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

Backout

TODO

Bibliography

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/
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/
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-e1fde448b18b
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