Background: I have an old Windows VPS with SQL Server and IIS and whatnot that has been serving this web app for years. Along the way I've upgraded SQL Server and Windows occasionally.
Last night, I migrated the database to a SQL Azure Elastic instance. To minimise downtime I turned off all processes that change data outside of user tables, and used the Data Migration Assistant to do a schema and data move which took about 8 hours.
After hours I then downed the website, used Visual Studio's data tools to do a data comparison and catch up the days records (with the automatic processes off, not a huge number in any one table).
Unfortunately I had some trouble getting the web app to migrate, so in the end I've simply pointed the existing web server at the Azure database and offlined the local databases to ensure I'm not accidentally going the wrong way.
So, the database on Azure is identical to my local copy in terms of schema and data - compatibility level is one version higher in the cloud.
I have restored the final backup from the VPS into dev and it works perfectly as you'd expect.
Now I have some apparently random features that don't work, and throw a "Violation of primary key constraint" exception. Let me be SUPER CLEAR - all of the primary keys involved are IDENTITY(1,1) columns, I do not ever invent unique identifiers in these tables.
I have quadruple checked my Linq2Sql contexts and they are correctly set to AutoGenerated and OnInsert, not creating their own values.
The code works perfectly fine on local SQL Server, it's the Azure instance only that is being a pain. Curiously I have tested and found that if I re-do the migration I get the same error on different tables, but it doesn't seem consistent.
I have tried DBCC CHECKDB. I have also DBCC CHECKIDENT('mytable', RESEED, 10000) (the 10,000 is an int bigger than my biggest existing ID)
Does anyone know what it is about the Azure DB that causes this or how I might be able to dig deeper?
DBCC CHECKIDENT('mytable', RESEED)
Doing the reseed without specifying a new ID seems to have cleared it up on the tables I've seen it happening. I would still like to understand what the cause of this is so I might be able to prevent it (or maybe I should just FOREACH TABLE run the reseed anyway?)
Because I don't know the cause, I don't know how to identify affected tables without tripping over them in production :(