Comprehensive Guide to SQL Server Patching: Standalone, AG, FCI,
Mirroring, SSIS, SSAS, and SSRS
Pre-Patching Steps (Common for All)
1. Plan and Communicate:
o Identify downtime requirements.
o Notify stakeholders and get the necessary approvals.
2. Backup:
o Backup system and user databases.
o For SSRS: Backup the encryption keys via Reporting Services Configuration
Manager.
o Save SSIS packages and SSAS configurations.
3. Check Server State: SELECT @@VERSION;
4. Validate Service Health:
o Ensure all services are running without issues (Server, SSIS, SSAS, SSRS).
o Validate replication health (AG, mirroring).
5. Download the Patch:
o Obtain the required Service Pack (SP) or Cumulative Update (CU) from Microsoft's
official site.
6. Verify System Resources:
o Confirm disk space availability.
o Check the OS compatibility with the patch.
7. Disable Monitoring Alerts:
o Temporarily pause monitoring tools to avoid false alarms.
8. Validate Application Readiness:
o Confirm applications are ready for downtime and failovers.
Standalone Server Patching
1. Run Patch Installer:
o Execute the patch executable with administrative privileges.
o Follow the installation wizard to apply the update.
2. Restart Services:
o Reboot the Server post-installation.
3. Post-Validation:
o Check version: SELECT @@VERSION;
o Review error logs for any issues.
1
Comprehensive Guide to SQL Server Patching: Standalone, AG, FCI,
Mirroring, SSIS, SSAS, and SSRS
Always On Availability Groups (AG) Patching with Switchover
1. Pre-Patch Preparation:
o Verify AG health:
SELECT * FROM sys.dm_hadr_database_replica_states WHERE
synchronization_health_desc = 'HEALTHY';
2. Perform Switchover:
o Failover AG to a secondary replica:
ALTER AVAILABILITY GROUP [YourAGName] FAILOVER;
3. Patch Former Primary:
o Apply the patch on the old primary (now secondary) and reboot the server.
4. Patch Remaining Replicas:
o Sequentially patch other secondary replicas and reboot the servers.
5. Failback (Optional):
o Fail back to the original primary if required.
6. Post-Validation:
o Verify AG synchronization and health:
SELECT * FROM sys.dm_hadr_availability_replica_states;
Failover Cluster Instance (FCI) Patching
1. Pre-Patch Preparation:
o Validate cluster health via Failover Cluster Manager.
o Move Server resource group to a node.
2. Patch Passive Node:
o Apply the patch on the passive node first and reboot the Node.
3. Failover to Patched Node:
o Move the resource group to the patched node.
4. Patch Former Active Node:
o Apply the patch on the former active node and reboot the Node.
5. Post-Validation:
o Validate cluster health and Server availability.
2
Comprehensive Guide to SQL Server Patching: Standalone, AG, FCI,
Mirroring, SSIS, SSAS, and SSRS
Database Mirroring Patching
1. Patch Mirror Server First:
o Directly apply the patch on the mirror server without stopping services.
o Once the patch is applied, reboot the mirror server to complete the installation.
2. Failover to Mirror:(Optional)
o Perform a manual failover:
ALTER DATABASE [YourDB] SET PARTNER FAILOVER.
3. Patch Former Principal (If 2nd step followed):
o Patch the principal server (now the mirror).
4. Post-Validation:
o Check mirroring status:
SELECT DB_NAME(database_id) AS DatabaseName, mirroring_state_desc
FROM sys.database_mirroring;
SSIS, SSAS, and SSRS Patching
1. SSIS:
o Stop ongoing package executions.
o Apply the patch and restart services.
o Test SSIS package execution post-patch.
2. SSAS:
o Terminate active cube processing.
o Apply the patch.
o Test cube processing and query execution.
3. SSRS:
o Backup encryption keys.
o Apply the patch and restart services.
o Test report execution post-patch.
3
Comprehensive Guide to SQL Server Patching: Standalone, AG, FCI,
Mirroring, SSIS, SSAS, and SSRS
Post-Patching Steps (Common for All)
1. Check Version: SELECT @@VERSION;
2. Validate Database Health:
o Ensure all databases are online.
3. Test Application Connectivity:
o Validate connections from critical applications.
4. Review Logs:
o Check Server logs, Windows Event Viewer, and service-specific logs (SSIS, SSRS,
SSAS).
5. Enable Monitoring Alerts:
o Reactivate alerts in monitoring tools.
6. Communicate Completion:
o Notify stakeholders that patching is complete.
This process ensures minimal downtime and covers all major Server configurations and services. Let
me know if you need scripts or assistance with any specific scenario!
