How to disable id field increment in ms sql 2012 when mirroring?

Question

SQL

Good afternoon! There is a scheme of 3 ms sql 2012 servers for database mirroring (main-server-mirror). I threw in a test database with several tables, added some data. I set up this whole bunch, everything connected and works. I carry out tests: I turn
off the main machine, the mode automatically changes and the backup server allows you to work with the database. And here the problem begins. When adding new records to the database on the backup server, the id increases by 1000. When the connection is restored and writing to the database on the main server, the id increases again by 1000. I tested it on a 2008 sql server, the id did not increase there. Can you tell me where to turn it off? Thanks

Answer 1

Full answer: In 2012, SQL Server implemented a new mechanism for caching IDENTITY values ​​- they are allocated immediately by a block and stored in memory until assigned to specific rows. This mechanism improved the performance of IDENTITY allocation, but resulted in the behavior you mentioned, because cached IDENTITY values ​​are lost when the server is powered off, and the next time the server is powered on, it caches the next block of values. This behavior can be disabled with flag 272, and the new caching mechanism will be disabled and the behavior will be the same as in version 2008R2 (according to the developers).
Decision

Answer 2

When adding new records to the database on the backup server, the id is increased by 1000.

This is a reserve for mismatch with disabled. You cannot disable it - there will be a violation of data integrity. For the key, use the uniqueidentifier type .