Backups with Split Availability Groups
I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups. First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.
We do Windows patches once per month, with the expected server reboots. Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.
If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server. I do this at the resolution of the listener, since it connects to the primary node of an availability group. The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.
As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.
Do I have to script jobs to run by availability group in these cases?
Is there a combination of settings that only backup an availability group which is primary for that node?
I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG. My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR. I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.
For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node. I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations. Any clarity that anyone can offer would be greatly appreciated.
sql-server backup availability-groups
edited 12 hours ago
Erik Darling
21.2k1264104
asked 12 hours ago
Aaron RheamsAaron Rheams
493
add a comment |
I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups. First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.
We do Windows patches once per month, with the expected server reboots. Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.
If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server. I do this at the resolution of the listener, since it connects to the primary node of an availability group. The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.
As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.
Do I have to script jobs to run by availability group in these cases?
Is there a combination of settings that only backup an availability group which is primary for that node?
I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG. My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR. I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.
For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node. I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations. Any clarity that anyone can offer would be greatly appreciated.
sql-server backup availability-groups
edited 12 hours ago
Erik Darling
21.2k1264104
asked 12 hours ago
Aaron RheamsAaron Rheams
493
add a comment |
I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups. First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.
We do Windows patches once per month, with the expected server reboots. Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.
If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server. I do this at the resolution of the listener, since it connects to the primary node of an availability group. The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.
As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.
Do I have to script jobs to run by availability group in these cases?
Is there a combination of settings that only backup an availability group which is primary for that node?
I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG. My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR. I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.
For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node. I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations. Any clarity that anyone can offer would be greatly appreciated.
sql-server backup availability-groups
edited 12 hours ago
Erik Darling
21.2k1264104
asked 12 hours ago
Aaron RheamsAaron Rheams
493
I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups. First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.
We do Windows patches once per month, with the expected server reboots. Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.
If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server. I do this at the resolution of the listener, since it connects to the primary node of an availability group. The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.
As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.
Do I have to script jobs to run by availability group in these cases?
Is there a combination of settings that only backup an availability group which is primary for that node?
I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG. My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR. I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.
For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node. I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations. Any clarity that anyone can offer would be greatly appreciated.
sql-server backup availability-groups
sql-server backup availability-groups
edited 12 hours ago
Erik Darling
21.2k1264104
asked 12 hours ago
Aaron RheamsAaron Rheams
493
edited 12 hours ago
Erik Darling
21.2k1264104
asked 12 hours ago
Aaron RheamsAaron Rheams
493
edited 12 hours ago
Erik Darling
21.2k1264104
edited 12 hours ago
Erik Darling
21.2k1264104
edited 12 hours ago
Erik Darling
21.2k1264104
21.2k1264104
asked 12 hours ago
Aaron RheamsAaron Rheams
493
asked 12 hours ago
Aaron RheamsAaron Rheams
493
asked 12 hours ago
Aaron RheamsAaron Rheams
493
493
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database. This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.
There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
2
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227213%2fbackups-with-split-availability-groups%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database. This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.
There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
2
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
add a comment |
Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database. This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.
There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
2
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
add a comment |
Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database. This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.
There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database. This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.
There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
answered 12 hours ago
Max VernonMax Vernon
50.1k13111220
50.1k13111220
2
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
add a comment |
2
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
2
2
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
11 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227213%2fbackups-with-split-availability-groups%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
