Availability Group, asynchronous mode, manual/forced failover - handling identity overlap
We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary. This is really bad for us...CustomerA seeing CustomerB's data.
EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly. A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below.
Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records?
Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run?
Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records? Back up/restore the old primary as a nonAG db?
Thanks!
sql-server availability-groups sql-server-2017 failover identity
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
asked May 9 '18 at 22:29
Gary SGary S
112
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary. This is really bad for us...CustomerA seeing CustomerB's data.
EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly. A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below.
Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records?
Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run?
Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records? Back up/restore the old primary as a nonAG db?
Thanks!
sql-server availability-groups sql-server-2017 failover identity
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
asked May 9 '18 at 22:29
Gary SGary S
112
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary. This is really bad for us...CustomerA seeing CustomerB's data.
EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly. A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below.
Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records?
Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run?
Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records? Back up/restore the old primary as a nonAG db?
Thanks!
sql-server availability-groups sql-server-2017 failover identity
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
asked May 9 '18 at 22:29
Gary SGary S
112
We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary. This is really bad for us...CustomerA seeing CustomerB's data.
EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly. A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below.
Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records?
Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run?
Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records? Back up/restore the old primary as a nonAG db?
Thanks!
sql-server availability-groups sql-server-2017 failover identity
sql-server availability-groups sql-server-2017 failover identity
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
asked May 9 '18 at 22:29
Gary SGary S
112
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
asked May 9 '18 at 22:29
Gary SGary S
112
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
edited Dec 21 '18 at 14:45
jadarnel27
4,3271331
4,3271331
asked May 9 '18 at 22:29
Gary SGary S
112
asked May 9 '18 at 22:29
Gary SGary S
112
asked May 9 '18 at 22:29
Gary SGary S
112
112
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.
Once the AG indicates that it's synchronized, then do the failover. This way you won't have data loss. You can then go back to async mode after the failover is completed.
See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.
Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode
But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover
This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
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%2f206300%2favailability-group-asynchronous-mode-manual-forced-failover-handling-identit%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
Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.
Once the AG indicates that it's synchronized, then do the failover. This way you won't have data loss. You can then go back to async mode after the failover is completed.
See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.
Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode
But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover
This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
add a comment |
Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.
Once the AG indicates that it's synchronized, then do the failover. This way you won't have data loss. You can then go back to async mode after the failover is completed.
See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.
Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode
But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover
This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
add a comment |
Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.
Once the AG indicates that it's synchronized, then do the failover. This way you won't have data loss. You can then go back to async mode after the failover is completed.
See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.
Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode
But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover
This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.
Once the AG indicates that it's synchronized, then do the failover. This way you won't have data loss. You can then go back to async mode after the failover is completed.
See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.
Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode
But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover
This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
answered May 10 '18 at 1:56
jadarnel27jadarnel27
4,3271331
4,3271331
add a comment |
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%2f206300%2favailability-group-asynchronous-mode-manual-forced-failover-handling-identit%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
