Database Management SystemsMaintenance Tasks

Database Maintenance Tasks

LevelAdvanced

Duration90 mins

TopicMaintenance Tasks

4 / 5

Patching

The Balancing Act of Database Patching

Database patching is one of the most anxiety-inducing maintenance tasks. On one side, unpatched systems are vulnerable to security exploits, bugs, and performance issues. On the other, patches can introduce new problems, require downtime, and in worst cases, cause data corruption or loss.

The stakes are high. A failed patch can bring down production systems for hours or days. But an unpatched system is a ticking time bomb—eventually, a security vulnerability will be exploited or a known bug will cause data loss.

Successful database patching requires careful planning, rigorous testing, and well-rehearsed execution.

What You Will Learn

By the end of this page, you will understand the types of database patches, develop comprehensive patch planning and testing strategies, master rollback procedures for failed patches, implement zero-downtime patching where possible, and establish production patching policies.

Understanding Database Patches

Database vendors release various types of patches, each with different risk profiles, urgency levels, and installation requirements. Understanding these categories helps prioritize and plan patching activities.

Types of Database Patches
Patch Type	Description	Frequency	Risk Level	Urgency
Security Patch (Critical)	Fixes known security vulnerabilities	As needed (often monthly)	Medium-High	HIGH - Apply ASAP
Cumulative Update (CU)	Bundle of bug fixes and improvements	Monthly to quarterly	Medium	Medium - Within 1-3 months
Service Pack (SP)	Major update with many fixes and potentially new features	Annually or less	Medium-High	Medium - Within 6 months
Major Version Upgrade	New database version with significant changes	Every 1-3 years	High	Low - Plan extensively
Hotfix	Targeted fix for specific critical issue	As needed	Low-Medium	Varies - Based on impact
Driver/Client Update	Updates to ODBC, JDBC, client libraries	Quarterly	Low	Low - Coordinate with apps

Vendor-specific terminology:

Microsoft SQL Server: CU (Cumulative Update), GDR (Security), Service Pack
Oracle: PSU (Patch Set Update), RU (Release Update), Critical Patch Update
PostgreSQL: Minor version (e.g., 15.1 → 15.2), Major version (e.g., 14 → 15)
MySQL: Point release (e.g., 8.0.31 → 8.0.32), Major version upgrade

The Patch Lag Dilemma

Many organizations delay patches due to testing requirements and downtime constraints. However, once a security vulnerability is publicly disclosed, attackers actively exploit it. The window between disclosure and exploitation is shrinking—sometimes to days or hours. Balance testing with urgency.

Comprehensive Patch Planning

Successful patching starts long before the maintenance window. Thorough planning identifies risks, ensures resources are available, and prepares for contingencies.

Patch Planning Checklist

•Review Release Notes — Read all documentation. Understand what the patch fixes, what it changes, and any known issues or prerequisites.
•Identify Dependencies — Check for required OS patches, prerequisite database patches, or application compatibility requirements.
•Assess Impact — Determine which features are affected. Will the patch require application changes? Database restarts? Configuration changes?
•Estimate Downtime — Based on patch type and environment size, estimate how long the maintenance will take. Add buffer for contingencies.
•Plan Rollback Procedure — Document exactly how to revert if the patch fails. Test the rollback procedure in non-production.
•Schedule Maintenance Window — Coordinate with stakeholders, application teams, and dependent systems. Communicate schedule widely.
•Prepare Communication Plan — Define who to notify at each stage: start, completion, issues, and rollback if needed.
•Verify Backup Status — Ensure recent full backups exist and have been tested for restorability.

Patching Environment Progression
Environment	Purpose	Timing	Success Criteria
Dev/Sandbox	Initial compatibility testing	Immediately after release	Patch installs successfully
QA/Test	Full regression testing	After dev validation	All tests pass, no regressions
Staging/Pre-Prod	Production-like validation	After QA sign-off	Performance matches production
Production (Non-Critical)	Real-world validation	After staging success	No incidents in 24-48 hours
Production (Critical)	Final production systems	After non-critical period	All systems stable

The Canary Approach

For organizations with multiple production database servers, use a 'canary' approach: patch one server first, monitor for 24-48 hours, then patch the rest. This catches production-only issues before they affect all systems.

