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
Best Practice
Read the list of issues fixed.
Don't patch SQL Server on the same day as OS or Application patches (to make it easier to identify the root cause in the event of issues)
Sandbox
Sandbox (First Test)
Make sure you have a viable backup of ALL databases
Disable SQL Agent
Apply the patch to a DBA sandbox server
Restart the SQL Instance
Enable SQL Agent
Run sandbox test jobs
Check the SQL Server Error Log
Sandbox (Backout Test)
Disable SQL Agent
Test backout of the patch
Restart the SQL Instance
Enable SQL Agent
Run sandbox test jobs
Check the SQL Server Error Log
Sandbox (Second Test)
Disable SQL Agent
Reapply the patch
Restart the SQL Instance
Enable SQL Agent
Run sandbox test jobs
Check the SQL Server Error Log
Dev/Test
Agree an outage window.
Be sure all dev/test OS/Database/Application versions are close enough to live to be meaningful.
Identify who is going to run (thorough) tests on each dev/test server
Make sure you have a viable backup of ALL databases
Disable SQL Agent
Apply the patch to dev/test server
Restart the SQL Instance
Enable SQL Agent
Handover for testing
Check the SQL Server Error Log
DR/RO Replicas/HA
Rolling patch application is a good idea.. patch a Replica, switchover to it, only patch your original server when you're sure you don't need to rollback.
Live
Agree an outage window.
Identify who is going to run (quick) tests on each live server
Make sure you have a viable backup of ALL databases
Disable SQL Agent
Apply the patch to the live server
Restart the SQL Instance
Enable SQL Agent
Handover for testing
Check the SQL Server Error Log
GDR vs CU
GDR updates – cumulatively only contain security updates for the given baseline.
CU updates – cumulatively contain all functional fixes and security updates for the given baseline.
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)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)ManageEngine Endpoint Central
BMC TrueSight Server Automation (TSSA)
SolarWinds Patch Manager
DBAtools (PowerShell)
SSCM
Tanium
Backout
TODO
Bibliography
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