Good day! There is a scheme of 3 servers ms sql 2012 for mirroring database (main-tracking-mirror). I threw in a test database with several tables, added some data. I set up this whole bunch of everything connected and working.
I conduct tests:
I turn off the main machine, the mode automatically changes and the backup server allows you to work with DB. And here the problem begins. When adding new entries in the database on the backup server, the id is increased by 1000. When the connection is restored and written to the database on the main server, the id is again increased by 1000. I tested it on the 2008 sql server; Tell me where to turn it off?
thank

  • one
    1000 small loss. And the logic of the database should not depend on how the id is assigned. In general, the documentation msdn.microsoft.com/ru-ru/library/ms186775.aspx says that if the pass is invalid, then you should use other mechanisms to assign id - Mike
  • Simply, the project already has a database with data for 5 years. The structure is defined and no one will change it. Therefore, we must use what we have. I think you can find where the behavior of the destination id changes when mirroring. But where? - scarfase
  • dfarber.com/computer-consulting-blog/2014/2/13/… - Mike
  • It's not about mirroring. The point is caching identity values. This is a well-known feature (see Connect one , two , and also en-SO one , two , three ). There are workarounds (at the cost of lowering performance), so see if it's worth bothering. - i-one

1 answer 1

The short answer is: you can turn off this behavior with the 272 trace flag.

Complete answer: In 2012, SQL Server implemented a new caching mechanism for IDENTITY values ​​— they are allocated immediately as a block and stored in memory until they are assigned to specific strings. Such a mechanism improved the performance of the IDENTITY allocation, but led to the behavior you specified, because when you turn off the server, the cached IDENTITY values ​​are lost, and the next time you turn on the server caches the next block of values. This behavior can be disabled with flag 272, while the new caching mechanism will be disabled and the behavior will be the same as in version 2008R2 (according to the developers).

Related links (contacting Microsoft Connect) already cited above @ i-one in the comments to the question: