Managing IDENTITY and SEQUENCE Behavior in SQL Server Always On Availability Groups

Introduction

In SQL Server Always On Availability Groups, maintaining data consistency during failovers is necessary for high availability and reliability. However, certain aspects of database behavior, such as the generation of values by IDENTITY columns and SEQUENCE objects, can lead to unexpected gaps or jumps after a failover or server restart. This can affect the continuity of sequential values, which is often important in business applications. In this post, we’ll look into how SQL Server handles IDENTITY columns and SEQUENCE objects in an Always On environment, explain the role of caching, and offer strategies to manage or prevent gaps in your sequences during failovers.

IDENTITY Column Behavior in Always On Availability Groups

An IDENTITY column automatically generates a new value for each inserted row. To improve performance, SQL Server caches identity values in memory. However, this caching can lead to gaps in the sequence if the server is restarted or if a failover occurs within an Always On Availability Group.

Scenario: Identity Caching During Failover

When a failover occurs in an Always On Availability Group, unused cached identity values from the former primary replica are discarded. When the secondary replica takes over as the new primary, SQL Server allocates a new batch of identity values. This results in gaps in the identity sequence.

For example, if SQL Server cached 1,000 identity values and a failover happens after 50 rows are inserted, the next row inserted on the new primary replica may receive an identity value starting from 1,001, leaving a gap from 51 to 1,000.

Identity Cache Size

The cache size for IDENTITY columns depends on the data type:

• INT: Cache size is 1,000.
• BIGINT or NUMERIC: Cache size is 10,000.

SEQUENCE Object Behavior in Always On Availability Groups

A SEQUENCE object generates sequential numbers independently of any specific table. Unlike IDENTITY, SEQUENCE objects offer more control over how sequences behave, such as configuring the increment size and cache size, or even disabling caching altogether.

Example of SEQUENCE Caching During Failover

Let’s assume we create a SEQUENCE with a cache size of 50:

CREATE SEQUENCE dbo.MySequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    CACHE 50;
GO

Next, we create a table that uses this SEQUENCE for its primary key:

CREATE TABLE dbo.MillionRows (
    ID INT DEFAULT NEXT VALUE FOR dbo.MySequence PRIMARY KEY,
    Column1 NVARCHAR(50),
    CreatedAt DATETIME2 DEFAULT GETDATE()
);
GO

If the primary replica inserts 25 rows, using sequence values 1 through 25, and then a failover occurs, the new primary replica will start using values from the next cache block, beginning at 51. As with IDENTITY columns, this causes a gap in the sequence.

Preventing Identity Jumps with SEQUENCE

You can avoid sequence gaps during failover by using the NO CACHE option:

CREATE SEQUENCE dbo.MyNoCacheSequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    NO CACHE;
GO

Using NO CACHE prevents gaps, but it may come with a performance cost, as SQL Server must fetch each new value from disk without caching.

Example of Identity Jump with Failover

Let’s consider an example of how identity jumping occurs during a failover:

CREATE TABLE dbo.MillionRows (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Column1 NVARCHAR(50),
    CreatedAt DATETIME2 DEFAULT GETDATE()
);

Insert three rows into the primary replica:

INSERT INTO dbo.MillionRows (Column1) VALUES ('Row1'), ('Row2'), ('Row3');

• The ID values will be 1, 2, and 3.
• After a failover, SQL Server discards the cached identity values from the original primary. If the default cache size was 1,000, the new primary might start with an identity value of 1,001 for the next row:

INSERT INTO dbo.MillionRows (Column1) VALUES ('Row4'), ('Row5');

The new ID values will be 1,001 and 1,002.

Options to Prevent Identity Jumps

1. Use SEQUENCE with NO CACHE: By substituting IDENTITY with a SEQUENCE and disabling caching, you can prevent identity jumps.

CREATE SEQUENCE dbo.MyNoCacheSequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    NO CACHE;
GO

2. Enable Trace Flag 272: You can enable trace flag 272 (-T272) to disable identity caching at the server level. This requires restarting the SQL Server service.
3. Handle Gaps in Application Logic: If gaps in identity values are acceptable, you can safely ignore this behavior, as it does not affect data integrity—only the sequential order.

Conclusion

Managing the behavior of IDENTITY and SEQUENCE during failovers in SQL Server Always On Availability Groups is important for ensuring data consistency and predictable application behavior. Caching of identity values improves performance but can lead to gaps during failovers, resulting in unexpected jumps.

For applications where sequential continuity is crucial, consider using a SEQUENCE with NO CACHE or enabling trace flag 272 to avoid gaps. However, if gaps are acceptable in your application logic, this behavior can be safely ignored.

By choosing the right approach based on your application’s requirements, you can optimize performance while minimizing the impact of identity or sequence gaps in your SQL Server Always On environment.