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.
Steps to be taken Before applying the Patch to SQL Instance:
1) Make sure to check we have Latest Full backup available for all SYSTEM and USER Databases on PRIMARY Replica.If the User Databases Sizes are SMALL better take fresh Full backup for all Databases else if the DB size is more then check lastest valid FULL backup is available and then take atleast Differential or Transactional Log backup before starting 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 I said in above, we have 3 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) Now apply patch to DR replica by following above steps.
After Patching all 3 replicas, validate the following :
. 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.