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?


2 Answers 2


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


I'm not sure how Customer A will see Customer B's data, unless you are moving data between customer A and customer B on the primary server (and assuming all the data is in 1 database). The data is synchronised in the same order that it happened on the primary server. It may not represent the current point in time in the primary server, but it will represent an exact point in time on the production server.

In terms of increasing ID's, starting in SQL 2012, SQL Server has a default increase in Identity column whenever you restart SQL Server (see this answer), so you will probably see a jump anyway.

In terms of synchronsing your data after your primary comes back online: If you force a failover on an async AG, then once the primary system comes back online you have to do a full resync of all the data, not just the data that changed since it went offline. This usually involves dropping and recreating the database on the primary and then re-adding it to the availability group as if it was a brand new node. There is no method of sync-ing data out of the box.

That doesn't mean you can't build one, you could find all of the data that was present on the primary before failover that didn't make it to the secondary and then copy it to the current primary server, but it's up to you to pull that data off and add it to the secondary. During that process you could create new ID's. How far behind does your replica run? How much data is the business willing to loose? If you're only a few minutes behind and can afford to loose 5 minutes of data, then maybe you don't want to get this data back.

As a side note, Availability Groups on SQL Standard Edition also have a real risk of data corruption that you cannot find. This is because you are not able to run Integrity Checks or Backups on the secondary. I think you should be running Integrity Checks regularly on every copy of your data, otherwise how do you know that the data on your secondary has not been corrupted by a disk error before you failover?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.