Patch Testing Strategies

Testing is the most critical phase of patching. Inadequate testing is the leading cause of patch-related incidents. A comprehensive testing strategy validates functionality, performance, and compatibility.

Functional Testing

•Run automated application test suites
•Test critical business transactions end-to-end
•Verify all database features used by applications
•Test backup and restore procedures
•Validate replication and high availability
•Check all scheduled jobs execute correctly

Performance Testing

•Compare query execution times before/after
•Run standardized benchmark workloads
•Test under peak load conditions
•Verify query plans haven't regressed
•Check memory and CPU utilization patterns
•Measure throughput and latency metrics

patch_testing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- SQL Server: Pre/Post patch validation queries
 
-- 1. Capture baseline before patching
-- Save query plan hashes for critical queries
SELECT 
    qs.query_hash,
    qs.query_plan_hash,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
    SUBSTRING(st.text, 1, 200) AS query_sample
INTO #BaselineQueryPerf
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.execution_count DESC;
 
-- 2. After patching, compare performance
-- Clear plan cache to force recompilation
DBCC FREEPROCCACHE;
 
-- Run representative workload, then compare:
SELECT 
    b.query_sample,
    b.avg_elapsed_us AS before_us,
    a.avg_elapsed_us AS after_us,
    (a.avg_elapsed_us - b.avg_elapsed_us) * 100.0 / 
        NULLIF(b.avg_elapsed_us, 0) AS change_pct
FROM #BaselineQueryPerf b
JOIN (
    SELECT 
        query_hash,
        total_elapsed_time / execution_count AS avg_elapsed_us
    FROM sys.dm_exec_query_stats
) a ON b.query_hash = a.query_hash
WHERE ABS((a.avg_elapsed_us - b.avg_elapsed_us) * 100.0 / 
    NULLIF(b.avg_elapsed_us, 0)) > 20  -- >20% change
ORDER BY change_pct DESC;
 
-- 3. Verify database integrity
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
 
-- 4. Check for orphaned users after upgrade
EXEC sp_change_users_login 'Report';
 
-- 5. Verify linked servers
EXEC sp_testlinkedserver 'LinkedServerName';
 
-- 6. Verify all databases are online
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE state_desc != 'ONLINE';
 
-- 7. Check SQL Agent jobs
SELECT 
    j.name,
    j.enabled,
    jh.run_status,
    jh.run_date,
    jh.message
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id = 0  -- Job outcome
  AND jh.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-1, 112));

The Query Plan Regression Trap

Database patches often include optimizer changes that can cause query plan regressions. A query that ran in 100ms might suddenly take 10 seconds. Always compare critical query performance before and after patching, and have a plan for forcing old plans if needed.

Patch Execution Procedures

When the maintenance window arrives, follow a structured execution procedure. Having a documented runbook reduces errors and ensures consistency across team members.

Patch Execution Runbook

•
Pre-Patch Checklist (T-60 min)
- Verify backup completed successfully
- Confirm rollback procedures are ready
- Notify stakeholders maintenance is starting
- Gather pre-patch baseline metrics
•
Stop Application Traffic (T-10 min)
- Drain connections gracefully
- Stop application servers or redirect traffic
- Kill remaining user sessions if necessary
•
Point-in-Time Backup (T-5 min)
- Take final transaction log backup
- Consider VM snapshot for quick rollback
- Document exact pre-patch state
•
Apply Patch (T-0)
- Execute patch installation
- Monitor for errors during installation
- Document any warnings or issues
•
Post-Patch Verification (T+15 min)
- Verify database services started
- Run post-patch validation queries
- Check logs for errors or warnings
•
Restore Application Traffic (T+30 min)
- Start application servers
- Gradually restore traffic (if possible)
- Monitor application behavior closely
•
Extended Monitoring (T+24 hours)
- Watch for performance regressions
- Monitor error rates and user reports
- Keep rollback option available

patch_execution.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- SQL Server: Patch execution scripts
 
-- 1. Pre-patch: Kill user connections
DECLARE @kill VARCHAR(MAX) = '';
SELECT @kill = @kill + 'KILL ' + CAST(session_id AS VARCHAR) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabase')
  AND session_id != @@SPID;
