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
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-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/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-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/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
Page updated
Google Sites
Report abuse