System and method for availability group database patching
A patching operation on an availability group cluster having a plurality of nodes is disclosed. The patching operation is performed in a plurality of iterations, each including determining a current state of each of the plurality of nodes, selecting a next node based on the current state, and patching the next node. A secondary replica node is selected as the next node before the a primary replica node. Each secondary replica node is patched in accordance with a first priority, upon patching each of the secondary replica node, a failover target node for patching the primary replica node is selected, the failover target node is selected based on a second priority, and according to the second priority, a healthy synchronous secondary replica node of the plurality of nodes is selected as the failover target node before an unhealthy synchronous secondary replica node of the plurality of nodes.
1 . A method comprising:
receiving, by a processor executing computer-readable instructions stored on a memory, a request for patching a plurality of nodes, each of the plurality of nodes being part of an availability group that maintains coordinated replication of a database and metadata indicating a single primary and one or more secondary copies;
determining, by the processor, for each node of the plurality of nodes, whether the node is designated in the metadata as storing a primary copy or only secondary copies of the database;
assigning, by the processor, a first priority value to each of the plurality of nodes, the first priority value representing a dynamically determined patching order, wherein the first priority value is assigned such that the plurality of nodes that are configured for asynchronous replication within the availability group are selected for patching before the plurality of nodes that are configured for synchronous replication within the availability group;
selecting, by the processor, a node from the plurality of nodes for patching based on the first priority value;
determining, by the processor, whether the selected node stores the primary copy of the database of at least one availability group;
responsive to determining that the selected node stores the primary copy of the database of at least one availability group:
identifying, by the processor, a failover target node from the plurality of nodes based on a second priority value assigned to each of the plurality of nodes, the second priority value being independent of the first priority value and indicating failover preference, wherein the second priority value is assigned such that the plurality of nodes that are configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are configured for asynchronous replication within the availability group; and
patching, by the processor, the selected node upon failing over the selected node to the failover target node; or
responsive to determining that the selected node does not store the primary copy of the database of any availability group, patching, by the processor, the selected node; and
after each patching operation, dynamically recalculating and updating the first priority value of each of the plurality of nodes based on current cluster conditions.
2 . The method of claim 1 , wherein the plurality of nodes that are designated in the metadata as storing a primary copy are patched after the plurality of nodes that are designated in the metadata as storing only secondary copies.
3 . The method of claim 1 , wherein the first priority value is a positive integer value and the second priority value is a negative integer value.
4 . The method of claim 1 , wherein of the plurality of nodes that are configured for synchronous replication within the availability group, a healthy node is selected for patching before an unhealthy node, wherein node health is determined based on the current cluster conditions.
5 . The method of claim 4 , wherein of the plurality of nodes that are healthy and configured for the synchronous replication within the availability group, a remote node is selected for patching before a local node.
6 . The method of claim 1 , wherein the first priority value for each of the plurality of nodes that is designated in the metadata as storing a primary copy is computed based on a sum of a pre-determined integer value and a number of availability groups on the each of the plurality of nodes.
7 . The method of claim 1 , wherein the second priority value is assigned such that the plurality of nodes that are healthy and configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are unhealthy and configured for synchronous replication within the availability group.
8 . The method of claim 1 , wherein the second priority value is assigned such that the plurality of nodes that are healthy are selected as the failover target node before the plurality of nodes that are unhealthy, wherein node health is determined based on the current cluster conditions.
9 . The method of claim 1 , wherein the second priority value is assigned such that the plurality of nodes that are healthy and already patched are selected as the failover target node before the plurality of nodes that are healthy and unpatched.
10 . The method of claim 1 , wherein the second priority value is assigned such that the plurality of nodes that are unhealthy and already patched are selected as the failover target node before the plurality of nodes that are unhealthy and unpatched.
11 . The method of claim 1 , wherein the patching of the plurality of nodes is performed using a software profile.
12 . The method of claim 11 , wherein for patching using the software profile, the method further comprises:
creating, by the processor, a new version of the software profile based on the patch;
cloning, by the processor, the new version of the software profile on the selected node or the next node that is being patched; and
migrating, by the processor, old data from the node to the new version of the software profile.
13 . The method of claim 1 , further comprising dynamically recalculating and updating the first priority value of each of the plurality of nodes based on the current cluster conditions for the plurality of nodes.
14 . The method of claim 13 , wherein the current cluster conditions of each of the plurality of nodes are determined based on at least one of a workload on the each of the plurality of nodes, or errors or alerts on the each of the plurality of nodes.
15 . A system comprising:
a memory having computer-readable instructions stored thereon; and
a processor executing the computer-readable instructions to:
receive a request to patch a plurality of nodes, each of the plurality of nodes being part of an availability group that maintains coordinated replication of a database and metadata indicating a single primary and one or more secondary copies;
determine, for each node of the plurality of nodes, whether the node is designated in the metadata as storing a primary copy or only secondary copies of the database;
assign a first priority value to each of the plurality of nodes, the first priority value representing a dynamically determined patching order, wherein the first priority value is assigned such that the plurality of nodes that are configured for asynchronous replication within the availability group are selected for patching before the plurality of nodes that are configured for synchronous replication within the availability group;
determine whether the selected node stores the primary copy of the database of at least one availability group;
responsive to determining that the selected node stores the primary copy of the database of at least one availability group:
identify a failover target node from the plurality of nodes based on a second priority value assigned to each of the plurality of nodes, the second priority value being independent of the first priority value and indicating failover preference, wherein the second priority value is assigned such that the plurality of nodes that are configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are configured for asynchronous replication within the availability group; and
patch the selected node upon failing over the selected node to the failover target node; or
responsive to determining that the selected node does not store the primary copy of the database of any availability group, patch the selected node; and
after each patching operation, dynamically recalculating and updating the first priority value of each of the plurality of nodes based on current cluster conditions.
16 . The system of claim 15 , wherein the plurality of nodes that are designated in the metadata as storing a primary copy are patched after the plurality of nodes that are designated in the metadata as storing only secondary copies.
17 . The system of claim 15 , wherein the first priority value is a positive integer value and the second priority value is a negative integer value.
18 . The system of claim 15 , wherein of the plurality of nodes that are configured for synchronous replication within the availability group, a healthy node is selected to patch before an unhealthy node, wherein node health is determined based on the current cluster conditions.
19 . The system of claim 18 , wherein of the plurality of nodes that are healthy and configured for the synchronous replication within the availability group, a remote node is selected to patch before a local node.
20 . The system of claim 15 , wherein the first priority value for each of the plurality of nodes that is designated in the metadata as storing a primary copy is computed based on a sum of a pre-determined integer value and a number of availability groups on the each of the plurality of nodes.
21 . The system of claim 15 , wherein the second priority value is assigned such that the plurality of nodes that are healthy and configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are unhealthy and configured for synchronous replication within the availability group.
22 . The system of claim 15 , wherein the second priority value is assigned such that the plurality of nodes that are healthy are selected as the failover target node before the plurality of nodes that are unhealthy, wherein node health is determined based on the current cluster conditions.
23 . The system of claim 15 , wherein the second priority value is assigned such that the plurality of nodes that are healthy and already patched are selected as the failover target node before the plurality of nodes that are healthy and unpatched.
24 . The system of claim 15 , wherein the second priority value is assigned such that the plurality of nodes that are unhealthy and already patched are selected as the failover target node before the plurality of nodes that are unhealthy and unpatched.
25 . The system of claim 15 , wherein the patching of the plurality of nodes is performed using a software profile.
26 . The system of claim 25 , wherein to patch using the software profile, the processor further executes computer-readable instructions to:
create a new version of the software profile based on the patch;
clone the new version of the software profile on the selected node or the next node that is being patched; and
migrate old data from the node to the new version of the software profile.
27 . The system of claim 15 , wherein the processor further executes computer-readable instructions to dynamically recalculate and update the first priority value of each of the plurality of nodes based on the current cluster conditions for the plurality of nodes.
28 . The system of claim 27 , wherein the current cluster conditions of each of the plurality of nodes are determined based on at least one of a workload on the each of the plurality of nodes, or errors or alerts on the each of the plurality of nodes.
29 . A non-transitory computer readable medium comprising computer-readable instructions stored thereon that when executed by a processor causes the processor to:
receive a request to patch a plurality of nodes, each of the plurality of nodes being part of an availability group that maintains coordinated replication of a database and metadata indicating a single primary and one or more secondary copies;
determine, for each node of the plurality of nodes, whether the node is designated in the metadata as storing a primary copy or only secondary copies of the database;
assign a first priority value to each of the plurality of nodes, the first priority value representing a dynamically determined patching order, wherein the first priority value is assigned such that the plurality of nodes that are configured for asynchronous replication within the availability group are selected for patching before the plurality of nodes that are configured for synchronous replication within the availability group;
select a node from the plurality of nodes to patch based on the first priority value;
determine whether the selected node stores the primary copy of the database of at least one availability group;
responsive to determining that the selected node stores the primary copy of the database of at least one availability group:
identify a failover target node from the plurality of nodes based on a second priority value assigned to each of the plurality of nodes, the second priority value being independent of the first priority value and indicating failover preference, wherein the second priority value is assigned such that the plurality of nodes that are configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are configured for asynchronous replication within the availability group; and
patch the selected node upon failing over the selected node to the failover target node; or
responsive to determining that the selected node does not store the primary copy of the database of any availability group, patch the selected node; and
after each patching operation, dynamically recalculating and updating the first priority value of each of the plurality of nodes based on current cluster conditions.
30 . The non-transitory computer readable medium of claim 29 , wherein the plurality of nodes that are designated in the metadata as storing a primary copy are patched after the plurality of nodes that are designated in the metadata as storing only secondary copies.
31 . The non-transitory computer readable medium of claim 29 , wherein the first priority value is a positive integer value and the second priority value is a negative integer value.
32 . The non-transitory computer readable medium of claim 29 , wherein of the plurality of nodes that are configured for synchronous replication within the availability group, a healthy node is selected to patch before an unhealthy node, wherein node health is determined based on the current cluster conditions.
33 . The non-transitory computer readable medium of claim 29 , wherein of the plurality of nodes that are healthy and configured for the synchronous replication within the availability group, a remote node is selected to patch before a local node.
34 . The non-transitory computer readable medium of claim 29 , wherein the first priority value for each of the plurality of nodes that is designated in the metadata as storing a primary copy is computed based on a sum of a pre-determined integer value and a number of availability groups on the each of the plurality of nodes.
35 . The non-transitory computer readable medium of claim 29 , wherein the second priority value is assigned such that the plurality of nodes that are healthy and configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are unhealthy and configured for synchronous replication within the availability group.
36 . The non-transitory computer readable medium of claim 29 , wherein the second priority value is assigned such that the plurality of nodes that are healthy are selected as the failover target node before the plurality of nodes that are unhealthy, wherein node health is determined based on the current cluster conditions.
37 . The non-transitory computer readable medium of claim 29 , wherein the second priority value is assigned such that the plurality of nodes that are configured for synchronous replication within the availability group are selected as the failover target node before the plurality of nodes that are configured for asynchronous replication within the availability group.
38 . The non-transitory computer readable medium of claim 29 , wherein the second priority value is assigned such that the plurality of nodes that are healthy and already patched are selected as the failover target node before the plurality of nodes that are healthy and unpatched.
39 . The non-transitory computer readable medium of claim 29 , wherein the second priority value is assigned such that the plurality of nodes that are unhealthy and already patched are selected as the failover target node before the plurality of nodes that are unhealthy and unpatched.
40 . The non-transitory computer readable medium of claim 29 , wherein the patching of the plurality of nodes is performed using a software profile.
41 . The non-transitory computer readable medium of claim 40 , wherein to patch using the software profile, the processor further executes computer-readable instructions to:
create a new version of the software profile based on the patch;
clone the new version of the software profile on the selected node or the next node that is being patched; and
migrate old data from the node to the new version of the software profile.
42 . The non-transitory computer readable medium of claim 29 , wherein the processor further executes computer-readable instructions to dynamically recalculate and update the first priority value of each of the plurality of nodes based on the current cluster conditions for the plurality of nodes.
43 . The non-transitory computer readable medium of claim 42 , wherein the current cluster conditions of each of the plurality of nodes are determined based on at least one of a workload on the each of the plurality of nodes, or errors or alerts on the each of the plurality of nodes.