Embedded Files
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
Check Patch Levels
Best Practice
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
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
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
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.
IMPORTANT: Be careful of breaching the "Maximum failures in the specified period" (MFSP). For example, if the MFSP is set to 3 and the period is set to 6 then more than 3 "failures" will prevent automatic failover from happening. Note that patching will cause a failure when you restart the SQL Server Engine service. A reboot will also cause a failure. Use the Failover Cluster Manager tool to check/change the values for MFSP and Period.
Live
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
What Can Go Wrong
What Can Go Wrong
Failed Upgrades & Rolling Back: A patch installation can fail midway, leaving the SQL instance in a corrupted state, which may require a complete reinstall.
Driver Conflicts: Some updates, particularly those involving firmware or storage drivers, can cause catastrophic hardware issues.
OS/SQL Mismatch: Installing SQL updates simultaneously with Windows OS updates can cause conflicts.
Startup Failures: A patch might cause the SQL Server service to fail to start, preventing applications from accessing the database.
Cluster Failures: In Availability Groups (HA), improper patching sequences can lead to failure to re-join the cluster.
Common Risks and Scenarios
Common Risks and Scenarios
Failed Updates in Prod: Patching during active SQL agent tasks or when databases are in use can lead to corruption.
Pending Reboots: SQL setup often fails if a previous Windows update is pending a restart.
Corrupted Master Database: In rare cases, patch installation can fail during the upgrade of system databases, causing major downtime.
Best Practices to Prevent Disasters
Best Practices to Prevent Disasters
Test in Non-Prod: Always test patches in a development or QA environment first.
Backups: Take full backups (and VM snapshots) before applying any patches.
Check Services: Ensure SQL Agent jobs are disabled before patching.
Wait for Stability: Don't apply the latest Cumulative Update immediately; wait a few weeks for feedback.
To minimize risks, always review the Microsoft Knowledge Base (KB) article for the specific patch, as it lists known issues.
GDR vs CU
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)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
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
From 22:03
Combining OS Patching and SQL Server Patching
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
Backout
TODO
Bibliography & References
Bibliography & References
https://learn.microsoft.com/en-us/troubleshoot/sql/releases/servicing-models-sql-serverhttps://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-ver15https://kendralittle.com/2016/07/28/should-i-automate-my-windows-updates-for-sql-server-dear-sql-dba-episode-10https://www.kevinrchant.com/2019/01/10/patching-sql-server-on-windows-notes-from-the-fieldhttps://www.reddit.com/r/SQLServer/comments/1bfevtw/do_you_keep_you_sql_installs_on_the_latest_cu
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/https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/availability-groups/troubleshooting-automatic-failover-problems
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
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-ver15https://kendralittle.com/2016/07/28/should-i-automate-my-windows-updates-for-sql-server-dear-sql-dba-episode-10https://www.kevinrchant.com/2019/01/10/patching-sql-server-on-windows-notes-from-the-fieldhttps://www.reddit.com/r/SQLServer/comments/1bfevtw/do_you_keep_you_sql_installs_on_the_latest_cu
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/https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/availability-groups/troubleshooting-automatic-failover-problems
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
Note that Kendra's video above dates back to 2016 and SQL2016. It is still interesting, but things will have moved on somewhat.
Page updated
Google Sites
Report abuse