How to patch SQL Server?

on

 Having a plan in place before patching is a good idea, as it eliminates the need to think about what to do when the patching time arrives. It ensures a smooth and well-organized patching process.

My plan:

  • Download the latest update of SQL Server from Microsoft's website.
  • Download the latest version of SQL Server Management Studio (SSMS).
  • Verify that all needed SQL Server services are set to automatic Start Mode. If they are not configured as such, they won't start after reboots. It would be advisable to capture a screenshot for reference.
  • If any jobs are currently running, please wait until they are finished before proceeding with any further actions.
  • If the backup, index maintenance, integrity check, or user jobs are scheduled to run soon, it's best to disable them temporarily to avoid any issues.
  • Before proceeding with the installation, ensure to back up all critical databases, including system databases, to safeguard against any data loss or potential issues.
  • Inform the relevant teams that SQL Server patching is started.
  • Do not stop SQL Server, patching will do everything for you.
  • Reboot Windows server.
  • Install the update.
  • Reboot OS.
  • Verify SQL Server ProductVersion with the script provided below.
  • Check the SQL Server Configuration Manager to ensure that all the required SQL Server services are up and running.
  • Check the SQL Server Error log and Event Viewer for any errors.
  • If any monitoring tools are used, please inspect them for any potential issues.
  • If an email profile is being used, conduct a test by sending an email to yourself to ensure its functionality.
  • Inform the relevant teams that SQL Server patching is complete, and they can now begin testing their applications.
  • Enable all SQL jobs that were disabled before installation.
  • Install SQL Server Management Studio (SSMS).

Scrip to verify SQL Server build number, etc:
SELECT
SERVERPROPERTY('ServerName')AS ServerInstance,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '16%' THEN 'SQL2022'
ELSE 'unknown'
END AS MajorVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductUpdateLevel') AS 'CU_number',
SERVERPROPERTY('ProductUpdateReference') AS 'KB',
SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'Last_update_time'

 Script to disable/enable jobs:

DECLARE @enabled nvarchar(2)
SET @enabled = 0 -- 0 - to disable, 1 - to enable
 
SELECT
       'EXEC dbo.sp_update_job @job_name = N'''+[name]+''', @enabled = '+ @enabled +''
FROM
       msdb..sysjobs
WHERE
       [enabled] = 1

Comments

Post a Comment