patternsqlMinor
Availability Group, asynchronous mode, manual/forced failover - handling identity overlap
Viewed 0 times
handlingidentitygroupasynchronousmodemanualoverlapforcedfailoveravailability
Problem
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!
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!
Solution
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
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
Context
StackExchange Database Administrators Q#206300, answer score: 2
Revisions (0)
No revisions yet.