EXEC (@kill);
 
-- 2. Pre-patch: Set database to single user (optional)
ALTER DATABASE [YourDatabase] SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
 
-- 3. Take tail-log backup
BACKUP LOG [YourDatabase] 
TO DISK = 'D:\Backup\YourDatabase_TailLog.trn'
WITH NORECOVERY;  -- If planning to restore if patch fails
 
-- 4. Apply patch (Windows command line)
-- cd "C:\Patch\SQLServer2019-KB5021125-x64.exe"
-- Setup.exe /QS /Action=Patch /IAcceptSQLServerLicenseTerms
 
-- 5. Post-patch: Verify version
SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion');
SELECT SERVERPROPERTY('ProductLevel');
 
-- 6. Post-patch: Restore multi-user mode
ALTER DATABASE [YourDatabase] SET MULTI_USER;
 
-- 7. Post-patch: Update DBCC CHECKDB for all databases
EXEC sp_MSforeachdb 'DBCC CHECKDB([?]) WITH NO_INFOMSGS';
 
-- 8. Post-patch: Rebuild any indexes if needed
-- (Major version upgrades may benefit from index rebuild)
 
-- 9. Post-patch: Update statistics
EXEC sp_updatestats;

Rollback Procedures and Recovery

Every patch operation must have a documented, tested rollback procedure. When patches fail, you need to recover quickly without losing data. The rollback approach depends on the type of patch and available resources.

Rollback Strategies by Patch Type
Strategy	Best For	Recovery Time	Data Loss Risk	Complexity
VM Snapshot Revert	All patch types, virtualized environments	Minutes	None (if taken at right moment)	Low
Uninstall Patch	Minor patches with uninstaller	Minutes to hours	None	Low-Medium
Database Restore	Major upgrades, complex changes	Hours	Transactions since backup	Medium
Side-by-Side Downgrade	Major version downgrades	Hours	Requires data export/import	High
Parallel Environment Failover	Enterprise HA setups	Minutes	Minimal (replication lag)	High

rollback_procedures.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- SQL Server: Rollback procedures
 
-- OPTION 1: Uninstall cumulative update (if supported)
-- From Windows: Control Panel > Programs > Uninstall
-- Or command line:
-- wusa /uninstall /kb:5021125 /quiet /norestart
 
-- Verify version after uninstall:
SELECT @@VERSION;
 
-- OPTION 2: Restore from backup
 
