I used the new MS Data Migration Assistant (v4.1 link) to migrate a database from a SQL 2008 R2 server to an Azure SQL database. The process worked well and the data migration worked great!
However, after starting to use the Azure database from the application, a table with an identity column (defined with IDENTITY(1,1)) failed when a new record was added. The last record in the table had a value of 207170 in the identity column when the database was copied.
The first attempt to add to that table failed with the error:
Cannot insert duplicate key in object X. The duplicate key value is (207168).
As you can see, the number that the new record attempted to use was 207168. But that record already existed. The next attempted failed using 207169. The next attempt failed as well. But the next one worked fine and used 207171.
So, it seems like the identity value for that table was 3 numbers lower than it should have been.
After encountering this, I ran
DBCC CHECKIDENT ('table', NORESEED ) on every table in the database. None of them appear to have a problem and inserting a record into another table worked fine.
I think when I do the next database migration, I will run DBCC CHECKIDENT on every table to ensure that the identity values are correct.
Has anyone else had this problem? Is there a way to avoid it?
Edit: I restored the database to before the inserts where done. The output from DBCC CHECKIDENT was
Checking identity information: current identity value '207167', current column value '207170'.