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 Jan 15 at 18:16
Erik Darling
21.2k1264104
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
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 Jan 15 at 18:16
Erik Darling
21.2k1264104
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
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 Jan 15 at 18:16
Erik Darling
21.2k1264104
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
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 Jan 15 at 18:16
Erik Darling
21.2k1264104
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
edited Jan 15 at 18:16
Erik Darling
21.2k1264104
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
edited Jan 15 at 18:16
Erik Darling
21.2k1264104
edited Jan 15 at 18:16
Erik Darling
21.2k1264104
edited Jan 15 at 18:16
Erik Darling
21.2k1264104
21.2k1264104
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
asked Jan 15 at 18:03
Aaron RheamsAaron Rheams
514
514
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 Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
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
Jan 15 at 18:59
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
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 Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
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
Jan 15 at 18:59
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
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 Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
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
Jan 15 at 18:59
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
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 Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
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 Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
answered Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
answered Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
answered Jan 15 at 18:16
Max VernonMax Vernon
50.2k13112222
50.2k13112222
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
Jan 15 at 18:59
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
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
Jan 15 at 18:59
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
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
Jan 15 at 18:59
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
Jan 15 at 18:59
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion.
– Aaron Rheams
Jan 16 at 18:12
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor.
– Max Vernon
Jan 16 at 18:17
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary.
– Aaron Rheams
Jan 17 at 22:09
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