-- Step 1: Stop the database
ALTER DATABASE [OrdersDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
 
-- Step 2: Restore full backup
RESTORE DATABASE [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Full.bak'
WITH NORECOVERY, REPLACE;
 
-- Step 3: Restore differential (if available)
RESTORE DATABASE [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Diff.bak'
WITH NORECOVERY;
 
-- Step 4: Restore log backups in sequence
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Log1.trn'
WITH NORECOVERY;
 
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Log2.trn'
WITH NORECOVERY;
 
-- Step 5: Restore final log with RECOVERY
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_TailLog.trn'
WITH RECOVERY;
 
-- Step 6: Verify database is online
SELECT name, state_desc FROM sys.databases 
WHERE name = 'OrdersDB';
 
-- OPTION 3: Failover to standby (Always On AG)
-- On secondary replica:
ALTER AVAILABILITY GROUP [AGName] 
FAILOVER;
 
-- Then remove patched node from AG, uninstall patch, re-add

Test Your Rollback Procedure

A rollback procedure you haven't tested is a procedure that might not work. Practice rollbacks in non-production environments. Time them. Document every step. The middle of an outage is not the time to learn your rollback has gaps.

Zero-Downtime Patching Strategies

For systems that cannot tolerate downtime, zero-downtime patching leverages redundancy and failover mechanisms to apply patches without service interruption. These approaches require high-availability architecture.

Zero-Downtime Patching Approaches

•Rolling Upgrade (HA Cluster) — Patch nodes one at a time. Remove node from cluster, patch, validate, return to cluster. Requires n+1 capacity.
•Blue-Green Deployment — Maintain two identical environments. Patch 'blue' while 'green' serves traffic. Switch when ready. Requires 2x infrastructure.
•Streaming Replica Promotion — Promote patched replica to primary. Rebuild old primary as new replica with patched version.
•Always On Availability Groups — Patch secondaries first, failover, patch former primary. Requires careful synchronization.
•Read Replica Rotation — For read-heavy workloads, rotate read replicas out for patching while maintaining read capacity.

zero_downtime_patch.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- SQL Server: Rolling upgrade with Always On AG
 
-- Prerequisites:
-- - Always On Availability Group with 2+ replicas
-- - Automatic failover configured (or manual failover planned)
-- - All replicas synchronized
 
-- PHASE 1: Patch secondary replica(s)
 
-- 1.1 Check current AG status
SELECT 
    ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar 
    ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag 
    ON ar.group_id = ag.group_id;
 
-- 1.2 Suspend data movement on secondary (to prevent lag during patch)
-- Run on PRIMARY:
ALTER DATABASE [OrdersDB] 
SET HADR SUSPEND;
 
-- 1.3 Apply patch to SECONDARY (via Windows/installer)
 
-- 1.4 Verify secondary is healthy after patch
-- Run on SECONDARY:
SELECT @@VERSION;  -- Confirm new version
SELECT state_desc FROM sys.databases WHERE name = 'OrdersDB';
 
-- 1.5 Resume data movement
-- Run on PRIMARY:
ALTER DATABASE [OrdersDB] 
SET HADR RESUME;
 
-- 1.6 Wait for synchronization
SELECT 
    database_name,
    synchronization_state_desc,
    synchronization_health_desc
FROM sys.dm_hadr_database_replica_states;
 
-- PHASE 2: Failover and patch former primary
 
-- 2.1 Planned failover to patched secondary
ALTER AVAILABILITY GROUP [YourAG] FAILOVER;
 
-- 2.2 Verify failover succeeded
SELECT 
    replica_server_name,
    role_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar 
    ON ars.replica_id = ar.replica_id;
 
-- 2.3 Apply patch to former primary (now secondary)
 
-- 2.4 Resume synchronization and verify
 
-- PHASE 3: (Optional) Failback to original primary
ALTER AVAILABILITY GROUP [YourAG] FAILOVER;

Zero-Downtime Has Costs

Zero-downtime patching requires additional infrastructure, more complex procedures, and more testing. For many systems, a planned 30-minute maintenance window is simpler and less risky than complex zero-downtime procedures. Evaluate whether the complexity is justified for your system's availability requirements.

Patching Policies and Governance

Sustainable patching requires organizational policies that define timelines, responsibilities, and exceptions. Without governance, patches are applied inconsistently, creating security risks and technical debt.

Patching Policy Framework
Component	Definition	Example
Patching Cadence	How often patches are evaluated and applied	Monthly patch review, quarterly application
Emergency Patch SLA	Maximum time to apply critical security patches	Critical: 72 hours, High: 7 days, Medium: 30 days
Testing Requirements	Minimum testing before production deployment	Dev → QA → Staging → Canary (48h) → Production
Exception Process	How to document and approve patch deferrals	Risk assessment, executive approval, expiration date
Rollback Authority	Who can authorize rollback and under what conditions	On-call DBA authority for P1 incidents
Documentation Requirements	What must be recorded for each patch	Patch ID, date, systems affected, tester, approver

Patching Governance Best Practices

•Maintain a Patch Inventory — Track all database instances, current versions, and pending patches. Use automation to detect drift.
•Define Risk-Based Timelines — Not all patches are equal. Security patches need faster deployment than feature updates.
•Automate Where Possible — Automated patch detection, testing, and deployment reduces human error and speeds response.
•Regular Exception Review — Deferred patches accumulate risk. Review exceptions monthly and enforce expiration dates.
•Post-Patch Reviews — After each patch cycle, review what went well and what didn't. Continuously improve the process.
•Stakeholder Communication — Keep application teams, security, and management informed of patching status and upcoming windows.

The Exception Trap

It's easy to defer 'just one more patch' due to project pressures. But exceptions accumulate. Before long, you have systems that are years behind on patches, creating major security vulnerabilities and making future upgrades much harder. Enforce exception limits.

Summary: Mastering Database Patching

Database patching is a critical maintenance activity that balances security, stability, and availability. Successful patching requires thorough planning, rigorous testing, careful execution, and well-prepared rollback procedures.

Key Takeaways

•Understand patch types — Security patches need urgent attention. Cumulative updates and service packs can be planned. Major version upgrades require extensive planning.
•Plan thoroughly — Review release notes, identify dependencies, estimate downtime, prepare rollback procedures, and schedule maintenance windows.
•Test rigorously — Progress patches through development, QA, staging, and canary before full production. Validate functionality and performance.
•Execute methodically — Follow documented runbooks. Take baselines before patching. Validate after patching. Monitor for 24-48 hours.
•Prepare for rollback — Every patch operation needs a tested rollback procedure. VM snapshots, database restores, and HA failover are options.
•Consider zero-downtime approaches — For critical systems, rolling upgrades and blue-green deployments can eliminate downtime, but add complexity.
•Establish governance — Define patching cadences, emergency SLAs, testing requirements, and exception processes. Track compliance.

What's next:

Patching keeps software current, but knowledge management keeps teams effective. In the final page of this module, we'll explore Documentation—creating and maintaining the operational documentation that enables consistent, reliable database administration.

Page Complete

You now understand database patching strategy, from planning through execution and rollback. Apply these principles to keep your databases secure and stable while minimizing risk and downtime.

4 / 5

Loading learning content...

Database Management SystemsMaintenance Tasks

Database Maintenance Tasks

LevelAdvanced

Duration90 mins

TopicMaintenance Tasks

4 / 5

Patching

The Balancing Act of Database Patching

Successful database patching requires careful planning, rigorous testing, and well-rehearsed execution.

What You Will Learn

Understanding Database Patches

Types of Database Patches
Patch Type	Description	Frequency	Risk Level	Urgency
Security Patch (Critical)	Fixes known security vulnerabilities	As needed (often monthly)	Medium-High	HIGH - Apply ASAP
Cumulative Update (CU)	Bundle of bug fixes and improvements	Monthly to quarterly	Medium	Medium - Within 1-3 months
Service Pack (SP)	Major update with many fixes and potentially new features	Annually or less	Medium-High	Medium - Within 6 months
Major Version Upgrade	New database version with significant changes	Every 1-3 years	High	Low - Plan extensively
Hotfix	Targeted fix for specific critical issue	As needed	Low-Medium	Varies - Based on impact
Driver/Client Update	Updates to ODBC, JDBC, client libraries	Quarterly	Low	Low - Coordinate with apps

Vendor-specific terminology:

Microsoft SQL Server: CU (Cumulative Update), GDR (Security), Service Pack
Oracle: PSU (Patch Set Update), RU (Release Update), Critical Patch Update
PostgreSQL: Minor version (e.g., 15.1 → 15.2), Major version (e.g., 14 → 15)
MySQL: Point release (e.g., 8.0.31 → 8.0.32), Major version upgrade

The Patch Lag Dilemma

Comprehensive Patch Planning

Successful patching starts long before the maintenance window. Thorough planning identifies risks, ensures resources are available, and prepares for contingencies.

Patch Planning Checklist

•Review Release Notes — Read all documentation. Understand what the patch fixes, what it changes, and any known issues or prerequisites.
•Identify Dependencies — Check for required OS patches, prerequisite database patches, or application compatibility requirements.
•Assess Impact — Determine which features are affected. Will the patch require application changes? Database restarts? Configuration changes?
•Estimate Downtime — Based on patch type and environment size, estimate how long the maintenance will take. Add buffer for contingencies.
•Plan Rollback Procedure — Document exactly how to revert if the patch fails. Test the rollback procedure in non-production.
•Schedule Maintenance Window — Coordinate with stakeholders, application teams, and dependent systems. Communicate schedule widely.
•Prepare Communication Plan — Define who to notify at each stage: start, completion, issues, and rollback if needed.
•Verify Backup Status — Ensure recent full backups exist and have been tested for restorability.

Patching Environment Progression
Environment	Purpose	Timing	Success Criteria
Dev/Sandbox	Initial compatibility testing	Immediately after release	Patch installs successfully
QA/Test	Full regression testing	After dev validation	All tests pass, no regressions
Staging/Pre-Prod	Production-like validation	After QA sign-off	Performance matches production
Production (Non-Critical)	Real-world validation	After staging success	No incidents in 24-48 hours
Production (Critical)	Final production systems	After non-critical period	All systems stable

The Canary Approach

Patch Testing Strategies

Functional Testing

•Run automated application test suites
•Test critical business transactions end-to-end
•Verify all database features used by applications
•Test backup and restore procedures
•Validate replication and high availability
•Check all scheduled jobs execute correctly

Performance Testing

•Compare query execution times before/after
•Run standardized benchmark workloads
•Test under peak load conditions
•Verify query plans haven't regressed
•Check memory and CPU utilization patterns
•Measure throughput and latency metrics

patch_testing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- SQL Server: Pre/Post patch validation queries
 
-- 1. Capture baseline before patching
-- Save query plan hashes for critical queries
SELECT 
    qs.query_hash,
    qs.query_plan_hash,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
    SUBSTRING(st.text, 1, 200) AS query_sample
INTO #BaselineQueryPerf
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.execution_count DESC;
 
-- 2. After patching, compare performance
-- Clear plan cache to force recompilation
DBCC FREEPROCCACHE;
 
-- Run representative workload, then compare:
SELECT 
    b.query_sample,
    b.avg_elapsed_us AS before_us,
    a.avg_elapsed_us AS after_us,
    (a.avg_elapsed_us - b.avg_elapsed_us) * 100.0 / 
        NULLIF(b.avg_elapsed_us, 0) AS change_pct
FROM #BaselineQueryPerf b
JOIN (
    SELECT 
        query_hash,
        total_elapsed_time / execution_count AS avg_elapsed_us
    FROM sys.dm_exec_query_stats
) a ON b.query_hash = a.query_hash
WHERE ABS((a.avg_elapsed_us - b.avg_elapsed_us) * 100.0 / 
    NULLIF(b.avg_elapsed_us, 0)) > 20  -- >20% change
ORDER BY change_pct DESC;
 
-- 3. Verify database integrity
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
 
-- 4. Check for orphaned users after upgrade
EXEC sp_change_users_login 'Report';
 
-- 5. Verify linked servers
EXEC sp_testlinkedserver 'LinkedServerName';
 
-- 6. Verify all databases are online
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE state_desc != 'ONLINE';
 
-- 7. Check SQL Agent jobs
SELECT 
    j.name,
    j.enabled,
    jh.run_status,
    jh.run_date,
    jh.message
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id = 0  -- Job outcome
  AND jh.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-1, 112));

The Query Plan Regression Trap

Patch Execution Procedures

When the maintenance window arrives, follow a structured execution procedure. Having a documented runbook reduces errors and ensures consistency across team members.

Patch Execution Runbook

•
Pre-Patch Checklist (T-60 min)
- Verify backup completed successfully
- Confirm rollback procedures are ready
- Notify stakeholders maintenance is starting
- Gather pre-patch baseline metrics
•
Stop Application Traffic (T-10 min)
- Drain connections gracefully
- Stop application servers or redirect traffic
- Kill remaining user sessions if necessary
•
Point-in-Time Backup (T-5 min)
- Take final transaction log backup
- Consider VM snapshot for quick rollback
- Document exact pre-patch state
•
Apply Patch (T-0)
- Execute patch installation
- Monitor for errors during installation
- Document any warnings or issues
•
Post-Patch Verification (T+15 min)
- Verify database services started
- Run post-patch validation queries
- Check logs for errors or warnings
•
Restore Application Traffic (T+30 min)
- Start application servers
- Gradually restore traffic (if possible)
- Monitor application behavior closely
•
Extended Monitoring (T+24 hours)
- Watch for performance regressions
- Monitor error rates and user reports
- Keep rollback option available

patch_execution.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- SQL Server: Patch execution scripts
 
-- 1. Pre-patch: Kill user connections
DECLARE @kill VARCHAR(MAX) = '';
SELECT @kill = @kill + 'KILL ' + CAST(session_id AS VARCHAR) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabase')
  AND session_id != @@SPID;
EXEC (@kill);
 
-- 2. Pre-patch: Set database to single user (optional)
ALTER DATABASE [YourDatabase] SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
 
-- 3. Take tail-log backup
BACKUP LOG [YourDatabase] 
TO DISK = 'D:\Backup\YourDatabase_TailLog.trn'
WITH NORECOVERY;  -- If planning to restore if patch fails
 
-- 4. Apply patch (Windows command line)
-- cd "C:\Patch\SQLServer2019-KB5021125-x64.exe"
-- Setup.exe /QS /Action=Patch /IAcceptSQLServerLicenseTerms
 
-- 5. Post-patch: Verify version
SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion');
SELECT SERVERPROPERTY('ProductLevel');
 
-- 6. Post-patch: Restore multi-user mode
ALTER DATABASE [YourDatabase] SET MULTI_USER;
 
-- 7. Post-patch: Update DBCC CHECKDB for all databases
EXEC sp_MSforeachdb 'DBCC CHECKDB([?]) WITH NO_INFOMSGS';
 
-- 8. Post-patch: Rebuild any indexes if needed
-- (Major version upgrades may benefit from index rebuild)
 
-- 9. Post-patch: Update statistics
EXEC sp_updatestats;

Rollback Procedures and Recovery

Rollback Strategies by Patch Type
Strategy	Best For	Recovery Time	Data Loss Risk	Complexity
VM Snapshot Revert	All patch types, virtualized environments	Minutes	None (if taken at right moment)	Low
Uninstall Patch	Minor patches with uninstaller	Minutes to hours	None	Low-Medium
Database Restore	Major upgrades, complex changes	Hours	Transactions since backup	Medium
Side-by-Side Downgrade	Major version downgrades	Hours	Requires data export/import	High
Parallel Environment Failover	Enterprise HA setups	Minutes	Minimal (replication lag)	High

rollback_procedures.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- SQL Server: Rollback procedures
 
-- OPTION 1: Uninstall cumulative update (if supported)
-- From Windows: Control Panel > Programs > Uninstall
-- Or command line:
-- wusa /uninstall /kb:5021125 /quiet /norestart
 
-- Verify version after uninstall:
SELECT @@VERSION;
 
-- OPTION 2: Restore from backup
 
-- Step 1: Stop the database
ALTER DATABASE [OrdersDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
 
-- Step 2: Restore full backup
RESTORE DATABASE [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Full.bak'
WITH NORECOVERY, REPLACE;
 
-- Step 3: Restore differential (if available)
RESTORE DATABASE [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Diff.bak'
WITH NORECOVERY;
 
-- Step 4: Restore log backups in sequence
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Log1.trn'
WITH NORECOVERY;
 
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_Log2.trn'
WITH NORECOVERY;
 
-- Step 5: Restore final log with RECOVERY
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backup\OrdersDB_TailLog.trn'
WITH RECOVERY;
 
-- Step 6: Verify database is online
SELECT name, state_desc FROM sys.databases 
WHERE name = 'OrdersDB';
 
-- OPTION 3: Failover to standby (Always On AG)
-- On secondary replica:
ALTER AVAILABILITY GROUP [AGName] 
FAILOVER;
 
-- Then remove patched node from AG, uninstall patch, re-add

Test Your Rollback Procedure

Zero-Downtime Patching Strategies

Zero-Downtime Patching Approaches

•Rolling Upgrade (HA Cluster) — Patch nodes one at a time. Remove node from cluster, patch, validate, return to cluster. Requires n+1 capacity.
•Blue-Green Deployment — Maintain two identical environments. Patch 'blue' while 'green' serves traffic. Switch when ready. Requires 2x infrastructure.
•Streaming Replica Promotion — Promote patched replica to primary. Rebuild old primary as new replica with patched version.
•Always On Availability Groups — Patch secondaries first, failover, patch former primary. Requires careful synchronization.
•Read Replica Rotation — For read-heavy workloads, rotate read replicas out for patching while maintaining read capacity.

zero_downtime_patch.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- SQL Server: Rolling upgrade with Always On AG
 
-- Prerequisites:
-- - Always On Availability Group with 2+ replicas
-- - Automatic failover configured (or manual failover planned)
-- - All replicas synchronized
 
-- PHASE 1: Patch secondary replica(s)
 
-- 1.1 Check current AG status
SELECT 
    ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar 
    ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag 
    ON ar.group_id = ag.group_id;
 
-- 1.2 Suspend data movement on secondary (to prevent lag during patch)
-- Run on PRIMARY:
ALTER DATABASE [OrdersDB] 
SET HADR SUSPEND;
 
-- 1.3 Apply patch to SECONDARY (via Windows/installer)
 
-- 1.4 Verify secondary is healthy after patch
-- Run on SECONDARY:
SELECT @@VERSION;  -- Confirm new version
SELECT state_desc FROM sys.databases WHERE name = 'OrdersDB';
 
-- 1.5 Resume data movement
-- Run on PRIMARY:
ALTER DATABASE [OrdersDB] 
SET HADR RESUME;
 
-- 1.6 Wait for synchronization
SELECT 
    database_name,
    synchronization_state_desc,
    synchronization_health_desc
FROM sys.dm_hadr_database_replica_states;
 
-- PHASE 2: Failover and patch former primary
 
-- 2.1 Planned failover to patched secondary
ALTER AVAILABILITY GROUP [YourAG] FAILOVER;
 
-- 2.2 Verify failover succeeded
SELECT 
    replica_server_name,
    role_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar 
    ON ars.replica_id = ar.replica_id;
 
-- 2.3 Apply patch to former primary (now secondary)
 
-- 2.4 Resume synchronization and verify
 
-- PHASE 3: (Optional) Failback to original primary
ALTER AVAILABILITY GROUP [YourAG] FAILOVER;

Zero-Downtime Has Costs

Patching Policies and Governance

Patching Policy Framework
Component	Definition	Example
Patching Cadence	How often patches are evaluated and applied	Monthly patch review, quarterly application
Emergency Patch SLA	Maximum time to apply critical security patches	Critical: 72 hours, High: 7 days, Medium: 30 days
Testing Requirements	Minimum testing before production deployment	Dev → QA → Staging → Canary (48h) → Production
Exception Process	How to document and approve patch deferrals	Risk assessment, executive approval, expiration date
Rollback Authority	Who can authorize rollback and under what conditions	On-call DBA authority for P1 incidents
Documentation Requirements	What must be recorded for each patch	Patch ID, date, systems affected, tester, approver

Patching Governance Best Practices

•Maintain a Patch Inventory — Track all database instances, current versions, and pending patches. Use automation to detect drift.
•Define Risk-Based Timelines — Not all patches are equal. Security patches need faster deployment than feature updates.
•Automate Where Possible — Automated patch detection, testing, and deployment reduces human error and speeds response.
•Regular Exception Review — Deferred patches accumulate risk. Review exceptions monthly and enforce expiration dates.
•Post-Patch Reviews — After each patch cycle, review what went well and what didn't. Continuously improve the process.
•Stakeholder Communication — Keep application teams, security, and management informed of patching status and upcoming windows.

The Exception Trap

Summary: Mastering Database Patching

Key Takeaways

•Understand patch types — Security patches need urgent attention. Cumulative updates and service packs can be planned. Major version upgrades require extensive planning.
•Plan thoroughly — Review release notes, identify dependencies, estimate downtime, prepare rollback procedures, and schedule maintenance windows.
•Test rigorously — Progress patches through development, QA, staging, and canary before full production. Validate functionality and performance.
•Execute methodically — Follow documented runbooks. Take baselines before patching. Validate after patching. Monitor for 24-48 hours.
•Prepare for rollback — Every patch operation needs a tested rollback procedure. VM snapshots, database restores, and HA failover are options.
•Consider zero-downtime approaches — For critical systems, rolling upgrades and blue-green deployments can eliminate downtime, but add complexity.
•Establish governance — Define patching cadences, emergency SLAs, testing requirements, and exception processes. Track compliance.

What's next:

Page Complete

You now understand database patching strategy, from planning through execution and rollback. Apply these principles to keep your databases secure and stable while minimizing risk and downtime.

4 / 5