locked
Odd behavior for auto increment after AG failover RRS feed

  • Question

  • New to availability groups (SQL2016 SP2, 2 node cluster, listener and AG created.

    I created a test table in test AG database with 2 columns: 
    id – Identity=yes, seed=1, increment=1, not null, bigint
    name – varchar(50), null

    I inserted 2 records while primary was node A
    Failover to node B
    Inserted 2 more records
    Failover to node A
    Inserted 2 more records

    I noticed an interesting behavior in the identity columns

    id            name
    1             testdata1
    2             testdata2
    10002    After Failover to Node B
    10003    After Failover to Node B
    20002    Failover back to Node A
    20003    Failover back to Node A

    No data loss and no integrity issues.  Thankfully, I’m not going to be doing any database development but this stood out.  Not sure if there is a setting somewhere that can control this.  Really just curious.

    Thursday, April 11, 2019 7:27 PM

Answers

  • It is related to identity caching and is the result of unexpected restarts or failovers. In 2017 there is a switch to disable caching.  
    • Marked as answer by mkrok Thursday, April 11, 2019 8:52 PM
    Thursday, April 11, 2019 8:42 PM

All replies

  • It is related to identity caching and is the result of unexpected restarts or failovers. In 2017 there is a switch to disable caching.  
    • Marked as answer by mkrok Thursday, April 11, 2019 8:52 PM
    Thursday, April 11, 2019 8:42 PM
  • Thank You
    Thursday, April 11, 2019 8:52 PM