DBInsight's Blog

Transactional Replication Identity Column Hell

Recently I re-created 80+ Transactional Replication publications with Updateable Subscriptions as part of a data centre move.

The data centre move process included a custom log shipping process to create and maintain the 80 “publication to be” databases in the new data centre server, at cut over the original SQL Server transactional replication to other subscriber servers was removed and the databases in the new data centre were made operational.

From there I recreated the publications and updateable subscription using a set of trusty scripts that has served me well over time for this customer.  The identity columns were set for automatic management with the publication definition, this option has operated successfully for this customer for a number of years.

All seemed good until we started getting Check Constraint failures relating to inserting records with identity column values outside the Check Constraint’s valid identity range. Nooohhh!!!

A typical Check Constraint for an identity column looks like below, as you can see, the ID column must be between 400049 and 600000, if a record is inserted outside of this range then a Constraint violation will occur.

ALTER TABLE [dbo].[Mytable]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_tran_1429580131] CHECK NOT FOR REPLICATION (([ID]>(400049) AND [ID]<(600000)))

My problem was that there were tables in the publication and subscription databases where the next Identity value generated would not be within the respective Check Constraint range. Using a DBCC CHECKIDENT statement I started to verify the anomaly.

In conjunction with the client’s developer we developed the query below to display tables that had their current identity value not within the corresponding Check Constraint identity range. This query was run against each of the 80 databases on each server to determine the extent of the problem. The script was then incorporated into a fix script that performed a DBCC CHECKIDENT with the correct reseed value. Once applied all of the Check Constraint violations disappeared. Phew!!!!.

SELECT
    a.TABLE_SCHEMA,
    a.TABLE_NAME,
    IDENT_current(a.TABLE_SCHEMA + ‘.’ + a.TABLE_NAME) AS IDENTcurrent,
    c.check_clause,
    cast(SUBSTRING(c.CHECK_CLAUSE, PATINDEX(‘%>(%’, c.CHECK_CLAUSE)+2, patindex(‘%)%’, c.CHECK_CLAUSE)-PATINDEX(‘%>(%’, c.CHECK_CLAUSE)-2) as numeric(18,0)) as LowerBound,
cast(SUBSTRING(c.CHECK_CLAUSE, PATINDEX(‘%<(%’, c.CHECK_CLAUSE)+2, patindex(‘%))’, c.CHECK_CLAUSE)-PATINDEX(‘%<(%’, c.CHECK_CLAUSE)-2) as numeric(18,0)) as UpperBound

FROM INFORMATION_SCHEMA.TABLES a
    inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE b on b.table_name = a.table_name
    inner join INFORMATION_SCHEMA.CHECK_CONSTRAINTS c on c.constraint_name = b.constraint_name

WHERE

       IDENT_current(a.TABLE_SCHEMA + ‘.’ + a.TABLE_NAME) IS NOT NULL
and b.constraint_name like ‘%identity%’

       and IDENT_current(a.TABLE_SCHEMA + ‘.’ + a.TABLE_NAME) not between
                cast(SUBSTRING(c.CHECK_CLAUSE, PATINDEX(‘%>(%’, c.CHECK_CLAUSE)+2, patindex(‘%)%’, c.CHECK_CLAUSE)-PATINDEX(‘%>(%’, c.CHECK_CLAUSE)-2) as numeric(18,0))
        and cast(SUBSTRING(c.CHECK_CLAUSE, PATINDEX(‘%<(%’, c.CHECK_CLAUSE)+2, patindex(‘%))’, c.CHECK_CLAUSE)-PATINDEX(‘%<(%’, c.CHECK_CLAUSE)-2) as numeric(18,0))
order by a.table_name

Posted in: Replication

Leave a Comment (0) ↓

Leave a Comment