How to Apply Patch in AlwaysOn Availability Group?
Steps for an Availability Group with one
secondary replica is mentioned following and in case there are more than 1
replica too, almost same steps will be followed.
- Needless to mention that being
a DBA, Backup is always a priority task for us. Make sure that we have
taken good recent OS backup with system state (or VMware snapshot with SQL
services stopped), a good recent backup of all databases and a successful
completion of a checkdb on the primary node. {This is not mandatory, but
to avoid “Ouch” moment}.
- Make note of primary replica
node or just capture the screen print of cluster manager. Second, From the
node acting as the primary replica (SQL1), change the failover mode to
manual to ensure, auto fail-over is not being triggered. It is similar to
what we do with classic cluster. In classic cluster, we remove nodes from
possible owners list.
- Refresh the affected databases
on the secondary replica (SQL2) and make sure that everything is green on
the dashboard. This way, we can isolate things and troubleshooting is much
easier in case we stuck with any issue.
- Apply the patch (service pack
or CU) on SQL2 and then on SQL 3 in case we have 3 replicas. We have
SQL server 2016 SP2 and CU5 to applied but steps are same for any
package.
- Repeat the Windows Update
and/or software updates until all available patches are applied ( In my
case, first SP2 and then CU5). Do not move on with the patching steps
until all patches and post patch reboot and configuration tasks are
completed. As server reboot will be required, please ensure to apply patch
on SQL 2 and then reboot that node. Once SQL 2 is up then only proceed to
SQL 3.
- Double check that patches have
been applied, the cluster is healthy and AlwaysOn Availability Groups are
functional. Also, check build version by running "select
@@Version" and it should be the target version, In my case
" Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1
(X64)"
- Make sure that synchronization
state is SYNCHRONIZED.
- Fail over the availability
group to the secondary replica (SQL2) or to replica which was primary
before starting patching activity.
- Refresh the affected databases
on secondary Replica (former primary = SQL1) until the synchronization
state is synchronized.
- Apply the patch (service pack
of CU) on SQL1.
- Repeat the Windows Update
and/or software updates until all available patches are applied. Do not
move on with the patching steps until all patches and post patch reboot
and configuration tasks are completed.
- Double check that patches have
been applied, the cluster is healthy and AlwaysOn Availability Groups are
functional.
- Make sure that synchronization
state is SYNCHRONIZED.
- Fail over the availability
group to the primary node (back to SQL1).
- Change the failover mode to
Automatic now (which we changed in Step 2).
- Validate all databases status
and you may need to check SQL agent jobs which would have been cancelled
during patching activity.
- Last but not least, validate
SPNs of involved instances to ensure you kerborase authentication.
Hope it will be helpful.
No comments:
Post a Comment