In Alwayson Availability Group replicas the SQL server is installed on all the participating replicas and the SQL services will also be in running state on all replicas
Where we have One PRIMARY and multiple SECONDARY replicas depending on your environment.
Here in our environment we have 3 Node HADR Setup , the SQL Servies runs on all 3 replicas and one Replicas is PRIMARY and the other two replicas are SECONDARY ( 1 is Local replica with SYNCHRONOUS Mode and 1 is DR replica with ASYNCHRONOUS Mode).
1)First need to check the current patch Level and target patch Level( N-1 where N is Latest Patch).
For Latest Patch Level which was available in market we can check on SQL server blogs.
2)Don’t apply patches directly to your production environment. First apply it to your lower environments, wait for atleast a week and so then schedule it to apply for Production environment.
3) In that one week the patch will be TESTED in lower environments by Application team
once validation is completed successfully , we can apply it on production environment.
Before applying the Patch to the SQL Instance, follow these steps::
1) Check that we have the most recent full backup for all SYSTEM and USER databases on the PRIMARY Replica. If the size of the user databases is small, take a fresh Full backup for all databases; if the database size is larger, check for the most recent valid FULL backup and then take at least a Differential or Transactional Log backup before beginning the SQL Patches.
2) On Secondary replicas take SYSTEM Databases Backup.
3) Check the AG Dashboard, make sure it shows all GREEN and HEALTHY.
Applying Patch to SQL Instances:
As previously stated, we have a three-node HADR setup ( 2 local replicas with Synchronous Mode and 1 DR with Asynchronous Mode )
1) First we will apply patch to LOCAL replica of Secondary Node
2) To do that we need to change Failover Mode from AUTOMATIC to MANUAL because just to be sure that no automatic failover happens to secondary when we are applying patch incase of any issues on PRIMARY .
3) Also SUSPEND the DATA movement on currently patching secondary replica as if you do it on PRIAMRY it will SUSPEND moving the DATA for all the secondary replicas.
4) Now start applying the Patch on Secondary replica and restart the Server after applying the Patch.
5) Once the secondary replica comes ONLINE then do below check :
. Verify the SQL Services are UP and RUNNING
. Verify the SQL Version and see newly applied patch is reflecting .
. verify the SQL Error Logs for any error messages.
. Check all USER Databases are HEALTHY and ONLINE.
6) Now RESUME the DATA movement on Secondary replica and wait till the Database gets SYNCHRONIZED.
7) Now check th AG Dashboard , make sure it shows all GREEN and HEALTHY.
8) Now perform MANUAL failover from PRIMARY to newly patched Secondary replica and make sure the AG dashboard showing all GREEN and HEALTHY on newly patched replica which is currently PRIMARY.
9) Now follow above steps to proceed Patching .
10) once it is completed, Failback the AG to previous PRIMARY replica and check Dashboard is showing as all GREEN and HEALTHY.
11) Follow the procedures above to apply the patch on the DR replica.
Validate the following after patching all three replicas:
. Verify the SQL Verison was updated on all 3 replicas which are participating in AlwaysON configuration.
. Verify the AG Dashboard, make sure all shows GREEN and HEALTHY.
. check the SQL Error Logs for any Error messages on all 3 replicas.
. Inform to APPLICATION Team for App Validation and functionality.